How to select a schema in postgres when using psql?

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?

10 Answers

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:

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

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 

if you in psql just type

set schema 'temp'; 

and after that \d shows all relations in "temp

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

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" 

quick solution could be:

SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name"; 

key word :

SET search_path TO 

example :

SET search_path TO your_schema_name; 

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 

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