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
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=#
Displays all database objects in the connected database, with comments
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=#
Displays all database objects, with comments
-- 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)
Displays all databases available, with comments
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=#
Displays all data types in the connected database, with comments
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 --
Displays all functions in the connected database, with comments
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 --
Displays all indices in the connected database, with comments
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=#
Displays all operators in the connected database, with comments
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 --
Displays all sequences in the connected database, with comments
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=#
Displays all tables in the connected database, with comments
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=#
Displays all views in the connected database, with comments
postgres=# --Displays all views in the connected database, with comments.
postgres=# \dv+
No relations found.
postgres=#
Listing psql slash commands
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 <table> tag attributes, or unset if none
\x toggle expanded output (currently off)
Copy, Large Object
\copy ... perform SQL COPY with data stream to the client host
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
postgres=#
The variable list
postgres=#
postgres=# -- The variable list
postgres=#
postgres=# \set
VERSION = "PostgreSQL 8.1.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
AUTOCOMMIT = "on"
VERBOSITY = "default"
PROMPT1 = "%/%R%# "
PROMPT2 = "%/%R%# "
PROMPT3 = ">> "
DBNAME = "postgres"
USER = "postgres"
HOST = "localhost"
PORT = "5432"
ENCODING = "SQL_ASCII"
LASTOID = "0"
data = "cat tabledata"
postgres=#
postgres=#
timestamp with time zone postmaster start time
postgres=#
postgres=# -- timestamp with time zone postmaster start time
postgres=# select pg_postmaster_start_time() ;
pg_postmaster_start_time
----------------------------
2006-10-12 07:48:20.453-07
(1 row)
postgres=#
To get out of psql
-- To get out of psql, type
\q