Postgres psql Quick Reference

December 19, 2009

psql commands

  • \q - quit
  • \l - list databases
  • \d - list schema objects
  • \dt - list tables
  • \g [filename] - output results of query buffer to a file
  • \o [filename] - output results of session to a file
  • \i [filename] - run commands in file

Altering Table

  • Rename Column - alter table contests_contest rename column "order" to ordering;
  • alter column width - alter table pan_column_data_types alter column pcd_data_type type varchar(40);

User Admin

  • alter user username createdb;
  • create user username with password 'password';
  • grant all on database database to username;


  • select datname from pg_database - list databases
  • select datname,usename,procpid,client_addr,query_start,current_query from pg_stat_activity - active queries (you need to turn on stats_command_string = true in postgresql.conf)

  • List database encodings: $ psql -l --echo-hidden


Oracle has a sysdate function to return the current date/time. In postgres this is current_date or current_time. This query selects events that are more than 14 days in the past, but are in the same week of the year as the current date:

SELECT id, name, to_char(date_of_event, 'WW') 
FROM contests_contestevent 
WHERE to_char(date_of_event, 'WW') = to_char(current_date, 'WW') 
AND to_char(date_of_event, 'DD') != '01' 
AND date_of_event < current_date - 14;

Tags: postgres psql