PostgreSQL Cheatsheet

CLI Utility

Enter the CLI Utility

sudo -u username 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)

References

Author

Programster

Stuart is a software developer with a passion for Linux and open source projects.

comments powered by Disqus
We are a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites. More info.