Postgres sql database cheatsheet

In this post, I am installing postgres on ubuntu and then will perform different sql operations on it. Installation commands may vary with other distributions like CentOS or Mac.

To install:

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

This will also install other dependencies.

To connect to Postgres:

sudo su - postgres

To check the login info, use the following command

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

By default, PostgreSQL will not allow general remote access. To grant permission to connect, you must edit a configuration file, pg_hba.conf. This file lives in the database file area (/etc/postgresql/9.6/main/pg_hba.conf in our example), and contains entries that grant or reject permission for users to connect to the database.

Connect from another system: 

To check network connectivity, you can use psql installed on another machine on the network as a client, or any other PostgreSQL compatible application.

With psql, you specify the host (either the name or IP address) with the -h option, and one of the system databases (as you haven’t yet created a real database):

remote$ psql -h -d db1

Welcome to psql 9.2.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help with psql commands

\g or terminate with semicolon to execute query

\q to quit

db1=# \q

Create DB: $ createdb mydb or $ createdb <db_name>
Remove DB: $ dropdb mydb

To access the database:

postgres@ubuntu:~$psql mydb

To check version info from database:

mydb=# SELECT version();
PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.2.0-8ubuntu3.2) 7.2.0, 64-bit
(1 row)

To test SELECT commands: 

mydb=# SELECT 2 + 2;
(1 row)

mydb=# SELECT current_date;
(1 row)

To create a table: 

mydb=# CREATE TABLE weather (
mydb(# city varchar(80),
mydb(# temp_lo int,
mydb(# temp_hi int,
mydb(# prcp real,
mydb(# date date
mydb(# );

Another table: 

mydb=# CREATE TABLE cities (
mydb(# name varchar(80),
mydb(# location point
mydb(# );

You can use multiple lines to create tables. Query end up with a semicolon );

To remove a table: 

DROP TABLE tablename;

To insert a row in weather table: 

mydb=# INSERT INTO weather VALUES ('San Franscisco', 46, 50, 0.25, '1994-11-27');

It returned 0 1 where 0 is OID (status code means no error) and 1 means 1 row affected.

COPY command to quickly copy multiple queries:

COPY weather FROM '/home/user/weather.txt';

Query table:

mydb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date 
San Franscisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)

To remove duplicate rows:


Here again, the result row ordering might vary. You can ensure consistent results by using DISTINCT and ORDER BY together: [2]

FROM weather
ORDER BY city;

[1] While SELECT * is useful for off-the-cuff queries, it is widely considered bad style in production code, since adding a column to the table would change the results.

[2] In some database systems, including older versions of PostgreSQL, the implementation of DISTINCT automatically orders the rows and so ORDER BY is unnecessary. But this is not required by the SQL standard, and current PostgreSQL does not guarantee that DISTINCT causes the rows to be ordered.

Update between tables: 

You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows:

UPDATE weather
SET temp_hi = temp_hi – 2, temp_lo = temp_lo – 2
WHERE date > ‘1994-11-28’;
Look at the new state of the data:

SELECT * FROM weather;

city | temp_lo | temp_hi | prcp | date
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)

Joins Between Tables



Rows can be removed from a table using the DELETE command. Suppose you are no longer interested in the weather of Hayward. Then you can do the following to delete those rows from the table:

DELETE FROM weather WHERE city = ‘Hayward’;
All weather records belonging to Hayward are removed.

SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
(2 rows)

One should be wary of statements of the form

DELETE FROM tablename;

Without a qualification, DELETE will remove all rows from the given table, leaving it empty. The system will not request confirmation before doing this.

Postgres extensions: 


Suppose the combined listing of weather records and city location is of particular interest to your application, but you do not want to type the query each time you need it. You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table:

SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SELECT * FROM myview;

Foreign eys: 

Consider the following problem: You want to make sure that no one can insert rows in the weather table that do not have a matching entry in the cities table. This is called maintaining the referential integrity of your data. In simplistic database systems this would be implemented (if at all) by first looking at the cities table to check if a matching record exists, and then inserting or rejecting the new weather records. This approach has a number of problems and is very inconvenient, so PostgreSQL can do this for you.

The new declaration of the tables would look like this:

city varchar(80) primary key,
location point

CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date

Now try inserting an invalid record:

INSERT INTO weather VALUES (‘Berkeley’, 45, 53, 0.0, ‘1994-11-28’);
ERROR: insert or update on table “weather” violates foreign key constraint “weather_city_fkey”
DETAIL: Key (city)=(Berkeley) is not present in table “cities”.
The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial, but just refer you to Chapter 5 for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn about them.


Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Let’s create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you’re really clever you might invent some scheme like this:

CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)

CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)



SELECT name, population, altitude FROM capitals
SELECT name, population, altitude FROM non_capitals;
This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this:

name text,
population real,
altitude int — (in ft)

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
In this case, a row of capitals inherits all columns (name, population, and altitude) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. State capitals have an extra column, state, that shows their state. In PostgreSQL, a table can inherit from zero or more other tables.

For example, the following query finds the names of all cities, including state capitals, that are located at an altitude over 500 feet:

SELECT name, altitude
FROM cities
WHERE altitude > 500;
which returns:

name | altitude
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
On the other hand, the following query finds all the cities that are not state capitals and are situated at an altitude over 500 feet:

SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
name | altitude
Las Vegas | 2174
Mariposa | 1953
(2 rows)
Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE, and DELETE — support this ONLY notation.