Thursday, September 25, 2025

Find out if a constraint in PostgreSQL is defined as deferrable

Logged into the relevant database, find out which FK are deferrable:
SELECT conname, condeferrable, condeferred
FROM pg_constraint
WHERE conrelid in ( 'mytable1'::regclass,'mytable2'::regclass) 
AND contype='f';
           conname            | condeferrable | condeferred
------------------------------+---------------+-------------
 mytable1_id_fkey             | f             | f
 mytable1_id_fkey             | f             | f
 mytable2_id_fkey             | f             | f
  • If condeferrable = t (true), the constraint is created initially deferrable
  • If condeferrable = f (false), like in my case, it is not.
  • Wednesday, September 24, 2025

    Terminate PostgresSQL sessions

    -- Terminate all connections from user "sales"
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE usename = 'sales';
    
    In my case the result was:
     pg_terminate_backend
    ----------------------
     t
     t
    (2 rows)
    

    Check active sessions in PostgreSQL

    The closest equivalent to this oracle query:
    SELECT USERNAME, COUNT(*) 
    FROM v$session 
    WHERE type <> 'BACKGROUND' 
    GROUP BY username;
    
    Would be
    SELECT datname, usename AS username, COUNT(*)
    FROM pg_stat_activity
    WHERE backend_type = 'client backend'
    GROUP BY datname, usename
    ORDER BY datname, usename;
    
    Example output:
     datname  |     username      | count
    ----------+-------------------+-------
     postgres | postgres          |     1
     postgres | postgres_exporter |     1
     mydb     | myser             |     2
    (3 rows)
    

    Thursday, September 18, 2025

    Convert all contents in a file to lower case using vim

    Open your file in vi
    vi myfile
    
    Press the escape key to enter interactive mode, then type
    :%s/.*/\L&/
    
    Press escape, then save + exit the editor:
    :wq
    

    Tuesday, September 9, 2025

    How to set echo in a SQL script executed in psql

    Use one of two options 1. in the sql file, add the following directive
    \set ECHO queries
    
    2. execute the script using the -e flag
    psql -e -f myfile.sql
    

    Monday, September 8, 2025

    Syntax for drop/recreate of constraints in PostgreSQL

    1. Find the constraints belonging to a specific table:
    SELECT conname
    FROM pg_constraint
    WHERE conrelid = 'mytable'::regclass;
        conname
    ----------------
     sys_c002733781
     sys_c002733782
    (2 rows)
    
    2. Drop them:
    ALTER TABLE mytable DROP CONSTRAINT sys_c002733781;
    ALTER TABLE
    ALTER TABLE mytable DROP CONSTRAINT sys_c002733782;
    ALTER TABLE
    
    3. Load your data using your preferred method

    4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):
    alter table mytable add constraint sys_c002733781 foreign key(job_id) references jobs(id) on delete cascade not valid;
    ALTER TABLE
    
    alter table mytable add constraint sys_c002733782 foreign key(task_id) references tasks(id) on delete cascade not valid;
    ALTER TABLE
    
    5. Validate constraints:
    alter table mytable validate constraint sys_c002733781;
    ALTER TABLE
    alter table mytable validate constraint sys_c002733782;
    ALTER TABLE
    

    Friday, September 5, 2025

    Query equivalent to SELECT * FROM USER_OBJECTS in PostgreSQL

    SELECT schema_name, object_name, object_type
    FROM (
        -- Tables, Views, Indexes, etc.
        SELECT
            n.nspname AS schema_name,
            c.relname AS object_name,
            CASE c.relkind
                WHEN 'r' THEN 'TABLE'
                WHEN 'v' THEN 'VIEW'
                WHEN 'm' THEN 'MATERIALIZED VIEW'
                WHEN 'i' THEN 'INDEX'
                WHEN 'S' THEN 'SEQUENCE'
                WHEN 'f' THEN 'FOREIGN TABLE'
                WHEN 'p' THEN 'PARTITIONED TABLE'
                WHEN 'I' THEN 'PARTITIONED INDEX'
                ELSE c.relkind::text
            END AS object_type
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = current_schema()
    
        UNION ALL
    
        -- Functions
        SELECT
            n.nspname AS schema_name,
            p.proname AS object_name,
            'FUNCTION' AS object_type
        FROM pg_proc p
        JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = current_schema()
    ) AS objects
    ORDER BY object_type, object_name;
    
    It only shows objects in the current schema (like Oracle USER_OBJECTS).

    If you want all objects the user owns (across schemas), replace
    WHERE n.nspname = current_schema()
    
    with
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')