How to Query Data in Heroku PostgreSQL Database

Published Oct 16, 2022  ∙  Updated Nov 4, 2022

We’ll be using pg:psql to query our Heroku PostgreSQL data.

When running pg:psql, we might encounter the following error message if we don’t have psql installed locally.

The local psql command could not be located. For help installing psql, see https://devcenter.heroku.com/articles/heroku-postgresql#local-setup

The first step, of course, is to install psql.

1. Install psql locally

On Ubuntu 20.04, we can install postgresql-client-12.

sudo apt-get update
sudo apt -y install postgresql-client-12

Otherwise, we can follow Heroku’s Local Setup guide.

2. Connect to Heroku PostgreSQL with psql

We can use pg:psql to establish a psql session with our Heroku database.

For these examples, the Heroku application that contains the PostgreSQL addon will be app-1.

heroku pg:psql --app app-1

This will connect to our primary database, which is located at DATABASE_URL by default.

With applications containing more than one database, we can specify the database with the first argument.

The database specifier will be the shorthand color.

heroku pg:psql green --app app-1

We can use pg:info to find the database color.

heroku pg:info --app app-1
=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
...

=== HEROKU_POSTGRESQL_GREEN_URL
Plan:                  Hobby-basic
Status:                Available
...

3. Get list of PostgreSQL tables

We can view the existing tables using the following command:

\d+
public | TableName   | table | kdjfhsncjdifur | 48 kB |
public | TableName2  | table | kdjfhsncjdifur | 64 kB |

4. View table schema (columns and types)

We can obtain a table’s schema like so:

\d "TableName"
id         | text    |         | not null |
message    | text    |         | not null | ''::text

5. Optionally turn on Expanded Auto Mode

With this option on, PostgreSQL will automatically fit query results to the screen width.

\x auto

If the record can fit into the screen width, normal formatting will be used.

| id  | p1Id | p2Id | created_at              | updated_at              |
| --- | ---- | ---- | ----------------------- | ----------------------- |
| 10  | 10   | 11   | 2022-01-28 19:06:45.492 | 2022-01-28 19:06:45.495 |
| 11  | 11   | 12   | 2022-06-19 19:57:57.036 | 2022-06-19 19:57:57.039 |
| 12  | 12   | 10   | 2022-06-19 19:57:57.036 | 2022-06-19 19:57:57.039 |

If the record can not fit into the screen width, expanded mode will be used.

-[ RECORD 1 ]------------------------------------
id         | 10
p1Id       | 10
p2Id       | 11
created_at | 2022-01-28 19:06:45.492
updated_at | 2022-01-28 19:06:45.495
-[ RECORD 2 ]------------------------------------
id         | 11
p1Id       | 11
p2Id       | 12
created_at | 2022-06-19 19:57:57.036
updated_at | 2022-06-19 19:57:57.039
-[ RECORD 3 ]------------------------------------
id         | 12
p1Id       | 12
p2Id       | 10
created_at | 2022-06-19 19:57:57.036
updated_at | 2022-06-19 19:57:57.039

6. Error: relation “table” does not exist

Note that SQL commands have a few syntactic requirements.

For instance, tables must be enclosed with double quotes. Otherwise, we might encounter this error:

app-1::DATABASE=> select * from Users;
ERROR:  relation "users" does not exist
LINE 1: select * from Users;
                      ^
app-1::DATABASE=> select * from "Users";

7. Error: column “col” does not exist

Also, note that column values should be wrapped in single quotes. Otherwise, we’ll encounter this error:

app-1::DATABASE=> delete from "Users" where location = "White House";
ERROR:  column "White House" does not exist
LINE 1: delete from "Users" where location = "White House";
                                               ^
app-1::DATABASE=> delete from "Users" where location = 'White House';

8. Error: nothing happens in terminal

We might also enter a command, and nothing will happen.

app-1::DATABASE=> select * from "Users"
app-1::DATABASE=> 
app-1::DATABASE=> select * from "Users";

Don’t forget that queries must end with a semicolon.