How to get a list of all the databases in PostgreSQL

You can get a list of all the databases in a PostgreSQL cluster by either using a psql meta command or by executing a query.

PSQL

1. Open psql
2. Type \l (lowercase L) and press Enter

Query

The query to view a list of all databases in a cluster is:

How get a list of all the tables in a PostgreSQL database

You can get a list of all the tables in a PostgreSQL database with the built in psql functions or by executing a query:

PSQL

1. Open psql and log into the database for which you want the list of tables
2. Type \dt and press Enter

Query

You can also query pg_tables to get a list of tables in a database. You can filter on the schema name in the where clause:

How much disk space does a PostgreSQL database, table or index use?

The amount of disk space used by a PostgreSQL database, table or index can be obtained by querying the PostgreSQL data dictionary.

Database size

The query

will return the current database size in bytes.

To view the size in K, MB or GB you can run the query:

Table of Index size

Tables and Indexes are referred to as relations in PostgreSQL. Run the following query to view the size of a relation in bytes:

To view the size in K, MB or GB you can run the query:

How to restore a backup made plain format

PGAdminIII can make backups in three formats:

1. Plain
2. Custom
3, Tar

Custom and Tar backups can be restored with PGAdminIII or pg_restore. Plain backups must be restored with psql.

Option 1:

1. Open psql and log into the database postgres
2. Type CREATE DATABASE databasename;

3. Log into the database by typing \c databasename. (in this example \c test)

4. Type \i path/to/backupfile (in this example \i c:/pgbackup/testplain.backup)

5. The backup will now be restored

Option 2

1. Go to the command prompt (Windows) or the console (Linux) and make sure that the user is the postgres user

2. On Windows: Press Shift and Right Click on the Command Prompt icon and select Run as different user

3. On Linux: Type su postgres to change the user to the postgres user

4. Go to the bin directory (it is a sub directory under PostgreSQL/10)

5. Type createdb databasename (in this example createdb test). This will create the database

6. Type psql databasename < path/to/backupfile (in this example psql test < c:/pgbackup/testplain.backup)

7. The backup will now be restored

Adding a primary key to a table

A properly designed table (in a relational database) will always have a primary key. This is essential to uniquely identify records and to relate to other tables.

Unfortunately there are a lot of databases with tables without a primary key. The good news is that it is very easy to correct. You can either add a new column with a primary key constraint or you can add a primary key constraint to an existing column.

Add a column called id to the table and set it as the primary key

Let's say we have an employee table like this:

We are going to add a column named id and set it as a primary key. The id column will be numbered automatically. Please note that the SQL syntax for Microsoft SQL Server differs from the syntax for MySQL and PostgreSQL. The queries are as follows:

Microsoft SQL Server 2008:

MySQL & PostgreSQL:

The amended employee table will look like this:

Add a primary key constraint to an existing column

A primary constraint can only be added to a column if:

1.            There are only unique values in the column; and

2.            The column is not able to contain NULL values.

If your table looked like this (but id were not a primary key) 

you can execute the following query to add a primary key constraint: