Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PostgreSQL Cheatsheet

PostgreSQL

CLI Utility

Enter the CLI Utility

sudo -u [user] psql

The default user is postgres

Exiting the CLI Utility

\q

It is easier just to use the keyboard shortcut Ctrl + D.

Switching Database

Technically this command does not "switch" database, you are just closing one connection and opening another.

\connect DBNAME

Tables

Create a table

CREATE TABLE broker (
    id serial NOT NULL,
    name varchar(255) NOT NULL,
    redirect_url text NOT NULL,
    secret varchar(30) NOT NULL,
    modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)

There is no AUTO_INCREMENT but a special serial and big_serial type. There is no unsigned type

Creating Tables With Foreign Keys

CREATE TABLE cities (
    city     varchar(80) primary key,
    location point
);

CREATE TABLE weather (
    city      varchar(80) references cities(city),
    temp_lo   int
);

By default, foreign keys will be set to use RESTRICT for both update and delete actions. You can manually specify what you want like so:

CREATE TABLE weather (
    city      varchar(80) references cities(city) ON UPDATE CASCADE ON DELETE RESTRICT,
    temp_lo   int
);

Show Tables

\dt

Show Databases

\list

Inserting Data

CREATE TABLE cities (
    city     varchar(80) primary key,
    location point
);

Insert into cities (city, location)
VALUES 
    ('Houston', '29.7604, -95.3698'),
    ('Dallas', '32.7767, -96.7970')
;

Pay close attention to the use of (or lack of ) various quotation marks.

Updating Data

update cities 
set city='Washington' 
where city='Houston';

Deleting Data

DELETE FROM cities 
WHERE city='Houston';

Misc

Ouput of Nulls

Unlike MySQL which will show "null" as a value when showing data, like so:

mysql> select * from cities;
+---------+----------+
| city    | location |
+---------+----------+
| Houston |     NULL |
+---------+----------+
1 row in set (0.00 sec)

...PostgreSQL will just show emptiness like so:

postgres=# select * from cities;
    city    |      location      
------------+--------------------
 Dallas     | (32.7767,-96.797)
 Washington | (29.7604,-95.3698)
 London     | 
(3 rows)

Users

Create User

CREATE USER programster WITH PASSWORD 'thisismypassword';

By default, when this user logs in, postgresql will try to connect them to the programster database, and they will have the ability to see and create tables in the other databases.