postgres=# \d List of relations Schema | Name | Type | Owner --------+-------------+----------+---------- public | animal | table | laetitia public | test | table | laetitia public | test_id_seq | sequence | laetitia (3 rows)
postgres=# \d List of relations Schema | Name | Type | Owner --------+-------------+----------+---------- public | animal | table | laetitia public | test | table | laetitia public | test_id_seq | sequence | laetitia (3 rows)
postgres=# \dS List of relations Schema | Name | Type | Owner ------------+---------------------------------+----------+---------- pg_catalog | pg_aggregate | table | postgres pg_catalog | pg_am | table | postgres pg_catalog | pg_amop | table | postgres pg_catalog | pg_amproc | table | postgres [...] pg_catalog | pg_views | view | postgres public | animal | table | laetitia public | test | table | laetitia public | test_id_seq | sequence | laetitia (136 rows)
postgres=# \d pg_aggregate Table "pg_catalog.pg_aggregate" Column | Type | Collation | Nullable | Default ------------------+----------+-----------+----------+--------- aggfnoid | regproc | | not null | aggkind | "char" | | not null | aggnumdirectargs | smallint | | not null | aggtransfn | regproc | | not null | aggfinalfn | regproc | | not null | aggcombinefn | regproc | | not null | aggserialfn | regproc | | not null | aggdeserialfn | regproc | | not null | aggmtransfn | regproc | | not null | aggminvtransfn | regproc | | not null | aggmfinalfn | regproc | | not null | aggfinalextra | boolean | | not null | aggmfinalextra | boolean | | not null | aggfinalmodify | "char" | | not null | aggmfinalmodify | "char" | | not null | aggsortop | oid | | not null | aggtranstype | oid | | not null | aggtransspace | integer | | not null | aggmtranstype | oid | | not null | aggmtransspace | integer | | not null | agginitval | text | C | | aggminitval | text | C | | Indexes: "pg_aggregate_fnoid_index" PRIMARY KEY, btree (aggfnoid)
postgres=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row)
postgres=# \dnS List of schemas Name | Owner --------------------+---------- information_schema | postgres pg_catalog | postgres pg_toast | postgres public | postgres (4 rows)
postgres=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | postgres | postgres=UC/postgres+| standard public schema | | =UC/postgres | (1 row)
postgres=# alter default privileges for role test revoke all on tables from test; ALTER DEFAULT PRIVILEGES laetitia=# \ddp Default access privileges Owner | Schema | Type | Access privileges -------+--------+-------+------------------- test | | table | (1 row)
postgres=# \dE List of relations Schema | Name | Type | Owner --------+------+---------------+---------- public | logs | foreign table | postgres (1 row)
postgres=# \dE+ List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+------+---------------+----------+-------------+---------+------------- public | logs | foreign table | postgres | permanent | 0 bytes | (1 row)
postgres=# \dES List of relations Schema | Name | Type | Owner --------+------+---------------+---------- public | logs | foreign table | postgres (1 row)
postgres=# \di List of relations Schema | Name | Type | Owner | Table --------+----------------+-------+----------+------- public | test_value_key | index | postgres | test (1 row)
postgres=# \di+ List of relations Schema | Name | Type | Owner | Table | Persistence | Access Method | Size | Description --------+----------------+-------+----------+-------+-------------+---------------+-------+------------- public | test_value_key | index | postgres | test | permanent | btree | 16 kB | (1 row)
postgres=# \diS List of relations Schema | Name | Type | Owner | Table ------------+-----------------------------------------------+-------+----------+------------------------- pg_catalog | pg_aggregate_fnoid_index | index | postgres | pg_aggregate pg_catalog | pg_am_name_index | index | postgres | pg_am pg_catalog | pg_am_oid_index | index | postgres | pg_ami [...] pg_catalog | pg_user_mapping_user_server_index | index | postgres | pg_user_mapping public | test_value_key | index | postgres | test (118 rows)
postgres=# \dm List of relations Schema | Name | Type | Owner --------+------+-------------------+---------- public | mymv | materialized view | postgres (1 row)
postgres=# \dm+ List of relations Schema | Name | Type | Owner | Persistence | Access Method | Size | Description --------+------+-------------------+----------+-------------+---------------+-------+------------- public | mymv | materialized view | postgres | permanent | heap | 16 kB | (1 row)
postgres=# \dmS List of relations Schema | Name | Type | Owner --------+------+-------------------+---------- public | mymv | materialized view | postgres (1 row)
postgres=# \ds List of relations Schema | Name | Type | Owner --------+-----------------------+----------+---------- public | mytest_id_seq | sequence | test public | partition_test_id_seq | sequence | postgres public | test_id_seq | sequence | postgres (3 rows)
(3 rows)
postgres=# \ds+ Schema | Name | Type | Owner | Persistence | Size | Description --------+-----------------------+----------+----------+-------------+------------+------------- public | mytest_id_seq | sequence | test | permanent | 8192 bytes | public | partition_test_id_seq | sequence | postgres | permanent | 8192 bytes | public | test_id_seq | sequence | postgres | permanent | 8192 bytes | List of relations postgres=# \dsS List of relations Schema | Name | Type | Owner --------+-----------------------+----------+---------- public | mytest_id_seq | sequence | test public | partition_test_id_seq | sequence | postgres public | test_id_seq | sequence | postgres (3 rows)
postgres=# \dt List of relations Schema | Name | Type | Owner --------+-----------------+-------------------+---------- public | animal | table | postgres public | first_partition | table | postgres public | mytest | table | test public | partition_test | partitioned table | postgres public | test | table | postgres (5 rows)
postgres=# \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access Method | Size | Description --------+-----------------+-------------------+----------+-------------+---------------+------------+--------------- public | animal | table | postgres | permanent | heap | 16 kB | public | first_partition | table | postgres | permanent | heap | 8192 bytes | public | mytest | table | test | permanent | heap | 0 bytes | public | partition_test | partitioned table | postgres | permanent | | 0 bytes | public | test | table | postgres | permanent | heap | 16 kB | table comment (5 rows)
postgres=# \dtS List of relations Schema | Name | Type | Owner ------------+-------------------------+-------------------+---------- pg_catalog | pg_aggregate | table | postgres pg_catalog | pg_am | table | postgres [...] public | partition_test | partitioned table | postgres public | test | table | postgres (67 rows)
postgres=# \df List of functions Schema | Name | Result data type | Argument data types | Type --------+--------------------+------------------+---------------------+------ public | file_fdw_handler | fdw_handler | | func public | file_fdw_validator | void | text[], oid | func (2 rows)
postgres=# \df+ List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description --------+--------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+--------------------+------------- public | file_fdw_handler | fdw_handler | | func | volatile | unsafe | postgres | invoker | | c | file_fdw_handler | public | file_fdw_validator | void | text[], oid | func | volatile | unsafe | postgres | invoker | | c | file_fdw_validator | (2 rows)
postgres=# \dfS xpath* List of functions Schema | Name | Result data type | Argument data types | Type ------------+--------------+------------------+---------------------+------ pg_catalog | xpath | xml[] | text, xml | func pg_catalog | xpath | xml[] | text, xml, text[] | func pg_catalog | xpath_exists | boolean | text, xml | func pg_catalog | xpath_exists | boolean | text, xml, text[] | func (4 rows)
技巧0087 (\dfa)
\df后边跟一个a，表示所有的聚集函数，始自PG8.4
1 2 3 4 5 6 7 8 9 10 11
postgres=# \dfa variance List of functions Schema | Name | Result data type | Argument data types | Type ------------+----------+------------------+---------------------+------ pg_catalog | variance | numeric | bigint | agg pg_catalog | variance | double precision | double precision | agg pg_catalog | variance | numeric | integer | agg pg_catalog | variance | numeric | numeric | agg pg_catalog | variance | double precision | real | agg pg_catalog | variance | numeric | smallint | agg (6 rows)
技巧0088 (\dfn)
\dfn，表示所有的”normal”函数。始自PG8.4
1 2 3 4 5 6 7
postgres=# \dfn List of functions Schema | Name | Result data type | Argument data types | Type --------+--------------------+------------------+---------------------+------ public | file_fdw_handler | fdw_handler | | func public | file_fdw_validator | void | text[], oid | func (2 rows)
技巧0089 (\dfp)
在\df后边加上p, 表示只列出所有的procedure。始自PG11
1 2 3 4 5 6
postgres=# \dfp List of functions Schema | Name | Result data type | Argument data types | Type --------+------------+------------------+---------------------+------ public | inset_data | | a text | proc (1 row)
postgres=# \dg List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | | {}
postgres=# \dg+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- postgres | Superuser | {} | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | test | | {} |
postgres=# \dl Large objects ID | Owner | Description ----+-------+------------- (0 rows)
postgres=# \dL List of languages Name | Owner | Trusted | Description ---------+----------+---------+------------------------------ plpgsql | postgres | t | PL/pgSQL procedural language (1 row)
postgres=# \dL+ List of languages Name | Owner | Trusted | Internal language | Call handler | Validator | Inline handler | Access privileges | Description ---------+----------+---------+-------------------+------------------------+------------------------+----------------------------------+-------------------+------------------------------ plpgsql | postgres | t | f | plpgsql_call_handler() | plpgsql_validator(oid) | plpgsql_inline_handler(internal) | | PL/pgSQL procedural language (1 row)
postgres=# \dLS List of languages Name | Owner | Trusted | Description ----------+----------+---------+-------------------------------- c | postgres | f | dynamically-loaded C functions internal | postgres | f | built-in functions plpgsql | postgres | t | PL/pgSQL procedural language sql | postgres | t | SQL-language functions (4 rows)
postgres=# \copy test(value) from stdin Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> value1 >> value2 >> \. COPY 2 postgres=# select * from test; id | value ----+-------- 1 | test 5 | blabla 6 | value1 7 | value2 (4 rows)
技巧0103 (\copy)
\copy与copy这个SQL命令不同，\copy元命令是运行在客户端上，不需要superuser权限，而copy则需要superuser权限或者具备下述权限之一才能执行：pg_read_server_files, pg_write_server_files, or pg_execute_server_program。始自PG7.1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
postgres=# \c postgres test You are now connected to database "postgres" as user "test". postgres=> \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+-------- postgres | Superuser | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | | {}
postgres=> \copy (select * from test) to mydata.dmp COPY 4 postgres=> \! cat mydata.dmp 1 test 5 blabla 6 value1 7 value2
postgres=> create table data(d1 integer, d2 date); CREATE TABLE postgres=> \copy data from stdin Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 1 '2021-04-12' >> 2 '2021-04-13' >> \. ERROR: invalid input syntax for type integer: "2 '2021-04-13'" CONTEXT: COPY data, line 2, column d1: "2 '2021-04-13'"
postgres=> select * from music ; artist | album | year -----------------------+-------------------+------ Foo Fighters | Concrete and Gold | 2017 Red Hot Chili Peppers | The Getaway | 2016 Green Day | ¡Uno! | 2012 (3 rows)
postgres=> \crosstabview artist | Concrete and Gold | The Getaway | ¡Uno! -----------------------+-------------------+-------------+------- Foo Fighters | 2017 | | Red Hot Chili Peppers | | 2016 | Green Day | | | 2012 (3 rows)
技巧0107 (\crosstabview)
你可以传列名给\crosstabview，以显示列的头和行的头.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
postgres=> select * from music ; artist | album | year -----------------------+-------------------+------ Foo Fighters | Concrete and Gold | 2017 Red Hot Chili Peppers | The Getaway | 2016 Green Day | ¡Uno! | 2012 (3 rows)
postgres=> \crosstabview artist year artist | 2017 | 2016 | 2012 -----------------------+-------------------+-------------+------- Foo Fighters | Concrete and Gold | | Red Hot Chili Peppers | | The Getaway | Green Day | | | ¡Uno! (3 rows)
技巧0108 (\crosstabview)
可以用列的序号来指定行头和列头.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
postgres=> select * from music ; artist | album | year -----------------------+-------------------+------ Foo Fighters | Concrete and Gold | 2017 Red Hot Chili Peppers | The Getaway | 2016 Green Day | ¡Uno! | 2012 (3 rows)
postgres=> postgres=> \crosstabview 1 3 artist | 2017 | 2016 | 2012 -----------------------+-------------------+-------------+------- Foo Fighters | Concrete and Gold | | Red Hot Chili Peppers | | The Getaway | Green Day | | | ¡Uno! (3 rows)
postgres=# \dT List of data types Schema | Name | Description --------+---------------+------------- public | one_word_only | (1 row)
postgres=# \dTS line List of data types Schema | Name | Description ------------+------+---------------- pg_catalog | line | geometric line (1 row)
postgres=# \dT+ List of data types Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description --------+---------------+---------------+------+----------+----------+-------------------+------------- public | one_word_only | one_word_only | var | | postgres | | (1 row)
postgres=# \dT List of data types Schema | Name | Description --------+---------------+------------- public | one_word_only | (1 row)
postgres=# \dTS line List of data types Schema | Name | Description ------------+------+---------------- pg_catalog | line | geometric line (1 row)
postgres=# \dT+ List of data types Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description --------+---------------+---------------+------+----------+----------+-------------------+------------- public | one_word_only | one_word_only | var | | postgres | | (1 row)
postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | | {}
postgres=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row)
postgres=# \dx+ Objects in extension "plpgsql" Object description ------------------------------------------- function plpgsql_call_handler() function plpgsql_inline_handler(internal) function plpgsql_validator(oid) language plpgsql (4 rows)
postgres=# \df add_em List of functions Schema | Name | Result data type | Argument data types | Type --------+--------+------------------+----------------------------------------+------ public | add_em | double precision | x double precision, y double precision | func public | add_em | integer | x integer, y integer | func (2 rows)
postgres=# \ef add_em ERROR: more than one function named "add_em" postgres=# \ef add_em(integer, integer) No changes
始自PG8.4, PG11开始支持过程。
技巧0121 (\ef function linenumber)
\ef function linenumber支持编辑函数中指定的行号
始自PG8.4, PG11开始支持过程。
技巧0122 （\ef）
\ef 不指定的时候，就会用缺省的模板，create function template。始自PG9.1, PG11开始支持过程。
技巧0123 (\ev)
\ev view会默认打开: create or replace view ，包含指定view的SQL代码。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
postgres=# \dv connection_settings List of relations Schema | Name | Type | Owner --------+---------------------+------+---------- public | connection_settings | view | postgres (1 row)
postgres=# \ev connection_settings postgres=# CREATE OR REPLACE VIEW public.connection_settings AS SELECT pg_settings.name, pg_settings.setting, pg_settings.unit FROM pg_settings WHERE pg_settings.name ~ 'connection'::text
postgres=# select 'drop table ' || table_name postgres-# from information_schema.tables postgres-# where table_schema = 'public' postgres-# and table_name ~ 'test'; ?column? ------------------ drop table test drop table test2 (2 rows)
postgres=# \gexec DROP TABLE DROP TABLE postgres=#
postgres=# select * from pg_settings where name = 'log_directory'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_ val | reset_val | sourcefile | sourceline | pending_restart ---------------+---------+------+--------------------------------------+-----------------------------------------------+---------------- ---------------------------------------------------------+---------+---------+--------------------+---------+---------+----------+------ ----+-----------+----------------------------------------+------------+----------------- log_directory | log | | Reporting and Logging / Where to Log | Sets the destination directory for log files. | Can be specifie d as relative to the data directory or as absolute path. | sighup | string | configuration file | | | | log | log | /var/lib/pgsql/14/data/postgresql.conf | 801 | f (1 row)
postgres=# \gx -[ RECORD 1 ]---+------------------------------------------------------------------------ name | log_directory setting | log unit | category | Reporting and Logging / Where to Log short_desc | Sets the destination directory for log files. extra_desc | Can be specified as relative to the data directory or as absolute path. context | sighup vartype | string source | configuration file min_val | max_val | enumvals | boot_val | log reset_val | log sourcefile | /var/lib/pgsql/14/data/postgresql.conf sourceline | 801 pending_restart | f
postgres=# \gx abc.out postgres=# \! cat abc.out -[ RECORD 1 ]---+------------------------------------------------------------------------ name | log_directory setting | log unit | category | Reporting and Logging / Where to Log short_desc | Sets the destination directory for log files. extra_desc | Can be specified as relative to the data directory or as absolute path. context | sighup vartype | string source | configuration file min_val | max_val | enumvals | boot_val | log reset_val | log sourcefile | /var/lib/pgsql/14/data/postgresql.conf sourceline | 801 pending_restart | f
技巧0137 (\gx)
\gx也可以把结果送到另一个命令里头
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
postgres=# select * from pg_settings where name = 'log_directory'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_ val | reset_val | sourcefile | sourceline | pending_restart ---------------+---------+------+--------------------------------------+------------------------------------ log_directory | log | | Reporting and Logging / Where to Log | Sets the destination directory for log files. | Can be specifie d as relative to the data directory or as absolute path. | sighup | string | configuration file | | | | log | log | /var/lib/pgsql/14/data/postgresql.conf | 801 | f (1 row)
postgres=# \gx | grep -i 'log' name | log_directory setting | log category | Reporting and Logging / Where to Log short_desc | Sets the destination directory for log files. boot_val | log reset_val | log
技巧0138 （\h)
\h给出任意SQL命令的语法. 始自PG7.1
1 2 3 4 5 6 7 8 9 10 11 12 13
postgres=# \h Available help: ABORT CHECKPOINT CREATE USER DROP TRIGGER ALTER AGGREGATE CLOSE CREATE USER MAPPING DROP TYPE ALTER COLLATION CLUSTER CREATE VIEW DROP USER ALTER CONVERSION COMMENT DEALLOCATE DROP USER MAPPING ALTER DATABASE COMMIT DECLARE DROP VIEW ALTER DEFAULT PRIVILEGES COMMIT PREPARED DELETE END ALTER DOMAIN COPY DISCARD EXECUTE ALTER EVENT TRIGGER CREATE ACCESS METHOD DO EXPLAIN ALTER EXTENSION CREATE AGGREGATE DROP ACCESS METHOD FETCH ALTER FOREIGN DATA WRAPPER CREATE CAST DROP AGGREGATE GRANT ALTER FOREIGN TABLE CREATE COLLATION DROP CAST IMPORT FOREIGN SCHEMA