Postgres: Misc

(Last Updated On: )

Here are some misc things you can do in postgres 9.4.

Print to Console:

  1.  raise notice 'VALUE: %', value;

Json Build Object:

  1.  json_build_object('name', value, 'name2': value2)

Json Array:

  1.  json_agg(data)

Extract Month:
You can also extract year, day, etc.

  1.  extract(month from date_column)

Json Querying:
You can extract a field from a json field. You can do:

  1.  rec->>'id'
  2. rec#>'{field, sub_field,value}'
  3. rec->'object'

Row To Json:

  1.  SELECT row_to_json(t)
  2. FROM mytable t

Update With FROM:

  1.  UPDATE mytable l
  2. SET old_val_id=sub.new_val_id
  3. FROM (
  4.      SELECT l2.id, s.id as new_val_id
  5.      FROM mytable l2
  6.           INNER JOIN mysupertable s ON s.id=l2.old_val_id
  7. ) sub
  8. WHERE sub.id=l.id;

Inline Script:

  1.  DO $do$
  2. DECLARE id integer;
  3. BEGIN      
  4.  
  5. END $do$ LANGUAGE plpgsql;

If:

  1.  IF CONDITION THEN
  2. END IF;

Let’s say you have a json field and in that field you have a field which tells you what key you should select for certain data. It could be customizable from the user entering data. To dynamically select that field you can do the below.

  1. jsonField->((to_json((jsonField->'key'->'sub_key'))#>>'{}')::text)

Upsert:
Sometimes we want to perform update if the record exists or an insert if it doesn’t. Most of the time we write a function that deals with this on a record by record basis. But what if we want to do a batch update or insert. What do we do then. We perform an upsert. See below. We write the update and return the result in the remainder of the query  we check if upsert gave us anything. If it didn’t we perform the insert.

  1. WITH upsert AS (UPDATE MyTable mt
  2.             SET column_name = sub.new_value
  3.             FROM (SELECT mot.id, mot.new_value 
  4.                  FROM MyOtherTable mot
  5.                  ) sub
  6.             WHERE sub.id=mt.related_id 
  7. RETURNING *) 
  8. INSERT INTO MyTable (id, column_name, related_id) 
  9. SELECT ?, ?, ? 
  10. WHERE NOT EXISTS (SELECT * FROM upsert)

Regex Substring:
There are different ways of using regex. This is one way.

  1. substring(column_name from '([0-9]{4}-[0-9]{2}-[0-9]{2})')

PGSQL Loop:
This is one way to loop using pgsql.

  1. DO $do$
  2. DECLARE rec RECORD;
  3. BEGIN
  4. 	FOR rec IN SELECT * FROM MyTable
  5. 	LOOP
  6. 		--We can then use rec like rec.colum_name
  7. 	END LOOP;
  8. END $do$ LANGUAGE plpgsql;

Milliseconds to timestamp:

This will return 2017-08-17 21:26:04

  1. select to_timestamp(1503005165);