• Skip to primary navigation
  • Skip to main content

Luc Russell

Data Engineering and Full Stack Development

  • Home
  • Blog

PostgreSQL Quick Reference

01/01/2017 by Luc

Connect

1
2
$ psql -h localhost -d mydb -U myuser
 

List Databases

From within a psql session:

1
2
l
 

List Tables

1
2
dt
 

Connect To DB

1
2
c myuser
 

Execute SQL in a File

1
2
$ psql -U myuser -d mydb -a -f myfile.sql
 

Run a Query

1
2
$ psql -U myuser -d mydb -c 'SELECT * FROM my_table'
 

Output to File

Example shows opening a file, listing tables to the file, and finally closing the file:

1
2
3
4
db=>o out.txt
db=>dt
db=>q
 

Get PostgreSQL Logs

1
2
db=> show log_destination;
 

See this reference.

Check Running Queries

1
2
3
4
5
SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE  query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
 

See also this reference.

Terminate a Running Query

1
2
select pg_terminate_backend(pid int)
 

Check Locks

See this reference.

Do Something for Every Child Table

1
2
3
4
5
6
7
8
9
10
11
12
DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN select c.relname as tablename from pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid) JOIN pg_class AS p ON (inhparent=p.oid) WHERE p.relname = 'my_table'
    LOOP
        EXECUTE 'drop TABLE ' || quote_ident(row.tablename);
    END LOOP;
END;
$$;
 

Insert Sample Data with Incrementing Values

1
2
3
4
insert into my_table (name, thing, id)
select 'user1', 'test' || x.id, 1
    from generate_series(40, 100, 1) as x(id);
 

Check the generate_series documentation for what the parameters mean.

Convert a String to a Timestamp

1
2
to_timestamp('2015-04-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 

Partitioning

Refer to this example for how to partition tables for performance.

Filed Under: postgres Tagged With: postgres

Copyright ©2019 Luc Russell | Site by Sproutee

Copyright © 2019 · Aspire Pro on Genesis Framework · WordPress · Log in