PostgreSQL

\ commands

\d - list tables, views, and sequences
\dn - list schemas

Common Table Expressions (CTEs) Link to heading

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);

Row-level permission Link to heading