PostgreSQL/Postgre SQL/Postgre SQL Command
Содержание
- 1 Command can be used to view all sequences in the currently connected database
- 2 Displays all database objects in the connected database, with comments
- 3 Displays all database objects, with comments
- 4 Displays all databases available, with comments
- 5 Displays all data types in the connected database, with comments
- 6 Displays all functions in the connected database, with comments
- 7 Displays all indices in the connected database, with comments
- 8 Displays all operators in the connected database, with comments
- 9 Displays all sequences in the connected database, with comments
- 10 Displays all tables in the connected database, with comments
- 11 Displays all views in the connected database, with comments
- 12 Listing psql slash commands
- 13 The variable list
- 14 timestamp with time zone postmaster start time
- 15 To get out of psql
Command can be used to view all sequences in the currently connected database
<source lang="sql">
postgres=# postgres=# -- \ds command can be used to view all sequences in the currently connected database postgres=# postgres=# CREATE SEQUENCE myseq MINVALUE 0; CREATE SEQUENCE postgres=# postgres=# \ds
List of relations Schema | Name | Type | Owner
+-------+----------+----------
public | myseq | sequence | postgres
(1 row) postgres=# postgres=# postgres=# drop sequence myseq; DROP SEQUENCE postgres=# postgres=#
</source>
Displays all database objects in the connected database, with comments
<source lang="sql">
postgres=# postgres=# postgres=# --Displays all database objects in the connected database, with comments. postgres=# \d+
List of relations Schema | Name | Type | Owner | Description
+------------------------+----------+----------+-------------
public | auto_identified | table | postgres | public | auto_identified_id_seq | sequence | postgres | public | subjects | table | postgres | public | tasklog | table | postgres |
(4 rows) postgres=#
</source>
Displays all database objects, with comments
<source lang="sql">
-- Displays all database objects, with comments. \dd pg_catalog | tintervalle | function | less-than-or-equal pg_catalog | tintervalleneq | function | length equal pg_catalog | tintervallenge | function | length greater-than-or-equal pg_catalog | tintervallengt | function | length greater-than pg_catalog | tintervallenle | function | length less-than-or-equal pg_catalog | tintervallenlt | function | length less-than pg_catalog | tintervallenne | function | length not equal to pg_catalog | tintervallt | function | less-than pg_catalog | tintervalne | function | not equal pg_catalog | tintervalov | function | overlaps pg_catalog | tintervalrecv | function | I/O pg_catalog | tintervalrel | function | tinterval to reltime pg_catalog | tintervalsame | function | same as? pg_catalog | tintervalsend | function | I/O pg_catalog | tintervalstart | function | start of interval pg_catalog | to_ascii | function | encode text from DB encoding to ASCII text pg_catalog | to_ascii | function | encode text from encoding to ASCII text pg_catalog | to_char | function | format float4 to text pg_catalog | to_char | function | format float8 to text pg_catalog | to_char | function | format int4 to text pg_catalog | to_char | function | format int8 to text pg_catalog | to_char | function | format interval to text pg_catalog | to_char | function | format numeric to text pg_catalog | to_char | function | format timestamp to text pg_catalog | to_char | function | format timestamp with time zone to text pg_catalog | to_date | function | convert text to date pg_catalog | to_hex | function | convert int4 number to hex pg_catalog | to_hex | function | convert int8 number to hex pg_catalog | to_number | function | convert text to numeric pg_catalog | to_timestamp | function | convert UNIX epoch to timestamptz pg_catalog | to_timestamp | function | convert text to timestamp with time zone pg_catalog | translate | function | map a set of character appearing in string pg_catalog | trunc | function | MAC manufacturer fields pg_catalog | trunc | function | truncate to integer pg_catalog | trunc | function | value truncated to "scale" pg_catalog | trunc | function | value truncated to "scale" of zero pg_catalog | unknownrecv | function | I/O pg_catalog | unknownsend | function | I/O pg_catalog | upper | function | uppercase pg_catalog | varbit | function | adjust varbit() to typmod length pg_catalog | varbit_recv | function | I/O pg_catalog | varbit_send | function | I/O pg_catalog | varbitcmp | function | compare pg_catalog | varbiteq | function | equal pg_catalog | varbitge | function | greater than or equal pg_catalog | varbitgt | function | greater than pg_catalog | varbitle | function | less than or equal pg_catalog | varbitlt | function | less than pg_catalog | varbitne | function | not equal pg_catalog | varchar | function | adjust varchar() to typmod length pg_catalog | varchar | function | convert name to varchar pg_catalog | varcharrecv | function | I/O pg_catalog | varcharsend | function | I/O pg_catalog | version | function | PostgreSQL version string pg_catalog | width | function | box width pg_catalog | width_bucket | function | bucket number of operand in equidepth histogram pg_catalog | xid | data type | transaction id pg_catalog | xideq | function | equal pg_catalog | xideqint4 | function | equal pg_catalog | xidrecv | function | I/O pg_catalog | xidsend | function | I/O 1459 rows)
</source>
Displays all databases available, with comments
<source lang="sql">
postgres=# postgres=# -- Displays all databases available, with comments postgres=# \l+
List of databases Name | Owner | Encoding | Description
+----------+-----------+---------------------------
postgres | postgres | SQL_ASCII | template0 | postgres | SQL_ASCII | template1 | postgres | SQL_ASCII | Default template database
(3 rows) postgres=# postgres=#
</source>
Displays all data types in the connected database, with comments
<source lang="sql">
postgres=# postgres=# --Displays all data types in the connected database, with comments. postgres=# \dT+
List of data types Schema | Name | Internal name | Size | Description
+-----------------------------+------------------+------+-------------------------------------------------------------------
pg_catalog | "any" | any | 4 | pg_catalog | "char" | char | 1 | single character pg_catalog | "trigger" | trigger | 4 | pg_catalog | "unknown" | unknown | var | pg_catalog | abstime | abstime | 4 | absolute, limited-range date and time (Unix system time) pg_catalog | aclitem | aclitem | 12 | access control list pg_catalog | anyarray | anyarray | var | pg_catalog | anyelement | anyelement | 4 | pg_catalog | bigint | int8 | 8 | ~18 digit integer, 8-byte storage pg_catalog | bit | bit | var | fixed-length bit string pg_catalog | bit varying | varbit | var | variable-length bit string pg_catalog | boolean | bool | 1 | boolean, "true"/"false" pg_catalog | box | box | 32 | geometric box "(lower left,upper right)" pg_catalog | bytea | bytea | var | variable-length string, binary values escaped pg_catalog | character | bpchar | var | char(length), blank-padded string, fixed storage length pg_catalog | character varying | varchar | var | varchar(length), non-blank-padded string, variable storage length pg_catalog | cid | cid | 4 | command identifier type, sequence in transaction id pg_catalog | cidr | cidr | var | network IP address/netmask, network address pg_catalog | circle | circle | 24 | geometric circle "(center,radius)" pg_catalog | cstring | cstring | var | pg_catalog | date | date | 4 | ANSI SQL date pg_catalog | double precision | float8 | 8 | double-precision floating point number, 8-byte storage pg_catalog | inet | inet | var | IP address/netmask, host address, netmask optional pg_catalog | int2vector | int2vector | var | array of int2, used in system tables pg_catalog | integer | int4 | 4 | -2 billion to 2 billion integer, 4-byte storage pg_catalog | internal | internal | 4 | pg_catalog | interval | interval | 16 | @ <number> <units>, time interval pg_catalog | language_handler | language_handler | 4 | pg_catalog | line | line | 32 | geometric line (not implemented)" pg_catalog | lseg | lseg | 32 | geometric line segment "(pt1,pt2)" pg_catalog | macaddr | macaddr | 6 | XX:XX:XX:XX:XX:XX, MAC address pg_catalog | money | money | 4 | monetary amounts, $d,ddd.cc pg_catalog | name | name | 64 | 63-character type for storing system identifiers pg_catalog | numeric | numeric | var | numeric(precision, decimal), arbitrary precision number pg_catalog | oid | oid | 4 | object identifier(oid), maximum 4 billion pg_catalog | oidvector | oidvector | var | array of oids, used in system tables pg_catalog | opaque | opaque | 4 | pg_catalog | path | path | var | geometric path "(pt1,...)" pg_catalog | point | point | 16 | geometric point "(x, y)" pg_catalog | polygon | polygon | var | geometric polygon "(pt1,...)" pg_catalog | real | float4 | 4 | single-precision floating point number, 4-byte storage pg_catalog | record | record | var | pg_catalog | refcursor | refcursor | var | reference cursor (portal name) pg_catalog | regclass | regclass | 4 | registered class pg_catalog | regoper | regoper | 4 | registered operator pg_catalog | regoperator | regoperator | 4 | registered operator (with args) pg_catalog | regproc | regproc | 4 | registered procedure pg_catalog | regprocedure | regprocedure | 4 | registered procedure (with args) pg_catalog | regtype | regtype | 4 | registered type pg_catalog | reltime | reltime | 4 | relative, limited-range time interval (Unix delta time) pg_catalog | smallint | int2 | 2 | -32 thousand to 32 thousand, 2-byte storage pg_catalog | smgr | smgr | 2 | storage manager pg_catalog | text | text | var | variable-length string, no limit specified pg_catalog | tid | tid | 6 | (Block, offset), physical location of tuple
-- More --
</source>
Displays all functions in the connected database, with comments
<source lang="sql">
postgres=# postgres=# --Displays all functions in the connected database, with comments. postgres=# \df+
List of functions
Schema | Name | Result data type | Argument data types | Owner | Language |
Source code
|
Description
+------------------------------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------+----------+----------+---------------------------------------------------------------------------------
+------------
pg_catalog | RI_FKey_cascade_del | "trigger" | | postgres | internal | RI_FKey_cascade_del
| referential
integrity ON DELETE CASCADE
pg_catalog | RI_FKey_cascade_upd | "trigger" | | postgres | internal | RI_FKey_cascade_upd
| referential
integrity ON UPDATE CASCADE
pg_catalog | RI_FKey_check_ins | "trigger" | | postgres | internal | RI_FKey_check_ins
| referential
integrity FOREIGN KEY ... REFERENCES
pg_catalog | RI_FKey_check_upd | "trigger" | | postgres | internal | RI_FKey_check_upd
| referential
integrity FOREIGN KEY ... REFERENCES
pg_catalog | RI_FKey_noaction_del | "trigger" | | postgres | internal | RI_FKey_noaction_del
| referential
integrity ON DELETE NO ACTION
pg_catalog | RI_FKey_noaction_upd | "trigger" | | postgres | internal | RI_FKey_noaction_upd
| referential
integrity ON UPDATE NO ACTION
pg_catalog | RI_FKey_restrict_del | "trigger" | | postgres | internal | RI_FKey_restrict_del
| referential
integrity ON DELETE RESTRICT
pg_catalog | RI_FKey_restrict_upd | "trigger" | | postgres | internal | RI_FKey_restrict_upd
| referential
integrity ON UPDATE RESTRICT
pg_catalog | RI_FKey_setdefault_del | "trigger" | | postgres | internal | RI_FKey_setdefault_del
| referential
integrity ON DELETE SET DEFAULT
pg_catalog | RI_FKey_setdefault_upd | "trigger" | | postgres | internal | RI_FKey_setdefault_upd
| referential
integrity ON UPDATE SET DEFAULT
pg_catalog | RI_FKey_setnull_del | "trigger" | | postgres | internal | RI_FKey_setnull_del
| referential
integrity ON DELETE SET NULL
pg_catalog | RI_FKey_setnull_upd | "trigger" | | postgres | internal | RI_FKey_setnull_upd
| referential
-- More --
</source>
Displays all indices in the connected database, with comments
<source lang="sql">
postgres=# --Displays all indices in the connected database, with comments. postgres=# \di+
List of relations Schema | Name | Type | Owner | Table | Description
+---------------+-------+----------+----------+-------------
public | subjects_pkey | index | postgres | subjects |
(1 row) postgres=#
</source>
Displays all operators in the connected database, with comments
<source lang="sql">
postgres=# --Displays all operators in the connected database, with comments. postgres=# \do+
List of operators Schema | Name | Left arg type | Right arg type | Result type | Description
+------+-----------------------------+-----------------------------+-----------------------------+-------------------------------------------------------
pg_catalog | ! | bigint | | numeric | pg_catalog | !! | | bigint | numeric | pg_catalog | !!= | integer | text | boolean | not in pg_catalog | !!= | oid | text | boolean | not in pg_catalog | !~ | character | text | boolean | does not match regex., case-sensitive pg_catalog | !~ | name | text | boolean | does not match regex., case-sensitive pg_catalog | !~ | text | text | boolean | does not match regex., case-sensitive pg_catalog | !~* | character | text | boolean | does not match regex., case-insensitive pg_catalog | !~* | name | text | boolean | does not match regex., case-insensitive pg_catalog | !~* | text | text | boolean | does not match regex., case-insensitive pg_catalog | !~~ | bytea | bytea | boolean | does not match LIKE expression pg_catalog | !~~ | character | text | boolean | does not match LIKE expression pg_catalog | !~~ | name | text | boolean | does not match LIKE expression pg_catalog | !~~ | text | text | boolean | does not match LIKE expression pg_catalog | !~~* | character | text | boolean | does not match LIKE expression, case-insensitive pg_catalog | !~~* | name | text | boolean | does not match LIKE expression, case-insensitive pg_catalog | !~~* | text | text | boolean | does not match LIKE expression, case-insensitive pg_catalog | # | bigint | bigint | bigint | binary xor pg_catalog | # | bit | bit | bit | bitwise exclusive or pg_catalog | # | box | box | box | box intersection (another box) pg_catalog | # | integer | integer | integer | binary xor pg_catalog | # | line | line | point | intersection point pg_catalog | # | lseg | lseg | point | intersection point pg_catalog | # | smallint | smallint | smallint | binary xor pg_catalog | # | | path | integer | number of points in path pg_catalog | # | | polygon | integer | number of points in polygon pg_catalog | ## | line | box | point | closest point to line on box pg_catalog | ## | line | lseg | point | closest point to line on line segment pg_catalog | ## | lseg | box | point | closest point to line segment on box pg_catalog | ## | lseg | line | point | closest point to line segment on line pg_catalog | ## | lseg | lseg | point | closest point to line segment on line segment pg_catalog | ## | point | box | point | closest point on box pg_catalog | ## | point | line | point | closest point on line pg_catalog | ## | point | lseg | point | closest point on line segment pg_catalog | #< | tinterval | reltime | boolean | length less-than pg_catalog | #<= | tinterval | reltime | boolean | length less-than-or-equal pg_catalog | #<> | tinterval | reltime | boolean | length not equal to pg_catalog | #= | tinterval | reltime | boolean | length equal pg_catalog | #> | tinterval | reltime | boolean | length greater-than pg_catalog | #>= | tinterval | reltime | boolean | length greater-than-or-equal pg_catalog | % | bigint | bigint | bigint | modulus pg_catalog | % | integer | integer | integer | modulus pg_catalog | % | integer | smallint | integer | modulus pg_catalog | % | numeric | numeric | numeric | modulus pg_catalog | % | smallint | integer | integer | modulus pg_catalog | % | smallint | smallint | smallint | modulus pg_catalog | & | bigint | bigint | bigint | binary and pg_catalog | & | bit | bit | bit | bitwise and pg_catalog | & | integer | integer | integer | binary and pg_catalog | & | smallint | smallint | smallint | binary and pg_catalog | && | box | box | boolean | overlaps pg_catalog | && | circle | circle | boolean | overlaps pg_catalog | && | polygon | polygon | boolean | overlaps pg_catalog | && | tinterval | tinterval | boolean | overlaps
-- More --
</source>
Displays all sequences in the connected database, with comments
<source lang="sql">
postgres=# --Displays all sequences in the connected database, with comments. postgres=# \ds+
List of relations Schema | Name | Type | Owner | Description
+------------------------+----------+----------+-------------
public | auto_identified_id_seq | sequence | postgres |
(1 row) postgres=#
</source>
Displays all tables in the connected database, with comments
<source lang="sql">
postgres=# --Displays all tables in the connected database, with comments. postgres=# \dt+
List of relations Schema | Name | Type | Owner | Description
+-----------------+-------+----------+-------------
public | auto_identified | table | postgres | public | subjects | table | postgres | public | tasklog | table | postgres |
(3 rows) postgres=#
</source>
Displays all views in the connected database, with comments
<source lang="sql">
postgres=# --Displays all views in the connected database, with comments. postgres=# \dv+ No relations found. postgres=#
</source>
Listing psql slash commands
<source lang="sql">
postgres=# postgres=# postgres=# -- Listing psql slash commands postgres=# postgres=# \? General
\c[onnect] [DBNAME|- [USER]]
connect to new database (currently "postgres")
\cd [DIR] change the current working directory
\copyright show PostgreSQL usage and distribution terms
\encoding [ENCODING]
show or set client encoding
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing toggle timing of commands (currently off)
\unset NAME unset (delete) internal variable
\! [COMMAND] execute command in shell or start interactive shell
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor \g [FILE] send query buffer to server (and results to file or |pipe) \p show the contents of the query buffer \r reset (clear) the query buffer \w FILE write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING]
write string to query output stream (see \o)
Informational
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\db [PATTERN] list tablespaces (add "+" for more detail)
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
\df [PATTERN] list functions (add "+" for more detail)
\dg [PATTERN] list groups
\dn [PATTERN] list schemas (add "+" for more detail)
\do [NAME] list operators
\dl list large objects, same as \lo_list
\dp [PATTERN] list table, view, and sequence access privileges
\dT [PATTERN] list data types (add "+" for more detail)
\du [PATTERN] list users
\l list all databases (add "+" for more detail)
\z [PATTERN] list table, view, and sequence access privileges (same as \dp)
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t show only rows (currently off)
\T [STRING] set HTML 