CartoDB SQL tricks: Various things that as a SQL-newbie I had to search for >10 minutes to find answers for.

fun_cartodb_sql.sql

-- union neighboring counties together into one polygon
SELECT 1 as cartodb_id, ST_Union(ST_SnapToGrid(the_geom,0.0001)) as the_geom FROM public.cb_2018_us_county_500k where name in ('Tompkins', 'Tioga', 'Broome') and statefp = '36'

-- want to update one column with multiple values in the same query? And use a function in a CASE?
UPDATE loan_data_copy
	SET the_geom = (
      CASE
      WHEN city = 'Columbus ' THEN ST_GeomFromText('POINT(33.12 -87.12)', 4326)
      WHEN city = 'Meridian ' THEN ST_GeomFromText('POINT(33.123 -88.123)', 4326)
      WHEN city = 'Tuscaloosa' THEN  ST_GeomFromText('POINT(33.321 -88.312)', 4326)
      ELSE the_geom
      END)

-- make columns x_long and y_lat, then run this update query to take x/ys out of geom and put them into separate columns.
-- Useful for SQL API usage.
UPDATE athompson.this_table
SET x_long = ST_X(the_geom), y_lat = ST_Y(the_geom)

-- Want to make great circle lines between points?
-- you have a "start points" table and an "end points" table
-- make a third table that has the number of rows of connections/lines you want
-- run this query on that third table:
UPDATE athompson.third_line_table
SET the_geom =
   ST_Transform(
     ST_Segmentize(
       ST_MakeLine(
         ST_Transform(athompson.third_line_table.the_geom, 953027),
         ST_Transform(startpoint_table.the_geom, 953027)
       ), 
       100000
     ), 
    4326 
   )
FROM (SELECT * from athompson.endpoints_table where athompson.endpoints_table.cartodb_id = 101) startpoint_table

-- Got an UPDATE query that's taking so long CartoDB times you out? Run this a few times changing the numbers in the where
update table_name
set updating_column = convoluted_update1+convoluted_update2
where cartodb_id >= 1 and cartodb_id < 1000

-- Given a polygon table, select the polygons that contain a point you define
select *
from polygons_table
where st_contains(polygons_table.the_geom,ST_GeomFromText('POINT(143.46849822998047 -37.709754973447865)',4326))
--it goes POINT(lon lat)

-- Replace HTML: Given a table with a column that has HTML in it, make a new column with the text content but not the HTML tags
ALTER TABLE points_table ADD description_nohtml TEXT;--add the new column
UPDATE points_table SET description_nohtml = regexp_replace(description, E'<.*?>', '', 'g' );-- use regex to replace

--Fix zip codes with leading zeroes that were turned to text instead of number or zeroes removed
--First add a new column to your dataset that's string type. Then run:

update my_zip_code_table
set new_string_column = to_char(zip_code_column, 'fm00000')

--For some reason if you only wanted to modify the zip codes that had 4 digits and not all the codes, you could do:

update my_zip_code_table
set new_string_column = to_char(zip_code_column, 'fm00000') where length(zip_code_column::text) = 4

-- or right in builder with a string "zip" field that lost its zeros
update retail_logistics_data set zip = to_char(zip::integer, 'fm00000') where length(zip::text) = 4

-- to find indexes on a table, from http://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
WHERE c.relkind = 'i' and t.relname = 'mc_bg_1' -- put tablename here
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname
    ,t.relname
    ,c.relname;
    
-- make a builder analysis table of buffers, and get the st_difference areas of the buffers
with one as (select * from analysis_blahblah5e where cartodb_id = 1),
two as (select * from analysis_blahblah5e where cartodb_id = 2),
three  as (select * from analysis_blahblah5e where cartodb_id = 3),
four as (select * from analysis_blahblah5e where cartodb_id = 4)
select one.the_geom as the_geom, 1 as cartodb_id from one, two, three, four
UNION ALL
select st_difference(two.the_geom, one.the_geom) as the_geom, 2 as cartodb_id from one, two, three, four
UNION ALL
select st_difference(three.the_geom, two.the_geom) as the_geom, 3 as cartodb_id from one, two, three, four
UNION ALL
select st_difference(four.the_geom, three.the_geom) as the_geom, 4 as cartodb_id from one, two, three, four;