By Benji Asperheim | 2024-08-29

Useful Postgres (psql) commands

Here are some useful and essential psql commands to perform CRUD operations on PostgreSQL data.

DISCLAIMER: Use these commands with extreme caution.

It is highly recommended to:

1. Back up your database before running any of these commands (especially the UPDATE, DROP, TRUNCATE, and DELETE commands).

2. Double-check the command is the intended action you want to do.

3. Review the output of the SELECT query before executing any DROP statements.

Create new DB and new user for that DB

This examples creates a new database called my_db:

CREATE DATABASE my_db;

Now let's create a new user (called my_user), and set the password for the user:

CREATE USER my_user WITH PASSWORD 'your_password_here';

NOTE: 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;

Connecting to a PostgreSQL Database Using psql

Connect to the database using the psql command with this syntax:

psql -U my_user -h https://example.com -p 1234 -d my_db

Here's a breakdown of each component and flag in the command:

psql: This is the command-line utility for interacting with PostgreSQL databases.

-U my_user: Specifies the username to connect to the database. Replace my_user with your actual PostgreSQL username.

-h https://example.com: Specifies the host where the PostgreSQL server is located. This should be the hostname or IP address of the server. Note that typically, this should be just the hostname or IP address without the protocol prefix (e.g., example.com or 192.168.1.1).

-p 1234: Specifies the port number on which the PostgreSQL server is listening. Replace 1234 with the actual port number configured for your PostgreSQL server (default is 5432).

-d my_db: Specifies the name of the database to connect to. Replace my_db with the name of the database you want to access.

Connect via psql with SSL Mode

You can also pass the sslmode=require flag if a connection requires an SSL cert:

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

Basic 'Slash' Commands in psql

PostgreSQL's psql interface has a number of 'slash' (\) commands you can use to manage and view your data. For example. you can list databases like so:

\l

Switch databases with the \c connect command:

\c

Describe a table with the \d command:

\d

Also, by appending a + to that previous command, you can get more basic information on a table:

\d+

Example output of the \d+ command:

\d+ users
                                                                     Table "public.users"
       Column       |          Type          | Collation | Nullable |              Default              | Storage  | Compression | Stats target | Description 
--------------------+------------------------+-----------+----------+-----------------------------------+----------+-------------+--------------+-------------
 id                 | integer                |           | not null | nextval('users_id_seq'::regclass) | plain    |             |              | 
 is_admin           | boolean                |           | not null | false                             | plain    |             |              | 
 email              | character varying(100) |           | not null |                                   | extended |             |              | 
 first_name         | character varying(100) |           |          |                                   | extended |             |              | 
 last_name          | character varying(100) |           |          |                                   | extended |             |              | 
 encrypted_password | character varying(256) |           | not null |                                   | extended |             |              | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Access method: heap

List all of your PostgreSQL schemas for a database:

\dn

Quit the psql REPL

To quit or exit psql, just input \q:

\q

Listing Users and Roles in Postgres

List the Postgres databases' users and roles:

\du

To get a specific user use the following syntax:

\du

List all functions in a PostgreSQL database:

\df

List all of the view relations:

\dv

Save Query Results to a File using psql

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.

To start saving query results:

\o

Then you can stop saving like so:

\o

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, the \o alone will stop saving results to that file.

After the second \o 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 psql Commands from an SQL File

Run psql commands from file with \i:

\i

Listing Tables in a Postgres Database

List all of a databases tables with \dt:

\dt

List just the table names and nothing else by querying the information_schema meta table:

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

Sorting and Selecting Specific Postgres Table Data

Here's how you list and sort tables by creation date with the ORDER BY clause:

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;

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

Get All Postgres Columns But Omit Some

Here's how you can have Postgres construct a statement of just the columns you want, by blacklisting the few you don't want, and it will return a SELECT SQL statement of them:

SELECT 'SELECT ' || array_to_string(
  ARRAY(SELECT 'o' || '.' || c.column_name
  FROM information_schema.columns AS c
  WHERE table_name='blog_posts' AND  c.column_name NOT IN('content')), ',') || ' FROM blog_posts AS o' AS blog;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------
blog | SELECT o.id,o.title,o.slug,o.markdown_hash,o.github_url,o.description,o.thumbnail,o.author,o.created_at,o.updated_at,o.keywords FROM blog_posts AS o

See this DBA stack exchange post for more details.

Modifying Postgres Table Data

Here are some commands that will help you modify your Postgres data.

Add a 'NOT NULL' Constraint to a Column

This command will apply a NOT NULL constraint to a column in the my_table table:

ALTER TABLE my_table
ALTER COLUMN my_column SET NOT NULL;

Modify existing tables

Add a column to a table:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Get All Constraints For a Table

SELECT conname                                                   
FROM pg_constraint 
WHERE conrelid='public.my_table'::regclass;

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 for psql

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;

You can also just use the DELETE FROM command to truncate a table:

DELETE FROM table_name;

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

TRUNCATE table_a, table_b CASCADE;

Other Meta Data Commands for psql

SELECT schemaname, sequencename, increment_by FROM pg_sequences;

Get specific table information by selecting from the information_schema.columns meta table as well:

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

Find a PostgreSQL Table's Primary Key

Use this command to get the primary key (PK) for a Postgres table:

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='blog_posts'::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;

The above example returns the PK for the 'blog_posts' table:

 attname | format_type 
---------+-------------
 id      | integer
(1 row)

Get a Postgres Table's Foreign Key Constraints

This next command will get the foreign key (FK) constraints for a Postgres table:

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

Additional Must-Know Commands

Execute SQL Query:

Lists all psql commands:

\?

Help on specific SQL command:

\h

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 in a Postgres Table

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 the 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 $$;

Postgres DROP TABLE Example

This DROP TABLE command will perform a cascading "drop" (or deletion) of a table (if it exists):

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

Here are some other conditions you can try:

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

Drop or Delete ALL PostgreSQL Tables

Get a list of all tables in the public schema and DROP them with the following:

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

WARNING: The above psql command will drop all tables in the public schema of your PostgreSQL database. This operation will permanently delete all tables and their associated data. Ensure you fully understand the impact of this command and that it is executed in the correct environment (e.g., a development or testing database rather than a production database).

Search for Postgres Tables in psql

Here's an example that searches for tables with a partial column 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: &quot;%&quot;', tbl_name;
        END LOOP;
    END LOOP; 
END $$;

Get the size of a row (in bytes):

SELECT sum(pg_column_size(t.*)) as filesize, count(*) as filerow FROM my_table as t;

Conclusion

I hope you find some of these psql commands useful. At the very least, this should give you a good overview of how the psql REPL for Postgres works, and how it can be used to query and modify both table and record data.