PostgreSQL Psql Examples (Part 2)

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This is the second part of a tutorial series providing psql examples. Pat two will pick up where part one left off, explainging SQL statement and query examples for the psql command line interface for Postgres. Part one covered how to install Postgres and psql on various operating systems and provided psql examples. Part two will cover how to insert and modify records and provided clause examples.

Prerequisites

  • PostgreSQL must be properly installed and working with access to its psql command line interface in order to execute the psql examples explained in this tutorial.

  • A basic working knowledge of database management systems and SQL commands for the psql PostgreSQL interface.

The following command will provide login privilege to the postgres super user after entering the password and executing the following command:

1
sudo su - postgres

Now enter the following psql command in a terminal or command-prompt window to access the interface:

1
psql

This should now allow the command-line interface to carry out PostgreSQL queries and modify data.

Use the following format in the following psql example to connect a user to a database using the -d and -U flags, respectively:

1
psql -U user_name -D db_name

psql examples

The first part of the series covered how to install and access the psql CLI for Postgres and create databases and tables. This section will cover how to INSERT records and modify the record data on the Postgres tables.

Postgres list tables example

First, check all of the available tables on a selected database.

One of the most common ways of displaying all of the table names in psql is to use the \dt command. A second common way of listing all of the table names is to get the entire table of table names from the built-in pg_catalog.pg_tables in the Postgres database by executing the following command:

1
SELECT * FROM pg_tables;

Note, however, that using the above SELECT statement will return all of the system tables, and not just the user-created ones.

Postgres ‘NOT LIKE’ clause

The following != operator can be used to exclude certain tables by comparing a string to the built-in schemaname SQL value for pg_tables:

1
2
SELECT * FROM pg_tables
WHERE schemaname != 'pg_catalog';

However, a better way is to use the following NOT LIKE clause with the % wildcard to further restrict the records of table names returned by the query:

1
2
SELECT * FROM pg_tables
WHERE tablename NOT LIKE 'pg_%';

Additionally, multiple NOT LIKE clauses can be used in the query by using the AND operator as follows:

1
2
3
SELECT * FROM pg_tables
WHERE tablename NOT LIKE 'pg_%'
AND tablename NOT LIKE 'sql_%';

This is shown in the following screenshot:

Screenshot of psql examples Postgres NOT LIKE clause and SELECT FROM

NOTE: The % wildcard at the end of the string in the above command instructs psql to exclude any table that starts with pg_ or sql_.

psql ‘DROP TABLE’ example

Following is a SQL statement that will drop an old Postgres table that is no longer needed:

1
DROP TABLE other_table;

The results from the above command should be: DROP TABLE.

Insert into example

The following example shows how to insert data into the table:

1
INSERT INTO TABLE_NAME(COL1, COL2) VALUES (VAL1, VAL2);

The results should resemble the following:

1
2
3
4
INSERT INTO sample(name, address)
VALUES ('Jolly Vega', 'California'),
('Mary Rose', 'Hongkong'),
('Tanders Smith', 'Australia');

The above SQL insert statement should return: INSERT 0 3.

Be certain to always add an INSERT INTO statement followed by the table_name to insert data into the PostgreSQL table. Create all of the columns separated by comma (,) and indicate the VALUES that are entered as the data records.

PostgreSQL ‘SELECT’ examples

Following is an example on how to select data in PostgreSQL. The asterisks (*) in the following SQL example tells psql to query all of the documents in the Postgres table called sample:

1
SELECT * FROM sample;

The results should resemble the following:

1
2
3
4
5
6
db=# SELECT * FROM sample;
 id |     name      |  address
----+---------------+------------
  1 | Jolly Vega    | California
  2 | Mary Rose     | Hongkong
  3 | Tanders Smith | Australia

Postgres ‘SELECT WHERE’ clause

Use the WHERE SQL clause in conjunction with SELECT FROM command to pass a condition that will restrict the Postgres records being returned.

In the following example, only records from a table that have an ID number that is greater than or equal to 2will be returned:

1
SELECT * FROM sample WHERE id -= 2;

The above SQL statement should only return the following two records:

1
2
3
4
5
 id |     name      |  address
----+---------------+-----------
  2 | Mary Rose     | Hongkong
  3 | Tanders Smith | Australia
(2 ROWS)

Use the Postgres ‘ORDER BY’ clause to sort

There are many ways to display the data returned by the table. For example, use the SELECT statement to return and display the data of the PostgreSQL table. Additionally, the sort command can be used to put the rows in a specified order.

The following example uses the ORDER BY command:

1
SELECT * FROM sample ORDER BY address;

The above command should produce the following results:

1
2
3
4
5
6
7
db=# SELECT * FROM sample ORDER BY address;
 id |     name      |  address
----+---------------+------------
  3 | Tanders Smith | Australia
  1 | Jolly Vega    | California
  2 | Mary Rose     | Hongkong
(3 ROWS)

Here are the results displayed in a screenshot:

Screenshot of PostgreSQL psql examples of SELECT WHERE and ORDER BY SQL statements Another example using the SELECT statement is to execute the WHERE clause to filter the table rows as follows:

1
SELECT * FROM sample WHERE address = 'California';

The above command should return results that resembles the following:

1
2
3
4
5
db=# SELECT * FROM sample WHERE address = 'California';
 id |    name    |  address
----+------------+------------
  1 | Jolly Vega | California
(1 ROW)

Update example

The following example shows how to update a record in PostgreSQL:

1
UPDATE sample SET name = 'Jelie Vargs', address = 'Texas' WHERE id = 1;

The above UPDATE statement should produce an UPDATE 1 response.

PostgreSQL delete example

Following is an example on how to delete a record in PostgreSQL:

1
DELETE FROM sample WHERE id = 3;

Since the table has a UNIQUE key constraint, the above command should delete only one record and return the following response:

1
DELETE 1

Use psql to delete all records from a table

The simplest way to delete all of the records in a PostgreSQL table is to use the TRUNCATE TABLE keywords followed by the table name, as shown here:

1
TRUNCATE TABLE some_table;

The system should respond with TRUNCATE.

Another method to eliminate all of the records is to use the following DELETE FROM statement to delete all of the records with an ID that is greater than or equal to 0:

1
DELETE FROM some_table WHERE id >= 0;

Here the system should respond with DELETE followed by a number representing the total number of records contained in the table.

Conclusion

This was the second part of a tutorial series providing psql examples on how to execute the psql examples in Postgres. The article specifically covered how to INSERT records and modify the record data on the Postgres tables. The tutorial then provided examples for list tables, the ‘NOT LIKE’ clause and ‘DROP TABLE’ commands, provided insert and ‘SELECT’ examples, the ‘SELECT WHERE’ clause and the ‘ORDER BY’ clause. Part two then provided update and delete examples. Remember to always add an INSERT INTO statement followed by the table_name when inserting data into a PostgreSQL table.

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.