Postgres cheat-sheet

Few handy queries for Postgres

A consolidated list of in-frequently used Postgres queries, which are quite useful for bigger projects

Image by Valiphotos from Pixabay

1. Find the name of the autogenerated sequence

Select pg_get_serial_sequence('SchemaName.TableName', 'ColumnName');

2. Update or reset the sequence number

SELECT SETVAL(pg_get_serial_sequence('SchemaName.TableName','ColumnName'), (select max(ColumnName) from SchemaName.TableName)+1, false);

3. Set the default schema for queries

set search_path to SchemaName

4. Rank the data

WITH summary AS (
 SELECT tn.column1, tn.column2,tn.column3,
 ROW_NUMBER() OVER(PARTITION BY column1 
 ORDER BY column2 DESC) AS rnk
 FROM TableName tn)
 SELECT s.column1, s.column2,s.column3,s.rnk
 FROM summary s
 WHERE s.rk = 1; /*you can give any number here, 1 provides the first record according to the order*/

5. page limit and offset. For limiting the data per query return, page limit will set the number of records to be returned, offset will help to get the next set. This is extremely helpful when pagination has to be done from the database. Offset starts with 0. If we provide, for example, 10 as offset, the result set from the 11th record will be shown to the user.

select * from TableName order by column1 limit PageLimit offset OffSet;

6. Initialize an array

declare VariableName varchar[];
declare VariableName int[];

7. Search for a given value in the array

if 'VariableName' = ANY(ArrayName) then 
 statements here
else
 statements here
end if;

8. Check if a function exists or not in a schema

if exists (
   select 1
              from pg_proc p 
                   join pg_namespace n 
                   on p.pronamespace = n.oid 
             where n.nspname =schema_name
             and proname = 'functionName' 
 ) then 
 statements here
end if;

9. Get the temporary schema name

pg_my_temp_schema()

10. Get the columns of a temporary table

select column_name from 
          information_schema.columns 
        WHERE
           table_schema =(SELECT nspname
   FROM   pg_namespace
   WHERE  oid = pg_my_temp_schema()) 
      AND table_name = LOWER('TemporaryTableName')

11. Get all the schemas in the database

SELECT DISTINCT nspname 
  FROM            pg_catalog.pg_namespace 
  WHERE           nspname !~ '^pg_'

12. Set default value during variable initialization

DECLARE 
        variableName BIGINT:=0;

13. For loop has to be created either in function/stored procedure or a DO block.

DO $doBlock$
DECLARE schema_name varchar; --give any name you want here
FOR schema_name IN 
  SELECT DISTINCT nspname 
  FROM            pg_catalog.pg_namespace 
  WHERE           nspname !~ '^pg_' 
  LOOP 
      EXECUTE 'set search_path to ' || schema_name;
  END LOOP;
END;
$doBlock$

14. Provide log during function execution. We can also add variables for substitution and get a relevant message during run time.

raise notice 'type your message here %' ,variableName;

15. Check if a table exists or not

IF EXISTS(
 SELECT 1 
 FROM information_schema.columns 
 WHERE table_schema='SchemaName' 
 AND table_name='TableName' 
 )
THEN
statements here
ELSE
statements here
END IF;

16. Check if a column exists or not

IF EXISTS(
 SELECT 1 
 FROM information_schema.columns 
 WHERE table_schema='SchemaName' 
 AND table_name='TableName' 
 AND column_name='ColumnName'
 )
THEN
statements here
ELSE
statements here
END IF;

17. Check if a JSON array is empty or not and replace it with ‘[]’

coalesce(json_agg(columnName)filter (where ColumnName is not null), '[]')