How to select a schema in postgres when using psql?
PostgresqlPsqlPostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
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
Solution 2 - Postgresql
\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'.
Solution 3 - Postgresql
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
Solution 4 - Postgresql
if you in psql just type
set schema 'temp';
and after that \d shows all relations in "temp
Solution 5 - Postgresql
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...
Solution 6 - Postgresql
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"
Solution 7 - Postgresql
key word :
SET search_path TO
example :
SET search_path TO your_schema_name;
Solution 8 - Postgresql
quick solution could be:
SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name";
Solution 9 - Postgresql
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
Solution 10 - Postgresql
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