PostgreSQL :- psql :- \dt => Did not find any relations

Background

Playing around with PostgreSQL.

psql

I have PostgreSQL installed on my laptop and so thankful I have psql, a terminal mode interface loaded, as well.

Commands

\dt

Googled for how to list tables. And, was told to use the \dt command.

Command

Command – Sample
\dt

Output

Output – Image

dt.01.20190727.1114AM

Output – Textual

Did not find any relations.

Explanation

Upon issuing \dt, got the message “Did not find any relations“.

Troubleshooting

Outline

  1. Database
    • Get Current Database
  2. Search Path
    • Get Search Path

Tasks

Database

Get Current Database
SQL

select current_database();

Output

current_database.01.20190727.1153AM

Explanation

The current database for our session is postgres.

Set Current Database
Outline

If the database is not the one we want, we change to another existing database.

  1. List Databases
    • Use \l
    • Or \list database
  2. Change Database
    • Change Database using same user
    • Change Database as a new user
SQL (using same user )

\connect {database}

Output

current_database.set.usingSamePrincipal.01.20190727.1205PM

Explanation

We are prompted to re-enter for the password for our current user.

SQL (using same user )

\connect {database} {user}

Output

current_database.set.usingSamePrincipal.01.20190727.1205PM

Explanation

We are prompted to re-enter for the password for the new user.

current_database.set.usingDifferentPrincipal.01.20190727.1208PM

Search Path

Get Search Path
SQL

show Search_Path;

Output

searchPath.show.01.20190727.1137AM

Explanation

We have the default.

And, that is for the Search Path for the current user to be set to Public schema.

Set Search Path for User
SQL – Syntax

alter role [principal] set search_path = "$user", public, {schema} ;

 

SQL – Sample

alter role [principal] set search_path = "$user", public, hrdb ;

 

Set Search Path
SQL

show Search_Path;

Output

searchPath.show.01.20190727.1137AM

Explanation

We have the default.

And, that is for the Search Path for the current user to be set to Public schema.

Set Search Path for Database
SQL – Syntax

alter database [sale] set search_path = "$user", public, {schema} ;

 

SQL – Sample

alter database [principal] set search_path = "$user", public, macy ;

 

References

  1. Liquid Web
    • Echo Diaz
      • Listing and Switching Databases in PostgreSQL
        Link
  2. StackExchange
    • Database Administrators
      • What is the search_path for a given database and user?
        Link

Summary

Nothing earth shaking.

\d was failing for us, because we were simply in the wrong database.

BTW, in psql, the prompt bears the name of the contextual database.

Others on the Internet experienced similar error messages, but theirs was a bit harder.  They had to expand their search path.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s