YouTip

By default, after PostgreSQL installation is complete, it comes with a command-line tool **SQL Shell (psql)**. **Linux** systems can directly switch to the postgres user to start the command-line tool: # sudo -i -u postgres **Windows** systems are generally located in its installation directory: Program Files → PostgreSQL 11.3 → SQL Shell(psql) **Mac OS** we can directly search to find it: !(#) After entering the command-line tool, we can use help to view the syntax of various commands: postgres-# help For example, let's view the syntax of the select statement: postgres=# help SELECT Command: SELECT Description: retrieve rows from a table or view Syntax:[ WITH with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] [ * | expression [ output_name ] [, ...] ] [ FROM from_item [, ...] ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] from_item can be one of the following options: table_name [ * ] [ alias [ ( column_alias [, ...] ) ] ] * * * ## SQL Statements A SQL statement typically contains keywords, identifiers (fields), constants, special symbols, etc. Here is a simple SQL statement: SELECT id, name FROM | | SELECT | id, name | FROM | | | --- | --- | --- | --- | --- | | Symbol Type | Keyword | Identifier (Field) | Keyword | Identifier | | Description | Command | id and name fields | Statement, used to set conditions, rules, etc. | Table Name | * * * ## PostgreSQL Commands ### ABORT ABORT is used to exit the current transaction. ABORT [ WORK | TRANSACTION ] ### ALTER AGGREGATE Modifies the definition of an aggregate function. ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) RENAME TO _new_name_ ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) OWNER TO _new_owner_ ALTER AGGREGATE _name_ ( _argtype_ [ , ... ] ) SET SCHEMA _new_schema_ ### ALTER COLLATION Modifies the definition of a collation. ALTER COLLATION _name_ RENAME TO _new_name_ ALTER COLLATION _name_ OWNER TO _new_owner_ ALTER COLLATION _name_ SET SCHEMA _new_schema_ ### ALTER CONVERSION Modifies the definition of an encoding conversion. ALTER CONVERSION name RENAME TO new_name ALTER CONVERSION name OWNER TO new_owner ### ALTER DATABASE Modifies a database. 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 ### ALTER DEFAULT PRIVILEGES Defines default access privileges. ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke where abbreviated_grant_or_revoke is one of: GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL } ON TABLES TO { role_name | PUBLIC } [, ...] ... ### ALTER DOMAIN Modifies the definition of a domain. ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT } ALTER DOMAIN name { SET | DROP } NOT NULL ALTER DOMAIN name ADD domain_constraint ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] ALTER DOMAIN name OWNER TO new_owner ### ALTER FUNCTION Modifies the definition of a function. ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner ### ALTER GROUP Modifies a user group. ALTER GROUP groupname ADD USER username [, ... ] ALTER GROUP groupname DROP USER username [, ... ] ALTER GROUP groupname RENAME TO new_name ### ALTER INDEX Modifies the definition of an index. ALTER INDEX name OWNER TO new_owner ALTER INDEX name SET TABLESPACE indexspace_name ALTER INDEX name RENAME TO new_name ### ALTER LANGUAGE Modifies the definition of a procedural language. ALTER LANGUAGE name RENAME TO new_name ### ALTER OPERATOR Changes the definition of an operator. ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } ) OWNER TO new_owner ### ALTER OPERATOR CLASS Modifies the definition of an operator class. ALTER OPERATOR CLASS name USING index_method RENAME TO new_name ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner ### ALTER SCHEMA Modifies the definition of a schema. ALTER SCHEMA name RENAME TO new_name ALTER SCHEMA name OWNER TO new_owner ### ALTER SEQUENCE Modifies the definition of a sequence generator. ALTER SEQUENCE name [ INCREMENT increment ][ MINVALUE minvalue | NO MINVALUE ][ MAXVALUE maxvalue | NO MAXVALUE ][ RESTART start ] [ CYCLE ] ### ALTER TABLE Modifies the definition of a table. ALTER TABLE name [ * ] action [, ... ] ALTER TABLE name [ * ] RENAME column TO new_column ALTER TABLE name RENAME TO new_name where action can be one of the following options: ADD column_type [ column_constraint [ ... ] ] DROP column [ RESTRICT | CASCADE ] ALTER column TYPE type ALTER column SET DEFAULT expression ALTER column DROP DEFAULT ALTER column { SET | DROP } NOT NULL ALTER column SET STATISTICS integer ALTER column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] CLUSTER ON index_name SET WITHOUT CLUSTER SET WITHOUT OIDS OWNER TO new_owner SET TABLESPACE tablespace_name ### ALTER TABLESPACE Modifies the definition of a tablespace. ALTER TABLESPACE name RENAME TO new_name ALTER TABLESPACE name OWNER TO new_owner ### ALTER TRIGGER Modifies the definition of a trigger. ALTER TRIGGER name ON table RENAME TO new_name ### ALTER TYPE Modifies the definition of a type. ALTER TYPE name OWNER TO new_owner ### ALTER USER Modifies a database user account. ALTER USER name [ option [ ... ] ] ALTER USER name RENAME TO new_name ALTER USER name SET parameter { TO | = } { value | DEFAULT } ALTER USER name RESET parameter Where _option_ can be − [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'| CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | VALID UNTIL 'abstime' ### ANALYZE Collects statistics about the database. ANALYZE [ table [ (column [, ...] ) ] ] ### BEGIN Starts a transaction block. BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] _transaction_mode_ can be one of the following options: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY ### CHECKPOINT Forces a transaction log checkpoint. CHECKPOINT ### CLOSE Closes a cursor. CLOSE name ### CLUSTER Clusters a table according to an index. CLUSTER index_name ON table_name CLUSTER table_name CLUSTER ### COMMENT Defines or changes the comment of an object. COMMENT ON { TABLE object_name | COLUMN table_name.column_name | AGGREGATE agg_name (agg_type) | CAST (source_type AS target_type) | CONSTRAINT constraint_name ON table_name | CONVERSION object_name | DATABASE object_name | DOMAIN object_name | FUNCTION func_name (arg1_type, arg2_type, ...) | INDEX object_name | LARGE OBJECT large_object_oid | OPERATOR op (left_operand_type, right_operand_type) | OPERATOR CLASS object_name USING index_method | LANGUAGE object_name | RULE rule_name ON table_name | SCHEMA object_name | SEQUENCE object_name | TRIGGER trigger_name ON table_name | TYPE object_name | VIEW object_name } IS 'text' ### COMMIT Commits the current transaction. COMMIT [ WORK | TRANSACTION ] ### COPY Copies data between a table and a file. COPY table_name [ ( column [, ...] ) ] FROM { 'filename' | STDIN }[ DELIMITER 'delimiter' ][ NULL 'null string' ][ CSV [ QUOTE 'quote' ][ ESCAPE 'escape' ][ FORCE NOT NULL column [, ...] ] COPY table_name [ ( column [, ...] ) ] TO { 'filename' | STDOUT }[ [ DELIMITER 'delimiter' ][ NULL 'null string' ][ CSV [ QUOTE 'quote' ][ ESCAPE 'escape' ][ FORCE QUOTE column [, ...] ] ### CREATE AGGREGATE Defines a new aggregate function. CREATE AGGREGATE name ( BASETYPE = input_data_type, SFUNC = sfunc, STYPE = state_data_type [, FINALFUNC = ffunc ] [, INITCOND = initial_condition ]) ### CREATE CAST Defines a user-defined cast. CREATE CAST (source_type AS target_type) WITH FUNCTION func_name (arg_types)[ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (source_type AS target_type) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ] ### CREATE CONSTRAINT TRIGGER Defines a new constraint trigger. CREATE CONSTRAINT TRIGGER name AFTER events ON table_name constraint attributes FOR EACH ROW EXECUTE PROCEDURE func_name ( args ) ### CREATE CONVERSION Defines a new encoding conversion. CREATE CONVERSION name FOR source_encoding TO dest_encoding FROM func_name ### CREATE DATABASE Creates a new database. CREATE DATABASE name [ [ OWNER db_owner ] [ TEMPLATE template ] [ ENCODING encoding ] [ TABLESPACE tablespace ] ] ### CREATE DOMAIN Defines a new domain. CREATE DOMAIN name data_type [ constraint [ ... ] ] _constraint_ can be one of the following options: { NOT NULL | NULL | CHECK (expression) } ### CREATE FUNCTION Defines a new function. CREATE FUNCTION name ( [ arg_type [, ...] ] ) RETURNS ret_type { LANGUAGE lang_name | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | SECURITY INVOKER | SECURITY DEFINER | AS 'definition' | AS 'obj_file', 'link_symbol'} ...[ WITH ( attribute [, ...] ) ] ### CREATE GROUP Defines a new user group. CREATE GROUP name [ option [ ... ] ]Where option can be: SYSID gid | USER username [, ...] ### CREATE INDEX Defines a new index. CREATE INDEX name ON table ( { column | ( expression ) } [, ...] ) ### CREATE LANGUAGE Defines a new procedural language. CREATE LANGUAGE name HANDLER call_handler ### CREATE OPERATOR Defines a new operator. CREATE OPERATOR name ( PROCEDURE = func_name [, LEFTARG = left_type ] [, RIGHTARG = right_type ] [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] [, RESTRICT = res_proc ] [, JOIN = join_proc ] [, HASHES ] [, MERGES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]) ### CREATE OPERATOR CLASS Defines a new operator class. CREATE OPERATOR CLASS name FOR TYPE data_type USING index_method AS { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] | FUNCTION support_number func_name ( argument_type [, ...] ) | STORAGE storage_type } [, ... ] ### CREATE ROLE Defines a new database role. CREATE ROLE _name_ [ _option_ [ ... ] ]where `_option_` can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE ... ### CREATE RULE Defines a new rewrite rule. CREATE RULE name AS ON event TO table DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } ### CREATE SCHEMA Defines a new schema. CREATE SCHEMA schema_name [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ] ### CREATE SERVER Defines a new foreign server. CREATE SERVER _server_name_ [ TYPE '_server_type_' ] [ VERSION '_server_version_' ] FOREIGN DATA WRAPPER _fdw_name_ [ OPTIONS ( _option_ '_value_' [, ... ] ) ] ### CREATE SEQUENCE Defines a new sequence generator. CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT increment ][ MINVALUE minvalue | NO MINVALUE ][ MAXVALUE maxvalue | NO MAXVALUE ][ START start ] [ CYCLE ] ### CREATE TABLE Defines a new table. CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( { column_name data_type [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ])[ INHERITS ( parent_table [, ... ] ) ][ WITH OIDS | WITHOUT OIDS ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] _column_constraint_ can be one of the following options: { NOT NULL | NULL | UNIQUE | PRIMARY KEY | CHECK (expression) | REFERENCES ref_table [ ( ref_column ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] }[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] _table_constraint_ can be one of the following options: { UNIQUE ( column_name [, ... ] ) | PRIMARY KEY ( column_name [, ... ] ) | CHECK ( exp