PostgreSQL

date handling

select count(*), DATE_TRUNC('hour', created) as hour from table group by 2 desc
select * from table where created > '"2019-10-20 00:00:00+02"'
select * from table where created > current_timestamp - '3 hours'::interval
select * from table where created > CURRENT_DATE - 91  -- days

Date formatting

see [1]

SELECT to_char(last_login, 'DD.MM.YYYY') as last_login FROM mytable

convert/truncate date to month/week etc.

SELECT date_trunc('week', import_date), count (production_order)
  FROM cosp.c_upload_sap_order
  group by 1
  order by 1 desc

cast types

select t.id, 'matched filter1'::character varying(16) from table t

EXCEPT (=MINUS in Oracle)

from [2]

SELECT column_list
FROM A
WHERE condition_a
EXCEPT 
SELECT column_list
FROM B
WHERE condition_b;

read only user

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username

sprintf-alternative

goal

sprintf 'u%07d', 1

achieved via

SELECT replace( format('u%7s', 1), ' ', '0');

random item of list

SELECT (array['Yes', 'No', 'Maybe'])[floor(random() * 3 + 1)];

UPDATE via SELECT

sync table tab1 from tab2

UPDATE tab1
SET (group_id, error_de, error_en, error_hu) = (
 SELECT group_id, error_de, error_en, error_hu FROM tab2 WHERE tab1.code = tab2.code
)

pseudonymization

UPDATE user
SET
username = replace( format('u%7s', id), ' ', '0') -- u0000001 for id=1
,first_name = (array['Max', 'Egon', 'Lisa']) [floor(random() * 3 + 1)]
,last_name = (array['Meier', 'Müller', 'Schmidt')[floor(random() * 3 + 1)]
,email = 'no-mail@schaeffler.com'
WHERE id = 1;

Count the occurrences of a substring in a string

solution based on [3] and [4]

SELECT
 (length(str) - length(replace(str, replacestr, )) )::int
 / length(replacestr)
FROM ( VALUES
 ('foobarbaz', 'ba')
) AS t(str, replacestr);

becomes

SELECT
  sub1.id, 
  (length(sub1.str) - length(replace(sub1.str, sub1.replacestr, )) )::int  / length(sub1.replacestr) AS times
FROM
(
SELECT id, checklist AS str, '{"id":'::text AS replacestr FROM d_department
) sub1

which counts the number of (json) ids '{"id":' in a textfield

Get table column type / describe alternative

SELECT
  table_name,
  column_name,
  data_type,
  character_maximum_length
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  1 = 1
  AND table_schema IN ('schema1','schema2')
  AND (table_name LIKE 'c\_%' OR table_name LIKE 'd\_%')
  AND column_name = 'order'
ORDER BY
  table_name,
  column_name

Performance Tuning

DELETE Statement

from [5]

Good: DELETE FROM foo WHERE id IN (select id from rows_to_delete);
Bad:  DELETE FROM foo WHERE id NOT IN (select id from rows_to_keep);
Good Alternative for the bad rows_to_keep path:
DELETE FROM foo 
 WHERE id IN (
  SELECT id FROM foo f 
  LEFT JOIN rows_to_keep k ON f.id = k.id
             WHERE k.id IS NULL
 );

delete based on multiple criteria also in another table

DELETE FROM
  table1 t1
WHERE
  EXISTS (
  SELECT
    1
  FROM
    table2 t2
  WHERE
    1 = 1
    AND t2.date < CURRENT_DATE - 30
    AND t1.col1 = t2.col1
    AND t1.col2 = t2.col2 )

Admin

SELECT version();

Check/analyze why a statement runs slow

from [6]

EXPLAIN (analyze,buffers,timing) 
 DELETE FROM myTable WHERE id IN (
  SELECT id FROM myTableToDelete
) AND id > 2150 
  AND id < 2200 ;

Find and Delete Locks

Display PIDs and Queries running on a table, from [7]

SELECT pid, state, usename, query, query_start 
  FROM pg_stat_activity 
 WHERE pid IN (
  SELECT pid FROM pg_locks l 
    JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' 
   WHERE t.relname = 'myTable'
 );

Cancel PID (soft)

SELECT pg_cancel_backend(12345); 

Cancel PID (force)

SELECT pg_terminate_backend(12345);

Backup and Restore

Backup

pg_dump.exe -h localhost -p 5432 -U myUser --schema=mySchema -F c -b -v -f myDB.dmp myDB

Restore

pg_restore  -h localhost -p 5432 -U myUser -d myDB -v myDB.dmp