Dump database to a script (.sql) file

Extract/Dump the database contents to a script (.sql) file

pg_dump -f ~/path/to/file.sql $YOUR_DATABASE_URL

Run a script (.sql) from file on a database

Run SQL commands from a file on a database

psql $YOUR_DATABASE_URL -f ~/path/to/file.sql

Check whether there is any row in the table matching your condition

select exists(select 1 from your_table where your_condition)

Use single-quote inside a string

Put two single-quotes (‘) wherever you want to use a single-quote inside the string

select * from people where full_name = 'Travis O''Connor'

Convert timestamptz to a timestamp in a particular timezone

Assuming created_at is a column in table people with type timstamptz and we want to see what those timestamps are in IST (Asia/Kolkata or Asia/Calcutta)

select created_at at time zone 'Asia/Kolkata' as shifted from people

Format timestamp/date

Assuming birthdate is a column in table people with type date and we want to display those values in 10 Oct 2021 format

select to_char(birthdate, 'DD Mon YYYY') from people

Refer to official docs for more and/or latest details

Auto-populate values for created_at and updated_at columns

We first need to create a function which would update the value of updated_at column every time data in a row changes

create or replace function update_modified_timestamp() returns trigger
language plpgsql as
$$
BEGIN
    new.updated_at := current_timestamp;
    return new;
END;
$$;

Then we need to provide default values to columns when defining table schema

create table people (
    id integer primary key,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    name text not null
);

At last we need to add a trigger to each table where we want to auto-update the value of updated_at

create trigger update_timestamp before update on people
for each row execute procedure update_modified_timestamp();

Have a great day people 👋