377

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?

Improve this question
1

11 Answers 11

Reset to default
437

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

234 
\l - Display database
\c - Connect to database
\dn - List schemas
\dt - List tables inside public schemas
\dt schema1.* - List tables inside a particular schema.
                For example: 'schema1'.
Improve this answer

4 Comments

Don't be like me forgetting about the period after the schema name :) (thank you, Mohamed!)
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.
PostgreSQL 14.1: you have to add a star after period. Like \dt schema1.*
The additional * is what fixed it for me To be 100% clear: - \dt schema1. did not work - \dt schema1.* worked
121

To display schemas:

\dn - list of schemas

To change schema, you can try:

SET search_path TO
Improve this answer

1 Comment

How to do this not in psql. how to "connect"
96

If you use psql, just type

SET schema 'temp';

and after that \d shows all relations in temp

Improve this answer

4 Comments

Note that the above command is just an alias to SET search_path TO temp;
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: setting PGOPTIONS leads to psql: error: FATAL: unsupported startup parameter: options for me (psql 12.16)
I'm not currently in that zone, @jfs, and obviously there's not enough room in a comment to describe your problem, but there seems to be some support out there. Have you searched up the error message? Could be drivers, environment, build... stackoverflow.com/questions/69133440/…
35

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...

Improve this answer

2 Comments

I was missing the period after \dt test_schema. which results in "no relation found message" Thanks for the examples, made it much easier :)
Seems PG14 it needs to be \dt schema.* or \dv schema.*, thus needs the * after the fullstop :shrug:
23

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"
Improve this answer

2 Comments

Nice idea. I would omit export and the semicolon in your aliases. This way PGOPTIONS doesn't stay around after you leave psql.
this is a great idea, much more practical than adding a SET search_path to every single query. thank you!
13

key word : 

SET search_path TO

example : 

SET search_path TO your_schema_name;
Improve this answer

Comments

11

quick solution could be:

SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name";
Improve this answer

Comments

3

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
Improve this answer

Comments

3

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

Improve this answer

Comments

2

To specify the schema to be used immediately upon connecting with psql, the options keyword with --search_path can be used.

psql "host=localhost dbname=mydb options=--search_path=myschema"

or

psql postgres://localhost/mydb?options=--search_path%3Dmyschema

Documentation

Connecting to a Database

An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection. For example:

$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require

34.1.2. Parameter Key Words

options

Specifies command-line options to send to the server at connection start.

20.11.1. Statement Behavior

search_path (string)

This variable specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema specified. When there are objects of identical names in different schemas, the one found first in the search path is used. An object that is not in any of the schemas in the search path can only be referenced by specifying its containing schema with a qualified (dotted) name.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.