How to Run PostgreSQL Queries in PSQL

1. The Basic Methodology of Using PSQL
2. Executing Queries Separately for Each Database
3. Using the SINGLE LINE Syntax Instead of Semicolon
4. Use File for Reading SQL Queries in PSQL

PostgreSQL comes with two options, the number one option is the PGADMIN4 or any other version number that you will be using, and the second option is the PSQL. PSQL is the command-line interface that can interact with our PostgreSQL session and run queries from the user interface.

Remember that PSQL is a terminal-based front-end and can also take arguments and queries from a file and its command line. So now, let’s go ahead and understand how our queries work in PSQL, the PostgreSQL command line.

The Basic Methodology of Using PSQL

Once you have booted PSQL with your Postgres username and password and logged in successfully, it’s time to understand how to run the queries you would run in the PostgreSQL user interface in a command line.

Ensure that you follow the syntax correctly when using queries with keywords such as SELECT, INSERT, UPDATE etc., to avoid unwanted errors. A lot of people tend to miss a semicolon ;.

Missing a semicolon leads to PSQL thinking that the query has not yet terminated, and pressing ENTER will not ensure it. So let’s say that you are in your PSQL instance as follows:

Now, INSERT a table into the database. We want to INSERT a table BAG with two columns, an ID and a MODEL.

Query:

CREATE table BAG (id INT, model TEXT);

Output:

If you forgot to put a semicolon and press ENTER, you will not get an output on the screen and will remain blank.

Output:

In the same manner, you can run an INSERT query to now input some values as follows. We’ll INSERT the data sets, ‘(1, Adidas)’, ‘(2, Jale)’ and ‘(3, Niky)’.

Query:

insert into BAG values (1, 'Adidas'), (2, 'Jale'), (3, 'Niky');

Output:

Run the SELECT query and return a TABLE.

Output:

Running queries with PSQL is easy. However, there is a condition.

If you tend to run a query before a query without the semicolon notation, the latter query won’t work at all. So, if I called the SELECT BAG without a semicolon and then ran the INSERT operation, PSQL would show an error.

Output:

Not putting a semicolon after SELECT never terminated the query. The next INSERT query happened to be appended to this SELECT query, and when to run, it would cause an error.

So it’s better to always take care of semicolons at the end of each statement.

Executing Queries Separately for Each Database

We can have more than one database. Running a query without listing or specifying the database can lead to more confusion.

Let’s go ahead and view all of our databases first. To view databases in PSQL, you can run a query below.

Query:

\l or \l+

\l+ is a more descriptive version of \l, with a + appended. I have my databases listed in the following.

Output:

In the case of \l+, the result comes out as:

Output:

We currently have three databases, POSTGRES, TEMPLATE0 and TEMPLATE1. Also, we can use the SELECT statement to view them.

Query:

SELECT datname from PG_DATABASE:

Output:

  datname
-----------
 postgres
 template1
 template0
(3 rows)

PG_DATABASE is a catalog that stores all the database information, and DATNAME is a column within this table that stores the databases. To connect to the database Postgres, let’s use the \c command.

Command:

\c postgres

You can then run the query to get the tables as \dt.

Query:

\dt

Output:

              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | animal          | table | postgres
 public | bag             | table | postgres
 public | bus             | table | postgres
 public | car             | table | postgres
 public | cat             | table | postgres
 public | dog             | table | postgres
 public | horse           | table | postgres
 public | identity_number | table | postgres
 public | mytable         | table | postgres
 public | person          | table | postgres
 public | person_details  | table | postgres
 public | rider           | table | postgres
 public | strings         | table | postgres
 public | student         | table | postgres
 public | tab             | table | postgres
 public | vehicle         | table | postgres
(16 rows)

You can now connect to the database TEMPLATE0 or TEMPLATE1 to view their tables, but they will show an error because they don’t accept any incoming user connections due to default settings.

Output:

To run a query for a specific database, use the \c command to connect and then the query.

Query:

psql -U postgres -d postgres -c "SELECT * from BAG;"

After the -d keyword, connect to the Postgres database and run the query. I have written the database name to connect to and then issued the command.

Make sure not to write the query after -c in single commas, leading to an error. Use double commas to make it a valid string, and in the end, write a semicolon to be on the safe side.

Not using the double commas will lead to an error like in the following example.

Query:

psql -U postgres -d postgres -c 'SELECT * FROM BAG;'

Output:

psql: warning: extra command-line argument "*" ignored
psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "BAG'" ignored

Using the SINGLE LINE Syntax Instead of Semicolon

You can use the -S or --SINGLE-LINE command to automatically terminate single line queries without needing a semicolon. When initializing the PostgreSQL session, run the following commands.

Command:

psql -U postgres -S

or

psql -U postgres --single-line

When you run your PSQL session, you will notice something as follows:

postgres^# __

This means that postgres# changed to postgres^#, indicating the use of a SINGLE LINE statement.

Query:

select * from BAG

If you run the query above will work perfectly fine, and there is no semicolon(;) appended at the end. If you protect your database with a password required at every session, you can use the PGPASSWORD keyword when initializing a session in PSQL.

Use File for Reading SQL Queries in PSQL

Another useful implementation of reading bigger SQL queries is to sometimes use a .sql extension file with written SQL queries and execute it from the PSQL command line. For example, let’s make a file with a .sql extension with the following query.

Query:

SELECT * from BAG;

Let’s save it as QUERY1.SQL on our desktop.

To run this while initializing our PSQL session. Use the following command.

Command:

psql -U postgres -f "C:\Users\Bilal Shahid\Desktop\query1.sql"

Output:

 id |  mode
----+--------
  1 | Adidas
  2 | Jale
  3 | Nikey
  1 | Adidas
  2 | Jale
  3 | Nikey
  1 | Adidas
  2 | Jale
  3 | Nikey
  4 | John
  5 | mike
(11 rows)

There is another equivalent for the -f command. The \icommand, if you are already inside the PSQL session and want to run the query from the file.

Query:

postgres=# \i 'C:/Users/Bilal Shahid/Desktop/query1.sql'

Use backslashes(/) and single commas(') as not doing so will return errors.

We hope you learned how to use PSQL efficiently and understood the various ways that it might produce errors and how to work around them. Always read our tutorials thoroughly for a careful and in-depth understanding.