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?

Kindrakindred

Consider: #9067835 – Hurdygurdy

Answer 1

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

Riti

Answer 2

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

Ballata

Don't be like me forgetting about the period after the schema name :) (thank you, Mohamed!) – Tartaglia

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

PostgreSQL 14.1: you have to add a star after period. Like \dt schema1.* – Diller

The additional * is what fixed it for me To be 100% clear: - \dt schema1. did not work - \dt schema1.* worked – Hardi

Answer 3

To display schemas:

\dn - list of schemas

To change schema, you can try:

SET search_path TO

Wobbly

How to do this not in psql. how to "connect" – Gertie

Answer 4

If you use psql, just type

SET schema 'temp';

and after that \d shows all relations in temp

Jimjams

Note that the above command is just an alias to SET search_path TO temp; – Bourque

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! – Meredithmeredithe

@NeilG: setting PGOPTIONS leads to psql: error: FATAL: unsupported startup parameter: options for me (psql 12.16) – Endure

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... #69133940 – Meredithmeredithe

Answer 5

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

Egyptology

I was missing the period after \dt test_schema. which results in "no relation found message" Thanks for the examples, made it much easier :) – Kindrakindred

Seems PG14 it needs to be \dt schema.* or \dv schema.*, thus needs the * after the fullstop :shrug: – Sharonsharona

Answer 6

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"

Highbrow

Nice idea. I would omit export and the semicolon in your aliases. This way PGOPTIONS doesn't stay around after you leave psql. – Kansas

this is a great idea, much more practical than adding a SET search_path to every single query. thank you! – Butterfly

Answer 7

key word :

SET search_path TO

example :

SET search_path TO your_schema_name;

Nonconductor

Answer 8

quick solution could be:

SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name";

Gertrudgertruda

Answer 9

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

Sharonsharona

Answer 10

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

Noto

Answer 11

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.

Waxman