Getting started with PostgreSQL on Linux

This post contains some introductory guidance and commands to get you started and productive with PostgreSQL in a few minutes. I’ve written it as a handy reference because I always forget most of these commands and then end up trawling the web to piece them all back together again.

Note: This was written and tested against PostgreSQL 11.7 on Debian GNU/Linux but most of it should still be generally applicable to other versions and operating systems.

Right, let’s get started!

Installation

The installation instructions below are for Debian. For other operating systems see here.

Before installing PostrgreSQL we need to run a few commands to verify that we’re installing the official package, then we can run the installation.

To do all of this, open up your terminal and enter the following commands:

$sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt$(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -$ sudo apt-get update
$sudo apt-get install postgresql  Confirm you’re happy with the installation size when prompted, wait a few minutes and PostgreSQL should now be installed on your machine. Getting started Postgres creates a postgres super user while installing. You’ll need to switch over to this user from your current Linux user account in order to access the psql client. You can switch over to the postgres user like this: $ sudo -i -u postgres


You should now see the postgres username somewhere in your command prompt, depending on your distro. To return to your previous user from here you can just type exit.

Run psql

Psql is a tool for interacting with Postgres via the command-line. Once you’ve switched to the postgres user as per above, then you can start up psql like this:

$psql postgres=#  You should now see the default postgres=# prompt, this means psql is ready to accept commands or SQL queries. Note: This prompt is included in code examples below but you do not need to enter it as part of any commands, it’s just there for context. Some SQL query examples below won’t show this prompt because it would make the formatting of longer queries unnecessarily messy for this post . Exit psql To exit psql, enter the command: \q postgres=# \q  Check version To check what version of Postgres you’re running you can use the following: postgres=# SELECT version(); PostgreSQL 11.7 (...more info about Linux distro etc.)  Users This section covers the basics on listing, creating, altering and deleting users. List users postgres=# \du  Create user CREATE USER username WITH PASSWORD 'secret123' CREATEDB CREATEUSER;  The above SQL query creates a user called ‘username’ with the password ‘secret123’ and gives this user permissions to create databases and users. More detail on creating users can be found here. Alter user ALTER USER username WITH PASSWORD '2ks9Fl297XljdyA' VALID UNTIL '2021-01-01' NOCREATEDB NOCREATEUSER;  This user has now had their password changed to something more secure and will become invalid just before the first second of 2021 has elapsed. It’s permissions to create databases and users have also been removed. More detail on altering users can be found here. Delete user DROP USER username;  If the username contains special characters then you need to enclose it in double quotes. Databases This section covers the basics of working with databases at a high-level. Working with tables within a database is addressed lower down. List databases User the command \list or \l to have a look at some of the default databases: postgres=# \list  Create database Let’s now create our own database called ‘mystore’: CREATE DATABASE mystore;  Connect to database to connect to a database you can use the \c command followed by the database name: postgres=# \c mystore You are now connected to database "mystore" as user "postgres". mystore=#  Notice that‌ your prompt now changes to reflect the name of the database you’re connected to. Delete database DROP DATABASE dbname;  Note: you cannot delete a database that has active connections, so if you’re connected to it you’ll need to connect to another database before you can run this command. Tables Create table Your new database won’t have any tables at this point, here’s an example of a query to create a table with columns of varying data types. CREATE TABLE customers ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, passwd VARCHAR(50) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, verified BOOL DEFAULT false, total_spend FLOAT DEFAULT 0.0, points INT DEFAULT 0, categories VARCHAR(50) [], extra JSON, created TIMESTAMP NOT NULL DEFAULT NOW() );  Below is a list of data types that you can use when creating columns. Data type Desscription CHAR(n) Fixed-length character field with a limit of n. Any remaining character count in the length is padded with spaces. VARCHAR(n) Fixed-length character field with a limit of n, without padding of spaces. TEXT Character field with no limit on characters. BOOL or BOOLEAN Can be true, false or NULL. SMALLINT signed 2-byte integer from -32,768 to 32,767 INT signed 4-byte integer from -2,147,483,648 to 2,147,483,647 SERIAL Same as INT except it will automatically increment the previous row’s value. This is commonly used for id fields to auto-increment the value for each new row. FLOAT(n) floating-point number with precision of at least n up to a max of 8 bytes. REAL or FLOAT8 4-byte floating-point number. NUMERIC or NUMERIC(p,s) floating-point number with p digits before decimal point and s digits after the decimal point. DATE Stores date only. TIME Stores time of day only. TIMESTAMP Stores date and time. TIMESTAMPZ Stores date and time with timezone. INTERVAL Stores periods of time. Arrays Allows you to store an array of any valid data types. See the categories column above in the create table example that defines an array field of the TEXT data type. JSON Stores data as plain JSON. Slower to process than JSONB as it requires re-parsing whenever it’s processed. JSONB Stores JSON data in binary format, slower to insert but faster to process and supports indexing. UUID Universally Unique Identifier has better uniqueness than SERIAL and is better for using in cases where the data is publically exposed. For example, it’s better to use UUID's in URLs. There is a lot more that can be done when creating tables, like creating foreign keys and so on. You can find more detail on creating tables here. List tables Now that you have at least one table you can list a database’s tables by being connected to it and running the \dt command. mystore=# \dt  Delete table DROP TABLE tablename;  Rows Insert rows Once we’ve created a table we can then proceed to insert data into selected columns. INSERT INTO customers (username, passwd, email, verified, total_spend, points) VALUES ('morgan', '2h2agf72ad', 'morgan@outlook.com', true, 201.12, 21), ('alex', 'ad2ebc82djef', 'alex@hey.com', true, 357.89, 35), ('sasha', '2ebda4ef12d', 'sasha@gmail.com', false, 430.74, 46);  The indentation above is not required, however it does help to improve the readability of the query. Select rows Selecting all columns and rows from a table is quite straightforward. SELECT * FROM users;  This should produce the following output:  id | username | passwd | email | verified | total_spend | points | categories | extra | created ----+----------+--------------+--------------------+----------+-------------+--------+------------+-------+---------------------------- 1 | morgan | 2h2agf72ad | morgan@outlook.com | t | 201.12 | 21 | | | 2020-07-29 20:49:46.816328 2 | alex | ad2ebc82djef | alex@hey.com | t | 357.89 | 35 | | | 2020-07-29 20:49:46.816328 3 | sasha | 2ebda4ef12d | sasha@gmail.com | f | 430.74 | 46 | | | 2020-07-29 20:49:46.816328  You can select data with conditions data more precisely like this: SELECT (username, total_spend, points) FROM customers WHERE (total_spend >= 300.00 AND verified = true) ORDER BY total_spend DESC;  which will produce an output like this: (alex,357.89,35)  Delete rows Deleting rows is very similar to selecting them, you just use delete instead. DELETE FROM customers WHERE verified = false RETURNING *;  You can also use the RETURNING command to output the rows that have been deleted. In the above example, the * requests all columns for the rows to be returned but if you only wanted specific columns you could replace this with something like (username, email). Exporting If you want to export a database, you can use the pg_dump tool. You’ll need to exit psql to use this. It effectively exports a file of SQL commands that when re-imported will re-create the database exactly as it was. The below example outputs the mystore database dump into a file called ‘mystore_backup.sql’: mystore=# \q$ pg_dump mystore > mystore_backup.sql


This is handy because you can also do things like pipe the output through your preferred compression tool into a compressed file:

$pg_dump mystore | gzip > mystore_backup.gz  Importing To import a database dump first make sure that you’ve created a new empty database, then exit the psql client and from the command line you can use the psql command again to import the dump into your newly created database. $ psql
postgres=# CREATE DATABASE new_store;
postgres=# \q
\$ psql new_store < mystore_backup.sql


Okay, so that should be enough to get you started for now. Once you’re comfortable with eveything above you might want to look into creating relationships between tables using FOREIGN KEY columns and then using these to perform JOIN queries between multiple tables.

If you have any questions, comments or tips of your own, feel free to leave them below. Otherwise, I’ll update this guide in future with any more info that might be helpful here.