Query a PostgresSQL® database 3 ways

Run a query against a PostgresSQL® database to inspect the data inside its tables using the command line, a graphical interface, and a language-specific package

Subscribe to RSS

The first step of interacting with a new PostgreSQL® database is running a query. Querying a database is any action in which you request information from a database. That information can be rows of a table that match certain criteria, an entire table itself, or the result of a mathematical operation on a column’s data.

In this article we’ll go through 3 different ways to query a PostgreSQL database. We’ll also cover how to view active queries in the Aiven console, and how to optimize SQL queries using the Aiven SQL optimizer.

Sign up for Aiven and install psql

In this article, we’ll be querying an Aiven for PostgreSQL database. Sign up for a free database today to follow along.

When you’ve signed up, select Create Service, then create a PostgreSQL database. You can either use the free tier or a free trial PostgreSQL database.

Install psql

Next, use the command line to install psql. Psql is the official command line tool for Postgres. We’ll be using this for multiple steps in this tutorial

On Mac using Homebrew:

In a terminal window, type:

brew install libpq

On Linux using sudo apt-get

In a terminal window, type:

sudo apt install postgresql-client

On Windows using the PostgreSQL installer

Go to postgresql.org and use the installer.

Connect to the database using psql

We need to add a table to our database and insert some data so we can query it in the next sections.

To connect to the Aiven for PostgreSQL database:

  1. In the Aiven Console’s Overview page for your database, click the Quick Connect button
  2. Copy the code sample into a terminal window, and hit enter. It should look similar to:
    psql '<YOUR_SERVICE_URI>'

This connects you to the database. You should see output similar to the following:

> psql (16.2, server 16.4)
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
> Type "help" for help.

Insert data into a PostgreSQL database using psql

Once connected, type the following into your terminal window and press enter to create a basic table for querying.

CREATE TABLE users (
    ID INT PRIMARY KEY,
    name VARCHAR(255)
);

You should see the following output if successful:

> CREATE TABLE

Next, insert some data into the table:

INSERT INTO users (ID, name) VALUES
(1, 'Celeste'),
(2, 'Jay'),
(3, 'Tibs'),
(4, 'Olena');

You should see the following output if successful:

> INSERT 0 4

The difference between a query and an insert

In SQL, an INSERT statement, like the one above, writes data to the database. A SELECT statement reads data from the database. “Query” is used as a general term in the database world and refers to any operation which starts from or includes a read from the database. Some queries can also return the result of transforming the data from the database, or the result of a calculation performed on the data. These queries typically include the WHERE operator, and can include mathematical operators such as +, -, * / and %.

Query a PostgreSQL database table using psql

Let’s run a basic query against our table to ensure it was created correctly. Assuming you’re still connected to the database as above, use the following command:

SELECT * FROM users;

Which should return the following:

 id |  name
----+---------
  1 | Celeste
  2 | Jay
  3 | Tibs
  4 | Olena
(4 rows)

Query a PostgreSQL database table using pgAdmin

The command line is a preferable tool for many, but sometimes it’s useful to inspect a database using a visual aid. PgAdmin is the tool of choice for many when using a UI.

Download pgAdmin, install it and run to begin.

To query data against our Aiven for PostgreSQL database, next we need to add the database to pgAdmin:

  1. Click the Add New Server button. Name the server Aiven.
  2. In the Connections tab add the following from the service Overview in the Aiven console:
    -** Host name/address:** The Host listed in the overview
    • Port: The Port listed in the overview
      -** Maintenance database:** The default database in the overview, usually defaultdb
      -** Username:** The Username in the overview, usually avnadmin
      -** Password:** The Password in the overview
  3. Click** Save and Connect**, and in the left hand pane expand the Aiven database. It should look like the following:

From here, expand Schemas and you should see the table we created earlier, users. If you right click the table and select** View/Edit Data > All Rows**, you should see the following:

The top right pane labeled Query allows us to query the data in the table as we desire. Selecting View/Edit Data as we did auto-populates the SELECT * FROM users query we used previously. You can replace the text in the top right pane with any query you want to run.

Query a PostgreSQL database using a language-specific package

The Aiven for PostgreSQL documentation includes examples for connecting to PostgreSQL databases in all major programming languages with the recommended packages. This example is copied from the Python example and uses the psycopg2 package:

import psycopg2


def main():
    conn = psycopg2.connect('POSTGRESQL_URI')

    query_sql = 'SELECT * FROM users'

    cur = conn.cursor()
    cur.execute(query_sql)

    users = cur.fetchone()[0]
    print(users)


if __name__ == "__main__":
    main()

Other types of queries

Postgres databases support a fully featured SQL query language. SQL is a query language commonly used for data management systems, though it varies slightly from implementation to implementation. As such, we recommend following the PostgreSQL project’s documentation for the full list of available commands.

If you aren’t keen on learning too much SQL, Aiven’s free Text to SQL tool takes natural language and turns it into valid SQL commands.

But let’s return to our small example table, and run some more complex commands. One of the most useful commands we can run selects rows based on a condition using the WHERE operator.

For example, if we wanted to calculate the number of rows with an even-numbered ID (divisible by 2), we could use the following:

SELECT COUNT(*) FROM users WHERE ID % 2 = 0;

This should return the count of two, as follows:

>  count
> -------
>     2

Similarly, you can also search for specific values within a column. Running:

SELECT * FROM users WHERE name = 'Celeste';

Should return the following:

>  id |  name
> ----+---------
>   1 | Celeste

Monitor active queries (connections) to the database using the Aiven console

The Current Queries tab in the Aiven console shows any active queries (or connections) to your PostgreSQL instance.

While we recommend connecting using a secure SSL connection only, this view also allows you to terminate any connections for reasons you see fit.

Optimize queries using the Aiven SQL Optimizer

The examples given in this article are simplistic because the data we’re operating on is simplistic as well. In practice, SQL queries can get extremely complex, and an inefficient SQL query can lead to a significant increase in compute and network traffic – both of which can increase cost for you, the end user.

Aiven offers a suite of free SQL tools for you to use to optimize your queries. You can optimize complex queries using AI, convert plain language to SQL, and more. The Aiven Console also includes the AI Insights optimizer, which can help you fine tune performance:

Next steps