Postgres psql essential commands

postgres-postgresql-psql-essential-useful-commands.md

Useful Postgres (psql) commands

Essential psql Commands.

Create new DB and new user for that DB

Create the new database my_db:

CREATE DATABASE my_db;

Create the new user my_user and set a password for the user:

CREATE USER my_user WITH PASSWORD 'your_password_here';

Replace your_password_here with the actual password you want to set.

Grant all privileges on the my_db database to my_user:

GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user;

Connection

Connect to psql database:

psql -U username -h https://example.com -p 1234 -d someDB

Connect to Local Database:

psql -d <db-name> -U <username> -W`

Connect to Remote Database:

psql -h <db-address> -d <db-name> -U <username> -W

Connect with SSL:

psql "sslmode=require host=<db-address> dbname=<db-name> user=<username>"

List Databases:

\l

Switch Database:

\c <db-name>

List Tables:

\dt

List JUST the table names and nothing else:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

List tables by creation date:

SELECT
  tablename AS "Table Name",
  create_date AS "Creation Date"
FROM (
  SELECT
    t.tablename,
    t.schemaname,
    t.tableowner,
    t.tablespace,
    c.relfilenode,
    c.create_date,
    ROW_NUMBER() OVER (PARTITION BY t.schemaname, t.tableowner ORDER BY c.create_date DESC) AS r
  FROM
    pg_tables t
  JOIN (
    SELECT
      c.relname AS tablename,
      n.nspname AS schemaname,
      u.usename AS tableowner,
      c.relfilenode,
      pg_stat_file(c.reltablespace || '/' || c.relfilenode) AS create_date
    FROM
      pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_user u ON c.relowner = u.usesysid
    WHERE
      n.nspname NOT LIKE 'pg_%'
      AND n.nspname != 'information_schema'
      AND c.relkind = 'r'
  ) c ON t.tablename = c.tablename
  AND t.schemaname = c.schemaname
) ranked
WHERE
  ranked.r = 1
ORDER BY
  "Creation Date" DESC;

Describe Table:

\d <table-name>

More Info:

\d+ <table-name>

List Schemas:

\dn

Get all NOT NULL columns for a table:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'your_table_name'
  AND is_nullable = 'NO';

List Users and Roles:

\du

Specific User:

\du <username>

List Functions:

\df

List Views:

\dv

Save Query Results:

\o <file-name>

Stop Saving:

\o

In PostgreSQL's command-line utility psql, the command \o is used to stop saving query results to an output file. It essentially turns off the output file redirection.

When you use \o without specifying a filename, it stops saving query results to any previously specified output file. If you were redirecting query results to a file using \o filename, \o alone will stop saving results to that file.

Here's how you can use it:

To start saving query results to a file, you use \o filename, where filename is the name of the file where you want to save the results. For example:

\o output.txt
SELECT * FROM some_table;

This would save the results of the SELECT query to the output.txt file.

To stop saving query results to a file, you simply type \o without specifying a filename:

\o

After this command, query results will no longer be saved to any file, and they will be displayed on the terminal instead.

So, \o is a convenient way to toggle the output file redirection on and off during a psql session.

Run Commands from File:

\i <file-name>

Quit psql:

\q

Add a 'NOT NULL' constraint to a column

ALTER TABLE my_table
ALTER COLUMN my_column SET NOT NULL;

Add an enum[] constraint

CREATE TYPE my_enum AS ENUM (
    'Apartment',
    'Condo',
    'House',
    'Townhouse',
    'Villa'
);
ALTER TABLE my_table
ALTER COLUMN type_col SET DATA TYPE my_enum
USING type_col::my_enum;

Add value or item to custom enum[]

ALTER TYPE my_enum
ADD VALUE 'Cottage';

Delete commands

The content of the table/tables in PostgreSQL database can be deleted in several ways.

Delete a single record from a table based on a condition:

DELETE FROM table_name WHERE id=123;

Delete ALL records from a table and truncate:

TRUNCATE table_name;

OR:

DELETE FROM table_name;

Deleting content of named tables and tables that reference to them:

TRUNCATE table_a, table_b CASCADE;

Get meta and other data

SELECT schemaname, sequencename, increment_by FROM pg_sequences;

Get table information

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'my_table';

Get a table's primary key

SELECT               
  pg_attribute.attname, 
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
WHERE 
  pg_class.oid = 'my_table'::regclass AND 
  indrelid = pg_class.oid AND 
  nspname = 'public' AND 
  pg_class.relnamespace = pg_namespace.oid AND 
  pg_attribute.attrelid = pg_class.oid AND 
  pg_attribute.attnum = any(pg_index.indkey)
 AND indisprimary;

Get a table's foreign key constraints

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema='myschema'
    AND tc.table_name='mytable';

More detailed view of sequences:

SELECT schemaname,
       sequencename,
       last_value,
       start_value,
       increment_by,
       max_value,
       min_value
FROM pg_sequences;

Get more info about a FK constraint:

SELECT
    conname AS constraint_name,
    connamespace AS schema_oid,
    nspname AS schema_name,
    conrelid::regclass AS table_name,
    a.attname AS column_name,
    confrelid::regclass AS foreign_table_name,
    af.attname AS foreign_column_name
FROM
    pg_constraint c
JOIN
    pg_namespace n ON n.oid = c.connamespace
JOIN
    pg_class t ON t.oid = c.conrelid
JOIN
    pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = t.oid
JOIN
    pg_class ft ON ft.oid = c.confrelid
JOIN
    pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = ft.oid
WHERE
    conname = 'fk_constraint_name';

Modify existing tables

Add a column to a table:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Additional Must-Know Commands

Execute SQL Query:

Lists all psql commands:

\?

Help on specific SQL command:

\h <SQL_command>

Clear Screen:

\! clear

Show Current User:

\echo :USER

Show Current Database:

\echo :DBNAME

Show All Settings:

\set

Show history:

\s

List Indexes:

\di

List Sequences:

\ds

Edit Last Query in Text Editor:

\e

Show Query Execution Time:

\timing

Count records

Count total number of records for a table in psql:

SELECT count(*) AS exact_count FROM public.table_name;

Conditionally count records for tables

DO $$              
DECLARE                                                                              
    table_name text;                                                          
    record_count bigint;                                                                                           
BEGIN                       
    FOR table_name IN
        SELECT c.table_name
        FROM information_schema.columns c
        WHERE c.column_name = 'id'
    LOOP                                       
        EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO record_count;
        RAISE NOTICE 'Table % has % records.', table_name, record_count;
    END LOOP;                                                           
END $$; 

Conditionally check for tables by name and print PK details

DO $$
DECLARE
    tbl_name text;
    pk_col_name text;
    pk_col_data_type text;
    pk_col_length integer;
BEGIN
    FOR tbl_name IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
    LOOP
        -- Define the list of table names to exclude
        IF tbl_name NOT IN ('table_1', 'table_2') THEN
            RAISE NOTICE 'Table name to be considered: %', tbl_name;
           
            -- Query to fetch primary key column information
            SELECT column_name, data_type, character_maximum_length
            INTO pk_col_name, pk_col_data_type, pk_col_length
            FROM information_schema.columns
            WHERE table_name = tbl_name
              AND column_name IN (
                SELECT column_name
                FROM information_schema.table_constraints AS tc
                JOIN information_schema.constraint_column_usage AS ccu
                  ON tc.constraint_name = ccu.constraint_name
                WHERE tc.constraint_type = 'PRIMARY KEY'
                  AND tc.table_name = tbl_name
              );
            
            RAISE NOTICE 'Primary Key Column Name: %, Data Type: %, Length: %', pk_col_name, pk_col_data_type, pk_col_length;
            
            -- You can perform additional actions here if needed
        END IF;
    END LOOP;
END $$;

Drop table if meets condition

EXECUTE 'DROP TABLE IF EXISTS ' || tbl_name || ' CASCADE';

Other conditions

WHERE column_name = 'id'
  AND data_type = 'character varying'
  AND character_maximum_length = 36

Search for tables with a partial col name string match

DO $$
DECLARE 
    tbl_name text;
    col_name text;
    query text;
    find_me text := 'otp_policy_window'; -- Declare and set the variable
BEGIN 
    FOR tbl_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public') 
    LOOP 
        FOR col_name IN (SELECT column_name FROM information_schema.columns WHERE table_name = tbl_name AND column_name ILIKE '%' || find_me || '%') -- Use the variable in the query
        LOOP 
            query := 'SELECT * FROM ' || tbl_name || ' WHERE ' || col_name || '::text ~ ' || quote_literal(find_me); -- Pass the variable into the query
            EXECUTE query;
            RAISE NOTICE 'Matching col found in table: "%"', tbl_name;
        END LOOP;
    END LOOP; 
END $$;

Drop ALL tables

Get a list of all tables in the public schema:

SELECT 'drop table if exists "' || tablename || '" cascade;' 
   FROM pg_tables
   WHERE schemaname = 'public';

Then copy and paste the output to drop the desired tables.

UTC timestamps for the Postgres tables

One way to do this is by creating a custom data type with the desired default value, and then using that data type when defining your columns. However, PostgreSQL does not allow you to specify expressions like CURRENT_TIMESTAMP AT TIME ZONE 'UTC' directly as default values for columns. Instead, you can create a custom function that returns the current timestamp in UTC and use that function as the default value.

Here's how you can achieve this:

Create a custom function that returns the current timestamp in UTC:

CREATE OR REPLACE FUNCTION utc_now() RETURNS timestamp with time zone AS $$
BEGIN
    RETURN CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
END;
$$ LANGUAGE plpgsql;
-- returns 'CREATE FUNCTION'

Use the utc_now() function as the default value for the created_at column in your table definitions:

CREATE TABLE public.user_images (
    uid character varying(32) NOT NULL,
    filename character varying(255) NOT NULL,
    extension character varying(10) NOT NULL,
    mime_type character varying(50),
    created_at timestamp with time zone DEFAULT utc_now(),
    user_id uuid NOT NULL
);

CREATE TABLE public.users (
    id uuid NOT NULL,
    email character varying(100) NOT NULL,
    first_name character varying(100),
    last_name character varying(100),
    password text NOT NULL,
    created_at timestamp with time zone DEFAULT utc_now()
);