Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select a schema in postgres when using psql?

People also ask

How do I change the schema in PostgreSQL?

ALTER SCHEMA changes the definition of a schema. You must own the schema to use ALTER SCHEMA . To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, and you must have the CREATE privilege for the database.

What is schema in psql?

Schema is a collection of logical structures of data. In PostgreSQL, schema is a named collection of tables, views, functions, constraints, indexes, sequences etc. PostgreSQL supports having multiple schemas in a single database there by letting you namespace different features into different schemas.


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


\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

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


if you in psql just type

set schema 'temp';

and after that \d shows all relations in "temp


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"

key word :

SET search_path TO

example :

SET search_path TO your_schema_name;