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;

Metadata

• 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

SYSDATE

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;