我有一个具有多个模式的postgres数据库。当我用psql从shell连接到数据库并运行\dt时,它使用默认的公共连接模式。是否有可以指定的标志或如何更改模式?


当前回答

在psql命令中使用带句点的模式名获取该模式的信息。

设置:

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

显示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)

显示test_schema。test_table定义:

test=# \d test_schema.test_table
Table "test_schema.test_table"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

显示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 | 

等等……

其他回答

这是旧的,但我把导出放在我的别名连接到db:

alias schema_one.con="PGOPTIONS='--search_path=schema_one' psql -h host -U user -d database etc"

对于另一个schema:

alias schema_two.con="PGOPTIONS='--search_path=schema_two' psql -h host -U user -d database etc"
\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'.

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)

对于\dv等,同样可以查看模式中的视图

快速解决方案可以是:

SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name";

如果在docker exec中使用PSQL,它是这样的:

docker exec -e "PGOPTIONS=--search_path=<your_schema>" -it docker_pg psql -U user db_name