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.