postgresql 使用psql时如何在postgres中选择模式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34098326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select a schema in postgres when using psql?
提问by mehany
I have a postgres database with multiple schemas. When I connect to the database from a shell with psqland I run \dtit uses the default connection schema which is public. Is there a flag I can specify or how can I change the schema?
我有一个具有多个模式的 postgres 数据库。当我从 shell 连接到数据库psql并运行\dt它时，它使用默认的连接模式public。是否有我可以指定的标志或如何更改架构？
回答by Ciro Pedrini
In PostgreSQL the system determines which table is meant by following a search path, which is a list of schemas to look in.
在 PostgreSQL 中，系统通过搜索路径来确定哪个表是指要查找的模式列表。
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
参考：https: //www.postgresql.org/docs/current/static/ddl-schemas.html
回答by miholeus
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
回答by Mohamed Sameer
\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'.
回答by klin
Use schema name with period in psql command to obtain information about this schema.
在 psql 命令中使用带有句点的模式名称来获取有关此模式的信息。
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_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_tabledefinition:
显示test_schema.test_table定义：
test=# \d test_schema.test_table
Table "test_schema.test_table"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Show all tables in test_schema:
显示 中的所有表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...
等等...
回答by techbrownbags
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"
回答by appsdownload
key word :
关键词：
SET search_path TO
example :
例子 ：
SET search_path TO your_schema_name;
回答by Stanislav
quick solution could be:
快速解决方案可能是：
SELECT your_db_column_name from "your_db_schema_name"."your_db_tabel_name";
回答by andilabs
if playing with psql inside docker exec it like this:
如果在 docker exec 里面玩 psql 像这样：
docker exec -e "PGOPTIONS=--search_path=<your_schema>" -it docker_pg psql -U user db_name
