How to Query Data in Heroku PostgreSQL Database
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.