Common Psql commands

Common Psql commands Source: http://www.cnblogs.com/happyhotty/articles/1920455.html

\ D[ Table]

List tables in the database or (if declared) tables TableIf the table name is declared with a wildcard ("*"), it lists the column/field information of all tables and tables.

\ Da

Lists all available aggregates.

\ Dd Object

List Pg_descriptionDescription of the declared object. The object can be a table, column/field, type, operator, or aggregation in the table.

TIPS:Not all objects are inPg_descriptionIt is described in. This post-command is useful for quickly obtaining internal Postgres features.

\ DF

List functions.

\ Di

Only list indexes.

\ Do

Only list operators.

\ DS

Only list sequences.

\ DS

List System tables and indexes.

\ Dt

Only list non-system tables.

\ Dt

List types.

\ E[ Filename]

Edit current query buffer or file Filename.

\ E[ Filename]

Edit current query buffer or file FilenameAnd executed after the editing is complete.

\ F[ Separator]

Set the domain separator. The default value is a single blank space.

\ G[{ Filename| | Command}]

Buffer the current query input to the backend and (optional) Put the output FilenameOr, the output is sent to a separate Unix shell through a pipeline for execution. Command.

\ H[ Command]

The declared SQL command syntax help is provided. If CommandIt is not a defined SQL command (or there is no documentation in Psql), or it is not declared CommandIn this case, Psql will list all commands that can be used for help. If the command Command
Is a wildcard ("*"), it provides syntax help for all SQL commands.

\ H

Switching html3 output is equivalent
-H command line options.

\ I Filename

Slave File FilenameRead and query the input buffer.

\ L

List all databases on the server.

\ M

Switch the output of the old-fashioned monitor table. At this time, the table is surrounded by boundary characters. This is a standard SQL output. There is a shortage of time, and Psql only contains delimiters between columns and fields.

\ O[{ Filename| | Command}]

Output The following query results to the file. FilenameOr output the subsequent results to an independent Unix shell in a pipeline for execution. Command.If no parameter is declared, the query result is output Stdout.

\ P

Print the current query buffer.

\ Q

Exit the Psql program.

\ R

Reset (empty) Query Buffer.

\ S[ Filename]

Print or store the command line history Filename.If omitted FilenameThe subsequent commands will not be stored in the history file. This option is valid only when Psql is configured to use the input line.

\ T

Switch the information header of the output column/field name and the row footer (on by default ).

\ T Table_options

Allow you to declare the output in the HTML 3.0 format in the table Table...For example, BorderThe border will be given to your table. This must be the same \ HLater commands are used together.

\ X

Switch the extended row format. when it is enabled, each row prints the column/field name on the left and the column/field value on the right. this is useful for extra-long rows that cannot be output in one row. this tag is also supported in the HTML row output mode.

\ W Filename

Output the current query buffer to a file Filename.

\ Z

Generate an output list of all tables in the database with the correct ACL (grant/deny permissions.

\![ Command]

Return to an independent Unix shell or execute a Unix Command Command.

\?

For help with the backslash ("\") command.

General options

\ C [onnect] [database name |-[user name]
Connect to the new database (currently "test ")
\ CD [directory name] changes the current working directory
\ Copyright: displays PostgreSQL usage and publishing information
\ Encoding [encoding]
Display or Set Client Encoding
\ H [name] SQL command syntax help. You can use * to view the help of all commands.
\ Q exit Psql
\ Set [name [value]
Set internal variables to list all
\ Timing time switch (currently disabled)
\ Unset name cancel (delete) Internal Variable
\! [Command] execute commands in shell or start an Interactive Shell

Information options

\ D [name] description table, index, sequence, or view
\ D {T | I | S | v | S} [mode] (add "+" to obtain more information)
List tables/indexes/sequences/views/system tables
\ Da [mode] listing Aggregate functions
\ DB [mode] list tablespaces (add "+" to obtain more information)
\ DC [mode] list encoding conversion
\ DC list type conversion
\ Dd [mode] displays the comment of the target
\ Dd [mode] list fields
\ DF [mode] list functions (add "+" to obtain more information)
\ DG [mode] list groups
\ DN [mode] list mode (add "+" to obtain more information)
\ Do [name] listing Operators
\ DL lists large objects, the same as \ lo_list
\ DP [mode] lists access permissions for tables, views, and Sequences
\ Dt [mode] lists data types (add "+" to obtain more information)
\ Du [mode] List users
\ L list all databases (add "+" for more information)
\ Z [mode] lists the table, view, and sequence access permissions (same as \ DP)

Command: Abort
Description: ends the current transaction.
Syntax:
Abort [Work | transaction]

Command: Alter Database
Description: changes a database.
Syntax:
Alter database name set parameter {to |=}{ value | default}
Alter database name reset Parameter
Alter database name Rename to new name
Alter database name owner to new owner
Command: Alter Group
Description: Used to change a user group.
Syntax:
Alter group name Add User user name [,...]
Alter group name drop user name [,...]
Alter group name Rename to new name

Command: Alter Index
Description: used to change the definition of an index.
Syntax:
Alter Index name
Action [,...]
Alter Index old name
Rename to index new name
The action is one of the following:
Owner to new owner
Set tablespace indexspace_name

Command: Alter Sequence
Description: used to change the definition of a sequence generator.
Syntax:
Alter sequence name [increment [by] increments]
[Minvalue minimum value | no minvalue] [maxvalue maximum value | no maxvalue]
[Restart [with] start] [cache] [[No] cycle]

Command: ALTER TABLE
Description: used to change the definition of a table.
Syntax:
Alter table [only] Table name [*]
Action [,...]
Alter table [only] Table name [*]
RENAME [column] field name to new field name
Alter table table name
Rename to new table name
Action is one of the following:
Add [column] field name type [field constraint [...]
Drop [column] field name [restrict | cascade]
Alter [column] field name type [using expression]
Alter [column] field name set default expression
Alter [column] field name drop default
Alter [column] field name {set | drop} not null
Alter [column] field name set statistics integer
Alter [column] field name set storage {plain | external | extended | main}
Add Table Constraints
Drop constraint name [restrict | cascade]
Cluster on index name
Set without Cluster
Set without oids
Owner to new owner
Set tablespace name

Command: Alter trigger
Description: used to change the definition of a trigger.
Syntax:
Alter trigger name on table rename to new name

Command: Alter user
Description: Used to change a database user.
Syntax:
Alter user name [[with] Option [...]
Where option can be:
Createdb | nocreatedb
| Createuser | nocreateuser
| [Encrypted | unencrypted] Password 'Password'
| Valid until 'abstime'
Alter user name Rename to newname
Alter user name set parameter {to |=}{ value | default}

Command: Copy
Description: Copy data between a file and a table.
Syntax:
Copy table name [(field [,...])]
From {'filename '| stdin}
[With]
[Binary]
[Oids]
[Delimiter [as] 'delimiter']
[Null [as] 'null string']
[CSV [quote [as] 'quote']
[Escape [as] 'escape ']
[Force not null column [,...]

Copy table name [(field [,...])]
To {'filename '| stdout}
[With]
[Binary]
[Oids]
[Delimiter [as] 'delimiter']
[Null [as] 'null string']
[CSV [quote [as] 'quote']
[Escape [as] 'escape ']
[Force quote column [,...]

Command: Create Table
Description: defines a new table.
Syntax:
Create [[Global | Local] {temporary | temp}] Table table_name (
{Column_name data_type [Default default_expr] [column_constraint [...]
| Table_constraint
| Like parent_table [{including | excluding} defaults]} [,...]
)
[Inherits (parent_table [,...])]
[With oids | without oids]
[On commit {preserve rows | Delete rows | drop}]
[Tablespace]

Where column_constraint is:

[Constraint constraint_name]
{Not null |
Null |
Unique [using index tablespace] |
Primary Key [using index tablespace] |
Check (expression) |
References reftable [(refcolumn)] [match full | match partial | match simple]
[On Delete action] [on update action]}
[Deferrable | not deferrable] [initially deferred | initially immediate]

And table_constraint is:

[Constraint constraint_name]
{Unique (column_name [,...]) [using index tablespace] |
Primary Key (column_name [,...]) [using index tablespace] |
Check (expression) |
Foreign key (column_name [,...]) References reftable [(refcolumn [,...])]
[Match full | match partial | match simple] [ON Delete action] [on update action]}
[Deferrable | not deferrable] [initially deferred | initially immediate]