How to select a schema in postgres when using psql?

Question

I have a postgres database with multiple schemas. When I connect to the database from a shell with psql and I run \dt it uses the default connection schema which is public. Is there a flag I can specify or how can I change the schema?

Consider: http://stackoverflow.com/questions/9067335/how-does-the-search-path-influence-identifier-resolution-and-the-current-schema/9067777#9067777 — Erwin Brandstetter, Dec 05 '15 at 06:30

score 366 · Answer 1 · edited Jan 22 '18 at 15:56

In PostgreSQL the system determines which table is meant by following a search path, which is a list of schemas to look in.

The first matching table in the search path is taken to be the one wanted, otherwise, if there is no match a error is raised, even if matching table names exist in other schemas in the database.

To show the current search path you can use the following command:

SHOW search_path;

And to put the new schema in the path, you could use:

SET search_path TO myschema;

Or if you want multiple schemas:

SET search_path TO myschema, public;

Reference: https://www.postgresql.org/docs/current/static/ddl-schemas.html

score 182 · Answer 2 · answered Nov 03 '18 at 06:09

182

\l - Display database
\c - Connect to database
\dn - List schemas
\dt - List tables inside public schemas
\dt schema1. - List tables inside particular schemas. For eg: 'schema1'.

answered Nov 03 '18 at 06:09

Mohamed Sameer

2,998
3
22
51

71

Don't be like me forgetting about the period after the schema name :) (thank you, Mohamed!) – anapaulagomes Oct 10 '19 at 09:21
3

OP is complaining that he cannot list tables from a specific schema using \dt, this answer is actually the closest to answer the question. Post title needs improvement to include that the question is about \d though. – TheWildHealer Jun 15 '21 at 14:19
26

PostgreSQL 14.1: you have to add a star after period. Like `\dt schema1.*` – oMid dehghani Feb 17 '22 at 13:44
2

The additional * is what fixed it for me To be 100% clear: - `\dt schema1.` did not work - `\dt schema1.*` worked – Paul Carroll Feb 15 '23 at 07:45

score 113 · Answer 3 · edited Aug 01 '16 at 00:15

113

Do you want to change database?

\l - to display databases
\c - connect to new database

Update.

I've read again your question. To display schemas

\dn - list of schemas

To change schema, you can try

SET search_path TO

edited Aug 01 '16 at 00:15

Eric

2,636
21
25

answered Dec 04 '15 at 22:15

miholeus

1,537
1
9
8

1

How to do this not in psql. how to "connect" – mathtick Jan 09 '19 at 14:15

score 72 · Answer 4 · edited Mar 07 '23 at 08:22

72

If you use psql, just type

SET schema 'temp';

and after that \d shows all relations in temp

edited Mar 07 '23 at 08:22

snoob dogg

2,491
3
31
54

answered Jan 25 '21 at 14:24

drprofee

831
6
2

9

Note that the above command is just an alias to `SET search_path TO temp;` – varun Feb 20 '21 at 14:46
3

I find that setting connection parameters through the environment is the most effective way of quickly changing databases, and guess what, you can set the schema through the PGOPTIONS environment variable: `PGOPTIONS="-c search_path=schemaname" psql`! Woohoo! – NeilG May 10 '22 at 11:21

score 34 · Answer 5 · answered Dec 04 '15 at 22:29

Use schema name with period in psql command to obtain information about this schema.

Setup:

test=# create schema test_schema;
CREATE SCHEMA
test=# create table test_schema.test_table (id int);
CREATE TABLE
test=# create table test_schema.test_table_2 (id int);
CREATE TABLE

Show list of relations in test_schema:

test=# \dt test_schema.
               List of relations
   Schema    |     Name     | Type  |  Owner   
-------------+--------------+-------+----------
 test_schema | test_table   | table | postgres
 test_schema | test_table_2 | table | postgres
(2 rows)

Show test_schema.test_table definition:

test=# \d test_schema.test_table
Table "test_schema.test_table"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer |

Show all tables in test_schema:

test=# \d test_schema.
Table "test_schema.test_table"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

Table "test_schema.test_table_2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer |

etc...

I was missing the period after \dt test_schema. which results in "no relation found message" Thanks for the examples, made it much easier :) — mehany, Dec 04 '15 at 22:36
Seems PG14 it needs to be `\dt schema.*` or `\dv schema.*`, thus needs the `*` after the fullstop :shrug: — Hvisage, Oct 30 '21 at 11:13

techbrownbags · Answer 6 · 2019-01-10T00:33:13.637

21

This is old, but I put exports in my alias for connecting to the db:

alias schema_one.con="PGOPTIONS='--search_path=schema_one' psql -h host -U user -d database etc"

And for another schema:

alias schema_two.con="PGOPTIONS='--search_path=schema_two' psql -h host -U user -d database etc"

edited Jan 10 '19 at 00:33

answered Jul 05 '17 at 15:39

techbrownbags

618
5
10

4

Nice idea. I would omit `export` and the semicolon in your aliases. This way `PGOPTIONS` doesn't stay around after you leave psql. – Doron Gold Jan 20 '18 at 17:53
1

this is a great idea, much more practical than adding a `SET search_path` to every single query. thank you! – hraban Apr 16 '19 at 11:29

score 10 · Answer 7 · answered Oct 30 '17 at 16:03

10

key word :

SET search_path TO

example :

SET search_path TO your_schema_name;

answered Oct 30 '17 at 16:03

appsdownload

751
7
20

score 10 · Answer 8 · answered Sep 26 '19 at 15:03

10

quick solution could be:

SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name";

answered Sep 26 '19 at 15:03

Stan

1,800
1
13
15

score 3 · Answer 9 · edited Oct 30 '21 at 17:43

PostgreSQL 14 Debian

    postgres@ovhswift:~$ psql
psql (14.0 (Debian 14.0-1.pgdg100+1))
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create schema tests;
CREATE SCHEMA
test=# \dt
Did not find any relations.
test=# create table pubtable (id integer);
CREATE TABLE
test=# create table tests.schematable (id integer);
CREATE TABLE
test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | pubtable | table | postgres
(1 row)

test=# \dt tests.
Did not find any relation named "tests.".
test=# \dt tests
Did not find any relation named "tests".
test=# \dt 'tests.'
Did not find any relation named "tests.".
test=# \dt 'tests.*'
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 tests  | schematable | table | postgres
(1 row)

test=# \dt 'tests*'
Did not find any relation named "tests*".
test=# \dt 'tests.*'
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 tests  | schematable | table | postgres
(1 row)

Ditto for \dv etc. to see the views in the schema

score 2 · Answer 10 · answered Feb 05 '20 at 17:05

2

if playing with psql inside docker exec it like this:

docker exec -e "PGOPTIONS=--search_path=<your_schema>" -it docker_pg psql -U user db_name

answered Feb 05 '20 at 17:05

andilabs

22,159
14
114
151

How to select a schema in postgres when using psql?

10 Answers10

Linked