Example of working with tables via psql

Daria Barysheva

Overview

The simplest way to work with PostgreSQL tables is to use the psql terminal client. This client allows you to enter data queries, pass them to PostgreSQL, and view the results. Queries can also be received from a file or command line arguments. Psql becomes available on each node of the ADPG cluster immediately after its installation.

To start working with psql, run the following commands that provide the connection to the default database (in our example — postgres) for the default user postgres.

$ sudo su - postgres
$ psql

The psql prompt ends with the # character. All subsequent commands should be written after that character.

psql (14.1)
Type "help" for help.

postgres=#

To logout from psql, use the following command:

\q

The main data operations available in PostgreSQL are listed below. The full list of these and other useful commands with their parameters description you can find in the official documentation.

All examples are based on a simple database that stores information about books and their authors.

Step 1. Create a database

To create a new database, it is necessary to use the CREATE DATABASE statement, then write the database name, and finish the query with the semicolon ;.

IMPORTANT

Do not forget to use the semicolon ; after every query that is passed to PostgreSQL.

The following example creates the new database books_store.

CREATE DATABASE books_store;

The output should be similar to:

CREATE DATABASE

To disconnect from the current database and switch to the new one, you can use the psql command \c:

\c books_store

The result:

You are now connected to database "books_store" as user "postgres".
books_store=#

Step 2. Create a table

To create a new table, you should use the CREATE TABLE statement, then write the table name, describe all table columns (by defining their names, data types, and modifiers), define indexes and other constraints if necessary, and finish the query with the semicolon ;.

The following query creates the author table with two columns:

  • id — the unique author identifier that performs the role of the primary key (and does not support the NULL values by default);

  • name — the text field that does not support the NULL values. It will contain the authors last names and initials.

CREATE TABLE author (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
TIP

In order to provide autoincrementing for the id values, we use the SERIAL data type for this column. But if serial types do not suit your needs (e.g. you want to increase the increment step), you can perform your own autoincrementing functionality by creating a custom SEQUENCE.

The next query creates the book table with four columns:

  • id — the unique book identifier that performs the role of the primary key (and does not support the NULL values by default);

  • title — the text field that does not support the NULL values. It will contain the books titles;

  • author_id — the integer field that does not support the NULL values. It will contain the unique authors identifiers. To explicitly indicate the relationship between the author and book tables, we add the fk_author foreign key based on the author_id column;

  • public_year — the integer field that supports the NULL values. It will contain the books publication years.

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author_id INT NOT NULL,
  public_year SMALLINT NULL,
  CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES author(id));

The result of the both commands is listed below:

CREATE TABLE

Step 3. Get information about the table

To check whether any table exists or not and to get information about it, you can use the following commands:

  • \d <table_name>. It shows the short information about the columns of the <table_name> table: name, type, collation, ability to have the NULL values, and default value. The command also displays the table indexes and primary/foreign keys.

    \d author

    The output is:

                                        Table "public.author"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+------------------------------------
 id     | integer                |           | not null | nextval('author_id_seq'::regclass)
 name   | character varying(100) |           | not null |
Indexes:
    "author_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "book" CONSTRAINT "fk_author" FOREIGN KEY (author_id) REFERENCES author(id)

    For non-existent tables, the command returns the following result:

    books_store=# \d not_existed
Did not find any relation named "not_existed".

  • \d+ <table_name>. In comparison with the \d command, this command returns some additional information about the columns of the <table_name> table: storage and compression types, stats target, description. It also displays the access method applied to the table.

    \d+ author

    The output is:

                                                                   Table "public.author"
 Column |          Type          | Collation | Nullable |              Default               | Storage  | Compression | Stats target | Description
--------+------------------------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
 id     | integer                |           | not null | nextval('author_id_seq'::regclass) | plain    |             |              |
 name   | character varying(100) |           | not null |                                    | extended |             |              |
Indexes:
    "author_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "book" CONSTRAINT "fk_author" FOREIGN KEY (author_id) REFERENCES author(id)
Access method: heap

    For non-existent tables, the command returns the following result:

    books_store=# \d+ not_existed
Did not find any relation named "not_existed".

  • \dt. It returns the list of all relations in the current database. Every relation is displayed with such information as schema, name, type, and owner.

    \dt

    The result:

             List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | author | table | postgres
 public | book   | table | postgres
(2 rows)

  • \dt+. In comparison with the \dt command, this command returns some additional information about the database relations: persistence, access method, size, description.

    \dt+

    The result:

                                    List of relations
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+--------+-------+----------+-------------+---------------+---------+-------------
 public | author | table | postgres | permanent   | heap          | 0 bytes |
 public | book   | table | postgres | permanent   | heap          | 0 bytes |
(2 rows)

Step 4. Insert new data into the table

To add new data to the table, it is necessary to use the INSERT INTO statement, then write the table name, column names, define column values after the VALUES keyword, and finish the query with the semicolon ;. Column names and values are enclosed in parentheses. Values should be specified in the same order as column names.

The following example shows how to insert five new rows into the author table via five different queries.

NOTE
We do not explicitly specify the id value, since it is filled in automatically. 
INSERT INTO author(name) VALUES('Virginia Woolf');
INSERT INTO author(name) VALUES('Harper Lee');
INSERT INTO author(name) VALUES('F. Scott Fitzgerald');
INSERT INTO author(name) VALUES('J.R.R. Tolkien');
INSERT INTO author(name) VALUES('George Orwell');

The result of each of the commands listed above is similar to:

INSERT 0 1

You can insert more than one row via one INSERT query. To do this, define all rows in the comma-separated list after the VALUES keyword.

INSERT INTO book(title, author_id, public_year) VALUES
('Mrs. Dalloway',1,1925),
('To the Lighthouse',1,1927),
('To Kill a Mockingbird',2,1960),
('The Great Gatsby',3,1925),
('The Lord of the Rings',4,1955);

The result:

INSERT 0 5

You can also use subqueries in the INSERT queries. For example, the following code uses a subquery to receive the author identifier by the author name.

INSERT INTO book(title, author_id, public_year) VALUES
('1984',(SELECT id FROM author WHERE name = 'George Orwell'),1949),
('Animal Farm',(SELECT id FROM author WHERE name = 'George Orwell'),1945);

The result:

INSERT 0 2

Step 5. Select the table data

To select data from the table, you should use the SELECT statement, then enter the column names (or the * symbol for receiving all columns), write the table name after the FROM keyword, describe the query conditions in the WHERE clause, and finish the query with the semicolon ;. Optionally, you can use the ORDER, GROUP BY, HAVING, and other standard SQL clauses. Examples of different SELECT queries are listed below.

NOTE
For more details on using queries in PostgreSQL, refer to the official documentation.

Select all data

The following query selects all data from the author table.

SELECT * FROM author;

Since we use * instead of column names and do not fill in the WHERE clause, all rows and columns are returned by this command.

 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
(5 rows)

Use WHERE

The next query selects titles of the books being published in 1925.

SELECT
  title
FROM book
WHERE public_year = 1925;

The result:

      title
------------------
 Mrs. Dalloway
 The Great Gatsby
(2 rows)

Sort the results

The following query returns the titles and publication years for all books stored in the book table. The sorting by the year is used.

SELECT
  title,
  public_year
FROM book
ORDER BY public_year;

The result:

         title         | public_year
-----------------------+-------------
 The Great Gatsby      |        1925
 Mrs. Dalloway         |        1925
 To the Lighthouse     |        1927
 Animal Farm           |        1945
 1984                  |        1949
 The Lord of the Rings |        1955
 To Kill a Mockingbird |        1960
(7 rows)

Group the results

The next query groups all books stored in the book table by the publication year and returns the count of the books published every year. Sorting by the year is performed automatically.

SELECT
  public_year,
  COUNT(*) AS books_count
FROM book
GROUP BY public_year;

The result:

 public_year | books_count
-------------+-------------
        1925 |           2
        1960 |           1
        1945 |           1
        1949 |           1
        1927 |           1
        1955 |           1
(6 rows)

Join tables

The next example joins two tables author and book — in order to display authors names instead of their identifiers stored in the table book.

SELECT
  author.name,
  book.title,
  book.public_year
FROM book INNER JOIN author ON author.id = book.author_id
ORDER BY public_year;

The result:

        name         |         title         | public_year
---------------------+-----------------------+-------------
 F. Scott Fitzgerald | The Great Gatsby      |        1925
 Virginia Woolf      | Mrs. Dalloway         |        1925
 Virginia Woolf      | To the Lighthouse     |        1927
 George Orwell       | Animal Farm           |        1945
 George Orwell       | 1984                  |        1949
 J.R.R. Tolkien      | The Lord of the Rings |        1955
 Harper Lee          | To Kill a Mockingbird |        1960
(7 rows)

Combine SELECT with CREATE and INSERT

You can use the SELECT statements for copying data from one table to another via the CREATE and INSERT commands.

The following query creates the book_copy table with the same structure as the book table has. The query also copies three first rows from the old table to the new one by using the SELECT statement with the WHERE clause.

CAUTION
The CREATE TABLE AS command does not copy the source table constraints. If you need it, use the CREATE TABLE command with the LIKE option. 
CREATE TABLE book_copy
AS SELECT * FROM book WHERE id <= 3;

The result:

SELECT 3

The next query adds the rest rows of the source table to the new one — via the INSERT statement.

INSERT INTO book_copy
SELECT * FROM book WHERE id > 3;

The result:

INSERT 0 4

You can check the results using the following commands. The content of two tables is identical.

books_store=# SELECT * FROM book_copy;
 id |         title         | author_id | public_year
----+-----------------------+-----------+-------------
  1 | Mrs. Dalloway         |         1 |        1925
  2 | To the Lighthouse     |         1 |        1927
  3 | To Kill a Mockingbird |         2 |        1960
  4 | The Great Gatsby      |         3 |        1925
  5 | The Lord of the Rings |         4 |        1955
  6 | 1984                  |         5 |        1949
  7 | Animal Farm           |         5 |        1945
(7 rows)
books_store=# SELECT * FROM book;
 id |         title         | author_id | public_year
----+-----------------------+-----------+-------------
  1 | Mrs. Dalloway         |         1 |        1925
  2 | To the Lighthouse     |         1 |        1927
  3 | To Kill a Mockingbird |         2 |        1960
  4 | The Great Gatsby      |         3 |        1925
  5 | The Lord of the Rings |         4 |        1955
  6 | 1984                  |         5 |        1949
  7 | Animal Farm           |         5 |        1945
(7 rows)

Step 6. Update the table data

To update one or more columns in the specified table rows, you should use the UPDATE statement, then write the table name, define the comma-separated list of the columns that should be updated (using the format <column_name> = <new_value>[, …​] in the SET section), describe the query conditions in the WHERE clause, and finish the query with the semicolon ;.

The following query inserts the new row into the author table.

INSERT INTO author(name) VALUES('Test author');

The output is:

INSERT 0 1

The next query updates the new row by adding the _updated postfix to the value of the name column.

UPDATE author
SET name = CONCAT(name, '_updated')
WHERE name = 'Test author';

The output is:

UPDATE 1

You can check the command results by selecting the table data. The column value is successfully updated.

books_store=# SELECT * FROM author;
 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
  6 | Test author_updated
(6 rows)
CAUTION
Be careful with using the UPDATE statement. Missing the WHERE clause can lead to all rows in the table being updated.

Step 7. Delete the table data

To delete one or more rows from the specified table, you should use the DELETE FROM statement, then write the table name, describe the query conditions in the WHERE clause, and finish the query with the semicolon ;.

The following query deletes from the author table the rows that contain the _updated postfix in the name column.

DELETE FROM author
WHERE name LIKE '%_updated';

The result is:

DELETE 1

You can check the command results by selecting the table data. One row is successfully deleted.

books_store=# SELECT * FROM author;
 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
(5 rows)
CAUTION
Be careful while using the DELETE statement. Missing the WHERE clause can lead to all rows in the table being deleted.

Step 8. Alter the table

To change the table definition, you should use the ALTER TABLE statement, then write the table name, describe all necessary changes using one of the possible actions, and finish the query with the semicolon ;. There are several forms of the ALTER TABLE command. Some of them are listed below.

Rename the table

The following query renames the book_copy table into book_deleted.

ALTER TABLE book_copy RENAME TO book_deleted;

The result is shown below.

ALTER TABLE

To check the command results, you can run the \dt psql command. The table is renamed.

books_store=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner
--------+--------------+-------+----------
 public | author       | table | postgres
 public | book         | table | postgres
 public | book_deleted | table | postgres
(3 rows)

Rename the column

The next query renames the title column of the book_deleted table into book_name.

ALTER TABLE book_deleted
RENAME COLUMN title TO book_name;

The result is:

ALTER TABLE

To check the command results, you can run the \d psql command. The column is renamed.

books_store=# \d book_deleted
                      Table "public.book_deleted"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | integer                |           |          |
 book_name   | character varying(255) |           |          |
 author_id   | integer                |           |          |
 public_year | smallint               |           |          |

Add the index

The last example shows how to add a new unique index to the book_deleted table. The index is based on two columns: author_id and book_name.

ALTER TABLE book_deleted
ADD CONSTRAINT full_name UNIQUE (author_id, book_name);

The output is:

ALTER TABLE

To check the command results, you can run the \d psql command. The new index is added.

books_store=# \d book_deleted
                      Table "public.book_deleted"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 id          | integer                |           |          |
 book_name   | character varying(255) |           |          |
 author_id   | integer                |           |          |
 public_year | smallint               |           |          |
Indexes:
    "full_name" UNIQUE CONSTRAINT, btree (author_id, book_name)

Step 9. Drop the table

To remove the table permanently, you should use the DROP TABLE statement, then write the table name, and finish the query with the semicolon ;.

DROP TABLE book_deleted;

The result should be similar to:

DROP TABLE

To check the command results, you can run the \dt psql command. The table is removed.

books_store=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | author | table | postgres
 public | book   | table | postgres
(2 rows)
Found a mistake? Seleсt text and press Ctrl+Enter to report it