The PostgreSQL Cheat Sheet (Part 1)

Introduction to the PostgreSQL cheat sheet

If you’re using PostgreSQL to store and query your data, you might find yourself needing to look up the syntax of some common statements and queries. There’s no need to go searching for this information– we’ve rounded up some of the most common SQL statements and queries and created a PostgreSQL cheat sheet for you. This handy reference guide will help you perform many common PostgreSQL tasks using the psql command-line interface.

Note that SQL statements in psql must terminate with a semicolon. This syntax lets Postgres knows where a statement ends; otherwise, PostgreSQL will interpret the next line as an extension of that same command.

Another thing to keep in mind when constructing SQL statements is that PostgreSQL strings must always be enclosed using single quotation marks (e.g. 'string here'). Using double quotes will result in a syntax error.

You can press CTRL+C if you’d like to escape a command or exit from the results of a command.

Prerequisites to using PostgreSQL SQL commands in psql

Before we proceed with our PostgreSQL cheat sheet, let’s review a few prerequisites that are necessary to get the most out of this article. You should have PostgreSQL installed, and you’ll need to have a PostgreSQL role with access to a database in order to execute the SQL statement examples found in this article. You’ll also need to use psql, the command-line interface for PostgreSQL. Use the psql -V command to find out which version of the interface is installed on your machine.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Accessing PostgreSQL using the ‘psql’ command line interface

We can use the following syntax to access a PostgreSQL database on a localhost server. The command should be executed using the psql command-line interface:

psql postgres

The command shown above will try to connect to the PostgreSQL database named postgres.

You can also use the alternative syntax shown below to connect to PostgreSQL using a username, host and database name:

psql -U some_username -h 127.0.0.1 -d some_database

NOTE: Notice that a few different flags are used in this command syntax. The -U flag represents the Postgres username, and the -h flag indicates the host domain or IP address. The -d flag is used to provide the database name. If only one parameter is supplied, psql will assume it is the database name.

PostgreSQL cheat sheet of useful SQL queries and commands

The following section provides an overview of some of the most common SQL commands used to manage and alter Postgres roles, indexes and tables.

PostgreSQL cheat sheet to manage the roles and users

In Postgres, a role is the entity that is granted privileges to access and modify database and table data. A role can be either a user or a group. You can create a new role using the following syntax:

CREATE ROLE new_role;

To add a username and password to the new role, simply use the command:

CREATE ROLE new_role NOINHERIT
LOGIN PASSWORD 'mYpAsSwOrDyO!';

We can assign a new role to our current psql session:

SET ROLE new_role;

The last command we’ll review in this section is used to create a user with an encrypted password:

CREATE USER new_user
WITH ENCRYPTED PASSWORD 'mYpAsSwOrDyO!';

Grant privileges to a Postgres user or role

Now that we know how to create a user or role, let’s discuss how to grant a user privileges. The following command uses the GRANT CONNECT keyword to grant a Postgres user access to a specific database:

GRANT CONNECT ON DATABASE some_db
TO new_user;

Here’s how we can grant all privileges for public tables to the same user:

GRANT ALL PRIVILEGES ON ALL TABLES
IN SCHEMA public TO new_user;

Revoke all privileges for a PostgreSQL user or role

Not only can you grant privileges to a user, but you can also take them away. The following command will reverse the one shown above and REVOKE all of a role’s privileges for all tables:

REVOKE ALL PRIVILEGES ON ALL TABLES
IN SCHEMA public FROM new_user;

The same can be done for database privileges:

REVOKE ALL PRIVILEGES ON DATABASE
some_db FROM new_user;

Last but not least, let’s look at an example of how to revoke database ownership for the user:

DROP OWNED BY new_user;

PostgreSQL cheat sheet commands for table views

In this section, we’ll look at some commands related to views. A PostgreSQL VIEW is a way of logically organizing data that’s meant to represent the column data of tables.

The following example uses the CREATE VIEW SQL keywords. It also uses the AS keyword to give the view database object a name:

CREATE VIEW some_view AS col_data;

The next example creates a view object that consists of the column names col1 and col2:

CREATE VIEW some_view(col1, col2)
AS SELECT col1, col2 FROM some_table;

If you need to DROP, or delete, a view from your database, here’s how to do it:

DROP VIEW [IF EXISTS] some_view;

PostgreSQL cheat sheet commands for indices

In this section, we’ll look at some common commands used to manage indexes. To create an index, we use the following command:

CREATE INDEX some_index ON some_table(col_name, second_col);

The DROP INDEX command is used to remove a specified index from a table:

DROP INDEX index_name;

PostgreSQL cheat sheet commands for Triggers

In SQL, a TRIGGER is a callback or function that will automatically execute a SQL statement when a certain event occurs. The basic structure of a PostgreSQL trigger is shown below:

CREATE TRIGGER some_trigger
[WHEN EVENT] ON some_table
[TRIGGER TYPE] EXECUTE stored_procedure;

NOTE: The WHEN EVENT and TRIGGER TYPE keywords are optional.

PostgreSQL cheat sheet commands for tables

The syntax for the SQL command used to create a new table looks like the following:

CREATE TABLE some_table
(some_column + DATA TYPE + CONSTRAINTS [OPTIONAL]);

Let’s look at an example of how you can use the CREATE TABLE keyword to create a Postgres table comprised of a string column and an integer column:

CREATE TABLE some_table (
id INT PRIMARY KEY,
some_str VARCHAR(30),
some_int INT
);

NOTE: Our example contains the PRIMARY KEY constraint, which tells Postgres to make the value of that particular column the main identifying information for the record.

If you need to rename a Postgres table, you can use the ALTER TABLE and RENAME commands:

ALTER TABLE some_table RENAME TO some_table;

To drop a table and all of its dependent objects:

DROP TABLE [IF EXISTS] some_table CASCADE;

To add a new column to an existing table, use the following command:

ALTER TABLE some_table ADD COLUMN some_column DATA TYPE;

To rename a specific column in an existing table, use the command shown below:

ALTER TABLE some_table RENAME some_column TO some_column;

To drop a column from a table, use the following command:

ALTER TABLE some_table
DROP COLUMN some_column;

To remove the SQL primary key constraint from a table, we use the ALTER TABLE and DROP CONSTRAINT commands:

ALTER TABLE some_table
DROP CONSTRAINT primary_key_constraint_name;

NOTE: Some common examples of a PRIMARY KEY constraint would be NOT NULL or UNIQUE.

Conclusion to the PostgreSQL cheat sheet

If you’re just getting started with PostgreSQL, it may seem like there’s a lot to learn; however, having a PostgreSQL cheat sheet handy can make it easier to perform everyday database tasks. In this article, we covered many of the common commands and queries used in PostgreSQL. In the next installment of this two-part series, we’ll pick up where we left off and look at some more examples of PostgreSQL syntax.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.