Developer's Guide to PostgreSQL on Linux: psql shell

By Adam McQuistan in PostgreSQL  09/29/2019 Comment

PostgreSQL psql Shell

Introduction

This is the second article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I introduce the psql interactive shell using the default postgres user.

Series Contents

Connecting to the psql Interactive Shell with postgres Linux User

Once the PostgreSQL service has been installed and the cluster has been initialized as described in Developer's Guide to PostgreSQL on Linux: Installation a Linux system user account is created named postgres. The postgres user serves as the database cluster's superuser.

# id postgres
uid=111(postgres) gid=116(postgres) groups=116(postgres),115(ssl-cert)

The postgres superuser is usually used to do administrative tasks like creating applications databases and users (also referred to as user roles).

Once logged into either the Ubuntu or CentOS server with PostgreSQL installed and initialized I can switch to the postgres user to interact with the default database cluster using the handy psql interactive shell client program.

# su - postgres
$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# 

When you execute the psql command as the postgres user the default behavior is to connect to the postgres database. In a fashion similar to how standard Linux terminals display a prompt symbol of # for the root user or $ for all other users, the psql shell displays a $ prompt symbol for the postgres superuser and a $ for all other users.

The psql program has a number of useful commands for inspecting and interacting with the contents of the database cluster.

Listing Databases

By default a PostgreSQL customer comes with the three databases postgres, template1 and template0. To list the databases present use the \l command.

postgres=# \l
                                List of databases
     Name      |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
---------------+----------+----------+---------+---------+-----------------------
 postgres      | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0     | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |          |          |         |         | postgres=CTc/postgres
 template1     | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |          |          |         |         | postgres=CTc/postgres
(3 rows)

Listing the User Roles of the PostgreSQL Cluster

To list the user roles available in the cluster use the \du command.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Executing SQL Statements From a File

Often times it is useful to execute a set of previously generated SQL commands from a file. With the psql program this is accomplished with \i filename as I show below to build a movies database. You can download the movies_db.sql file used in this tutorial series if you would like to follow along.

postgres=# \i movies_db.sql

Now when I list the contents of the cluster I see a new database named movies.

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 movies    | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

Switching Connections to Another Database

To switch connections to another database within the PostgreSQL cluster use \c dbname

postgres=# \c movies
You are now connected to database "movies" as user "postgres".
movies=# 

Listing Tables, View and Sequences

To list the tables views or sequences in the database you are connected to issue the \d command

movies=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner   
--------+---------------+----------+----------
 public | genres        | table    | postgres
 public | genres_id_seq | sequence | postgres
 public | movies        | table    | postgres
 public | movies_id_seq | sequence | postgres
(4 rows)

To list the contents of a table issue the command \d tablename

movies=# \d genres
                                    Table "public.genres"
 Column |          Type          | Collation | Nullable |              Default               
--------+------------------------+-----------+----------+------------------------------------
 id     | integer                |           | not null | nextval('genres_id_seq'::regclass)
 name   | character varying(100) |           |          | 
Indexes:
    "genres_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "movies" CONSTRAINT "movies_genre_fx" FOREIGN KEY (genre_id) REFERENCES genres(id)

movies=# \d movies
                                     Table "public.movies"
  Column  |          Type          | Collation | Nullable |              Default               
----------+------------------------+-----------+----------+------------------------------------
 id       | integer                |           | not null | nextval('movies_id_seq'::regclass)
 title    | character varying(200) |           | not null | 
 year     | integer                |           |          | 
 runtime  | integer                |           |          | 
 genre_id | integer                |           |          | 
Indexes:
    "movies_pkey" PRIMARY KEY, btree (id)
    "movies_title_key" UNIQUE CONSTRAINT, btree (title)
Foreign-key constraints:
    "movies_genre_fx" FOREIGN KEY (genre_id) REFERENCES genres(id)

Listing Functions and Stored Procedures

To list available functions or stored procedures in a database use \df

movies=# \df
                                                        List of functions
 Schema |    Name    | Result data type |                               Argument data types                               | Type 
--------+------------+------------------+---------------------------------------------------------------------------------+------
 public | save_movie |                  | title character varying, year integer, runtime integer, genre character varying | proc
(1 row)

Showing the Definition of a Function or Stored Procedure

To display the definition of functions or stored procedures issue the command \sf function_name

movies=# \sf save_movie
CREATE OR REPLACE PROCEDURE public.save_movie(title character varying, year integer, runtime integer, genre character varying)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
  genre_id INTEGER;
  movie_id INTEGER;
BEGIN
  SELECT id INTO genre_id FROM genres WHERE name = genre;
  
  IF genre_id IS NULL THEN
    INSERT INTO genres (name) VALUES (genre) RETURNING id INTO genre_id; 
    RAISE NOTICE 'Created: genres(id=%, genre=%)', genre_id, genre;
  END IF;

  INSERT INTO movies ("title", "year", "runtime", "genre_id")
  VALUES (title, year, runtime, genre_id)
  RETURNING id INTO movie_id;

  RAISE NOTICE 'Created: movies(id=%, title=%, year=%, runtime=%, genre_id=%)',
    movie_id, title, year, runtime, genre_id;

  COMMIT;
  
END $procedure$

Quitting the psql Shell

To quit the psql interactive shell type \q in the shell.

movies=# \q
$

Resources for Learning More About PostgreSQL

Conclusion

In this article I demonstrated how to use the psql shell to display and interact with the contents of the PostgreSQL cluster such as databases, tables, and functions or stored procedures. The psql program is a very powerful and full featured tool that does much more than I introduced in this article. I invite the reader to explore the docs and experiment with it further.

Share with friends and colleagues

[[ likes ]] likes

Community favorites for PostgreSQL

theCodingInterface