postgres quick reference

command line reference

\l list databases
\c DATABASE NAME connect/switch to database
\dt list tables
\d TABLE NAME describe table
\o FILE NAME \o save query result to a file
\! shell command run external shell command

You can do \l to list all the databases, use \c to connect to one database, use \d to list tables in this database

SQL Reference

/* multiline comments
 * foo bar
 */

-- create table
CREATE TABLE cities (
    name            varchar(80) primary key,
    location        point
);
CREATE TABLE weather (
    city            varchar(80) references cities(name), --foreign key
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

-- insert into table
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

-- drop table
DROP TABLE TABLE_NAME;

-- drop column
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

-- rename column
ALTER TABLE oneview_incomeprofile RENAME COLUMN business_unit_id TO adviser_business_unit_id;
ALTER TABLE oneview_incomeprofile RENAME COLUMN adviser_business_unit_id TO business_unit_id;

-- example table for testing
CREATE TABLE test (x int, y varchar(256));
INSERT INTO test VALUES (1, 'a'), (2, 'b'), (3, 'c');
SELECT * FROM test;
--  x | y 
-- ---+---
--  1 | a
--  2 | b
--  3 | c