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.

The PostgreSQL cheat sheet provides you with the common PostgreSQL commands and statements that enable you to work with PostgreSQL quickly and effectively. Download PostgreSQL cheat sheet. We provide you with a 3-page PostgreSQL cheat sheet in PDF format.

• PostGIS 2.0.0 pgsql2shp shp2pgsql Cheat Sheet shp2pgsql and pgsql2shp are all located in the bin folder of the PostgreSQL install. Pgsql2shp dumps a postgis database table, view or sql query to ESRI shape file format. USAGE: pgsql2shp OPTIONS database schema.table pgsql2shp OPTIONS database query.
• View the manual. You can view the manual for an older version or download a PDF of a manual from the below table.
• PostgreSQL Cheat Sheet Some things to note about SQL: All SQL statements end in a semicolon. You can separate statements into separate lines, for readability, as long as you declare the end with a semicolon. Capitalizing commands is optional, but highly recommended for readability. Need help, or more explanations?
• PostgreSQL is an object-relational database management system. This site has published excellent Postgresql 8.3 commands cheat sheet. = Download / View at Postgres onLine journal postgresonline.com.

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.

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:

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:

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:

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

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

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

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:

Here’s how we can grant all privileges for public tables to the same 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 REVOKEall of a role’s privileges for all tables:

The same can be done for database privileges:

Last but not least, let’s look at an example of how to revoke database ownership for the 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:

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

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

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:

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

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:

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:

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:

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:

To drop a table and all of its dependent objects:

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

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

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

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

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.

You’ve installed PostgreSQL. Now what? I assume you’ve been given a task that uses psql and you want to learn the absolute minimum toget the job done.

This is both a brief tutorial and a quick reference for the absolute least you need to know about psql. I assume you’re familiar with the command line and have a rough idea aboutwhat database administration tasks, but aren’t familiar with how touse psql to do the basics.

View on GitHub Pages or directly on GitHub

The PostgreSQL documentation is incredibly well written and thorough, but frankly, I didn’t know where to start reading. Thisis my answer to that problem.

If you have any complaints or suggestions please let me know by sending your feedback to tomcampbell@gmail.com.

It shows how to do the following at the psql prompt:

• Reference pointing to the official PostgreSQL documentation

If you don’t have access to a live PostgreSQL installation at the moment we still have your back.You can follow through the examples and the output is shown as if youdid type everything out.

The psql command line utility

Many administrative tasks can or should be done on your local machine, even though if database lives on the cloud.You can do some of them through a visual user interface, but that’s not covered here. Knowing how to perform these operations on the command line means you can script them,and scripting means you can automate tests, check errors, and do data entry on the command line.

This section isn’t a full cheat sheet for psql.It covers the most common operations and shows them roughly in sequence, as you’d use them in a typical work session.

Postgresql Command Line Cheat Sheet Pdf

What you need to know

Before using this section, you’ll need:

• The user name and password for your PostgreSQL database
• The IP address of your remote instance

Command-line prompts on the operating system

The $ starting a command line in the examples below represents your operating system prompt. Prompts are configurable so it may well not look like this. On Windows it might look like C:Program FilesPostgreSQL> but Windows prompts are also configurable.

A line starting with # represents a comment. Same for everything to the right of a #. If you accidentally type it or copy and paste it in, don’t worry. Nothing will happen.

Using psql

You’ll use psql (aka the PostgreSQL interactive terminal) most of all because it’s used to create databases and tables, show information about tables, and even to enter information (records) into the database.

Quitting pqsql

Before we learn anything else, here’s how to quit psql and return to the operating system prompt.You type backslash, the letter q, and then you press the Enter or return key.

This takes you back out to the operating system prompt.

Opening a connection locally

A common case during development is opening a connection to a local database (one on your own machine).Run psql with -U (for user name) followed by the name of the database, postgres in this example:

Opening a connection remotely

To connect your remote PostgreSQL instance from your local machine, use psql at your operating system command line.Here’s a typical connection.

Here you’d enter the password. In case someone is peering over your shoulder, the characters are hidden. After you’ve entered your information properly you’ll get this message (truncated for clarity):

Looking at the psql prompt

A few things appear, then the psql prompt is displayed.The name of the current database appears before the prompt.

At this point you’re expected to type commands and parameters into the command line.

psql vs SQL commands

psql has two different kinds of commands. Those starting with a backslashare for psql itself, as illustrated by the use of q to quit.

Those starting with valid SQL are of course interactive SQL used tocreate and modify PostgreSQL databases.

Warning: SQL commands end with a semicolon!

One gotcha is that almost all SQL commands you enter into psql must end in a semicolon.

• For example,suppose you want to remove a table named sample_property_5. You’d enter this command:

It’s easy to forget. If you do forget the semicolon, you’ll see this perplexing prompt.Note that a [ has been inserted before the username portion of the prompt, and anotherprompt appears below it:

When you do, just remember to finish it off with that semicolon:

Scrolling through the command history

• Use the up and down arrow keys to move backwards and forwards through the command history.

Getting information about databases

These aren’t SQL commands so just press Enter after them. Remember that:

• When there’s more output than fits the screen, it pauses. Press space to continue
• If you want to halt the output, press q.

h Help

You’ll get a long list of commands, then output is paused:

• Press space to continue, or q to stop the output.

You can get help on a particular item by listing it after the h command.

• For example, to get help on DROP TABLE:

You’ll get help on just that item:

l List databases

What most people think of as a database (say, a list of customers) is actually a table. A database is a set of tables, information about those tables, information about users and their permissions, and much more. Some of these databases (and the tables within) are updated automatically by PostgreSQL as you use them.

To get a list of all databases:

You can get info on a single database by following the l prompt with its name.

• For example, to view information about the template0 database:

The output would be:

l+ List databases with size, tablespace, and description

To get additional information on the space consumed by database tablesand comments describing those tables, use l+:

x Expand/narrow table lists

Use x (X for eXpanded listing) to control whether table listings use a wide or narrow format.

Example: Here’s an expanded listing:

Use x on for narrower listings:

c Connect to a database

To see what’s inside a database, connect to it using c followed by the database name. The prompt changes to match the name of the database you’re connecting to.(The one named postgres is always interesting.) Here we’re connecting to the one namedmarkets:

dt Display tables

• Use dt to list all the tables (technically, relations) in the database:

Postgresql Psql Commands

• If you choose a database such as postgres there could be many tables.Remember you can pause output by pressing space or halt it by pressing q.

d and d+ Display columns (field names) of a table

To view the schema of a table, use d followed by the name of the table.

• To view the schema of a table named customerpaymentsummary, enter

To view more detailed information on a table, use d+:

du Display user roles

• To view all users and their roles, use du:

• To view the role of a specific user, pass it after the du command.For example, to see the only tom role:

Creating a database

Postgresql Command Line Cheat Sheet

Before you add tables, you need to create a database to contain those tables.That’s not done with psql, but instead it’s done with createdb(a separate external command; see the PostgreSQL createdb documentation) at the operating system command line:

On success, there is no visual feedback. Thanks, PostgreSQL.

Adding tables and records

Creating a table (CREATE TABLE)

To add a table schema to the database:

And psql responds with:

For more see CREATE TABLE in the PostgreSQL official docs.

Adding a record (INSERT INTO)

• Here’s how to add a record, populating every field:

PostgreSQL responds with:

• Try it again and you get a simliar response.

Adding (inserting) several records at once

• You can enter a list of records using this syntax:

Adding only specific (columns) fields from a record

You can add records but specify only selected fields (also known as columns). MySQL will use common sense default values for the rest.

In this example, only the name field will be populated. The sku column is left blank, and the id column is incremented and inserted.

Two records are added:

PostgreSQL responds with the number of records inserted:

For more on INSERT, see INSERT in the PostgreSQL official docs

Doing a simple query–get a list of records (SELECT)

Probably the most common thing you’ll do with a table is to obtain information about itwith the SELECT statement. It’s a huge topic

Pg Sql Command Line

• Let’s list all the records in the product table:

The response:

Note

If your table has mixed case objects such as column names or indexes, you’ll need to enclose them in double quotes. For example, If a column name were Product instead of product your query would need to look like this:

For more on SELECT, see the SELECT in the PostgreSQL official docs.

Maintenance and operations issues

Timing

t Timing SQL operations

Use t to show timing for all SQL operations performed.

Example of t Timing command

Watch

The watch command repeats the previous command at the specified interval.To use it, enter the SQL command you want repeated, thenuse watch followed by the number of seconds you want forthe interval between repeats, for rexample, watch 1to repeat it every second.

Example of the Watch command

Here’s an example of using watch to see if any records have beeninserted within the last 5 seconds.

Locate the pg_hba.conf file

Postgres configuration is stored in a file named pg_hba.confsomewhere in the file system, butthat location varies widely. The way to find it is to use show hba_file like this:

See below for hot reloading this file while Postgres is running.

Reload the configuration file while Postgres is running

If you make changes to the pg_hba.conf Postgres configuration sometimes you need to restart.But you may just choose to reload the pg_hba.conf configuration file like this:

Reference

• PostgreSQL offical docs: Server Administration
• psql , a.k.a the PostgreSQL interactive terminal
• createdb in the PostgreSQL offical docs
• CREATE TABLE in the PostgreSQL official docs
• INSERT in the PostgreSQL official docs