Skip to content
Archive of entries posted on

PostgreSQL banter on ‘with’

RhodiumToad with foo(x) as (values (‘blah blah blah’)) insert into wherever select x from foo; merlinm or, with vars as (select ‘a’ as a, 2 as b) insert into foo values (select a from vars), select b from vars); merlinm oops, missing a parent there merlinm gah RhodiumToad with vars(a,b) as (values (‘a’, 2)) … […]

CTE to Remove Duplicates

Consider for a moment, a table of records including addresses and data, such that most every address occurs once, but for unknown reasons, also contains more than one duplicate address (656 in 55,279 below). How to remove duplicate addresses? let us count the ways.. Here is one: copy (  select a_name, address, city, state, zip, […]

Slice a Polygon by a Grid

Sometimes there are polygons with too many vertices to process comfortably. For that or other reasons, you may want to cut one or more polygons with a reference grid. Here is an example using the US 2010 Census and a set of grid cells in EPSG:3310: ——————————————————————– — take a single census place polygon and […]