PostgreSQL
\
commands
\d - list tables, views, and sequences
\dn - list schemas
Common Table Expressions (CTEs) Link to heading
- Docs
WITH
clause
List active transactions Link to heading
SELECT
datname AS database,
usename AS user,
pid,
application_name,
client_addr,
state,
state_change,
query
FROM
pg_stat_activity
WHERE
state = 'active' OR state = 'idle in transaction';
List all tables in the database Link to heading
SELECT
*
FROM
pg_catalog.pg_tables
WHERE
schemaname != 'pg_catalog'
AND schemaname != 'information_schema'
;
Drop all tables in the database Link to heading
⚠️
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
Remove all non-letter chars Link to heading
regexp_replace(value, '[^A-z ]+', '', 'g')
Split string in multiple rows Link to heading
SELECT regexp_split_to_table('row1,row2,row3', ',')
Create a PL/pgSQL function Link to heading
CREATE OR REPLACE FUNCTION even_odds(max int)
RETURNS int[][]
LANGUAGE plpgsql
AS $function$
DECLARE
evens int[] := Array[]::int[];
odds int[] := Array[]::int[];
BEGIN
FOR n IN 1..max loop
IF n % 2 = 0 then
evens := ARRAY_APPEND(evens, n);
ELSE
odds := ARRAY_APPEND(odds, n);
END IF;
END LOOP;
RETURN Array[evens, odds];
END;
$function$
;
SELECT even_odds(100);