PostgreSQL Syntax
To check the syntax of all PostgreSQL commands, one can take the help of the HELP command by following the below steps.
After the successful installation of PostgreSQL, open the SQL.
Program Files > PostgreSQL 9.2 > SQL Shell(psql)
To see the syntax of a specific command.
Command: postgres-# \help &<command_name>
PostgreSQL commands:
A list of all PostgreSQL commands along with their syntax can be found below.
| COMMAND | SYNTAX |
| ABORT
command |
ABORT [ WORK | TRANSACTION ] |
| ALTER AGGREGATE
command |
ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner |
| ALTER CONVERSION
command |
ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner |
| ALTER DATABASE
command |
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 DOMAIN
command |
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
command |
ALTER FUNCTION name ( [ type [, …] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, …] ] ) OWNER TO new_owner |
| ALTER GROUP
command |
ALTER GROUP groupname ADD USER username [, … ]
ALTER GROUP groupname DROP USER username [, … ] ALTER GROUP groupname RENAME TO new_name |
| ALTER INDEX
command |
ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name ALTER INDEX name RENAME TO new_name |
| ALTER LANGUAGE
command |
ALTER LANGUAGE name RENAME TO new_name |
| ALTER OPERATOR
command |
ALTER OPERATOR name ( { lefttype | NONE } , { righttype | NONE } )
OWNER TO new_owner |
| ALTER OPERATOR CLASS
command |
ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner |
| ALTER SCHEMA
command |
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner |
| ALTER SEQUENCE
command |
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] |
| ALTER TABLE
command |
ALTER TABLE [ ONLY ] name [ * ]
action [, … ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name |
| ALTER
TABLESPACE command |
ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner |
| ALTER TRIGGER
command |
ALTER TRIGGER name ON table RENAME TO new_name |
| ALTER TYPE
command |
ALTER TYPE name OWNER TO new_owner |
| ALTER USER
command |
ALTER USER name [ [ WITH ] option [ … ] ]
ALTER USER name RENAME TO new_name ALTER USER name SET parameter { TO | = } { value | DEFAULT } ALTER USER name RESET parameter |
| ANALYZE
command |
ANALYZE [ VERBOSE ] [ table [ (column [, …] ) ] ] |
| BEGIN
command |
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, …] ] |
| CHECKPOINT command | CHECKPOINT |
| CLOSE
command |
CLOSE name |
| CLUSTER
command |
CLUSTER index_name ON table_name
CLUSTER table_name CLUSTER |
| COMMIT
command |
COMMIT [ WORK | TRANSACTION ] |
| COPY
command |
COPY table_name [ ( column [, …] ) ]
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 [ ( column [, …] ) ] TO { ‘filename’ | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] ‘delimiter’ ] [ NULL [ AS ] ‘null string’ ] [ CSV [ QUOTE [ AS ] ‘quote’ ] [ ESCAPE [ AS ] ‘escape’ ] [ FORCE QUOTE column [, …] ] |
| CREATE AGGREGATE
command |
CREATE AGGREGATE name (
BASETYPE = input_data_type, SFUNC = sfunc, STYPE = state_data_type [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] ) |
| CREATE CAST
command |
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
command |
CREATE CONSTRAINT TRIGGER name
AFTER events ON table_name constraint attributes FOR EACH ROW EXECUTE PROCEDURE func_name ( args ) |
| CREATE CONVERSION
command |
CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name |
| CREATE DATABASE
command |
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] ] |
| CREATE DOMAIN
command |
CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ] [ constraint [ … ] ] |
| CREATE FUNCTION
command |
CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, …] ] )
RETURNS ret_type { LANGUAGE lang_name | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AS ‘definition’ | AS ‘obj_file’, ‘link_symbol’ } … [ WITH ( attribute [, …] ) ] |
| CREATE GROUP
command |
CREATE GROUP name [ [ WITH ] option [ … ] ]
Where option can be: SYSID gid | USER username [, …] |
| CREATE INDEX
command |
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, …] ) [ TABLESPACE tablespace ] [ WHERE predicate ] |
| CREATE LANGUAGE
command |
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ] |
| CREATE OPERATOR
command |
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
command |
CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ] | FUNCTION support_number func_name ( argument_type [, …] ) | STORAGE storage_type } [, … ] |
| CREATE RULE
command |
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command … ) } |
| CREATE SCHEMA
command |
CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ … ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ … ] ] |
| CREATE SEQUENCE
command |
CREATE [ TEMPORARY | TEMP ] SEQUENCE name
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] |
| CREATE TABLE
command |
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 tablespace ] |
| CREATE TABLE AS
command |
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, …] ) ] [ [ WITH | WITHOUT ] OIDS ] AS query |
| CREATE TABLESPACE
command |
CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION ‘directory’ |
| CREATE TRIGGER
command |
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR … ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE func_name ( arguments ) |
| CREATE TYPE
command |
CREATE TYPE name AS
( attribute_name data_type [, … ] ) CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function [ , RECEIVE = receive_function ] [ , SEND = send_function ] [ , ANALYZE = analyze_function ] [ , INTERNALLENGTH = { internal_length | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] ) |
| CREATE USER
command |
CREATE USER name [ [ WITH ] option [ … ] ] |
| CREATE VIEW
command |
CREATE [ OR REPLACE ] VIEW name [ ( column_name [, …] ) ] AS query |
| DEALLOCATE
command |
DEALLOCATE [ PREPARE ] plan_name |
| DECLARE
command |
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query [ FOR { READ ONLY | UPDATE [ OF column [, …] ] } ] |
| DELETE
command |
DELETE FROM [ ONLY ] table [ WHERE condition ] |
| DROP AGGREGATE
command |
DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ] |
| DROP CAST
command |
DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ] |
| DROP CONVERSION
command |
DROP CONVERSION name [ CASCADE | RESTRICT ] |
| DROP DATABASE
command |
DROP DATABASE name |
| DROP DOMAIN
command |
DROP DOMAIN name [, …] [ CASCADE | RESTRICT ] |
| DROP FUNCTION
command |
DROP FUNCTION name ( [ type [, …] ] ) [ CASCADE | RESTRICT ] |
| DROP GROUP
command |
DROP GROUP name |
| DROP INDEX
command |
DROP INDEX name [, …] [ CASCADE | RESTRICT ] |
| DROP LANGUAGE
command |
DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ] |
| DROP OPERATOR
command |
DROP OPERATOR name ( { left_type | NONE } , { right_type | NONE } )
[ CASCADE | RESTRICT ] |
| DROP OPERATOR CLASS
command |
DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ] |
| DROP RULE
command |
DROP RULE name ON relation [CASCADE | RESTRICT ] |
| DROP SCHEMA
command |
DROP SCHEMA name [, …] [ CASCADE | RESTRICT ] |
| DROP SEQUENCE
command |
DROP SEQUENCE name [, …] [ CASCADE | RESTRICT ] |
| DROP TABLE
command |
DROP TABLE name [, …] [ CASCADE | RESTRICT ] |
| DROP TABLESPACE
command |
DROP TABLESPACE tablespace_name |
| DROP TRIGGER
command |
DROP TRIGGER name ON table [ CASCADE | RESTRICT ] |
| DROP TYPE
command |
DROP TYPE name [, …] [ CASCADE | RESTRICT ] |
| DROP USER
command |
DROP USER name |
| DROP VIEW
command |
DROP VIEW name [, …] [ CASCADE | RESTRICT ] |
| END
command |
END [ WORK | TRANSACTION ] |
| EXECUTE
command |
EXECUTE plan_name [ (parameter [, …] ) ] |
| EXPLAIN
command |
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement |
| FETCH
command |
FETCH [ direction { FROM | IN } ] cursor_name |
| INSERT
command |
INSERT INTO table [ ( column [, …] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, …] ) | query } |
| LISTEN
command |
LISTEN name |
| LOAD
command |
LOAD ‘filename’ |
| LOCK
command |
LOCK [ TABLE ] name [, …] [ IN lock_mode MODE ] [ NOWAIT ] |
| MOVE
command |
MOVE [ direction { FROM | IN } ] cursor_name |
| NOTIFY
command |
NOTIFY name |
| PREPARE
command |
PREPARE plan_name [ (data_type [, …] ) ] AS statement |
| REINDEX
command |
REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ] |
| RESET
command |
RESET name
RESET ALL |
| ROLLBACK
command |
ROLLBACK [ WORK | TRANSACTION ] |
| ROLLBACK TO SAVEPOINT
command |
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name |
| SAVEPOINT
command |
SAVEPOINT savepoint_name |
| SELECT
command |
SELECT [ ALL | DISTINCT [ ON ( expression [, …] ) ] ]
* | expression [ AS output_name ] [, …] [ FROM from_item [, …] ] [ WHERE condition ] [ GROUP BY expression [, …] ] [ HAVING condition [, …] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, …] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR UPDATE [ OF table_name [, …] ] ] |
| SELECT INTO
command |
SELECT [ ALL | DISTINCT [ ON ( expression [, …] ) ] ]
* | expression [ AS output_name ] [, …] INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table [ FROM from_item [, …] ] [ WHERE condition ] [ GROUP BY expression [, …] ] [ HAVING condition [, …] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, …] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR UPDATE [ OF table_name [, …] ] ] |
| SET
command |
SET [ SESSION | LOCAL ] name { TO | = } { value | ‘value’ | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT } |
| SET CONSTRAINTS
command |
SET CONSTRAINTS { ALL | name [, …] } { DEFERRED | IMMEDIATE } |
| SET TRANSACTION
command |
SET TRANSACTION transaction_mode [, …]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, …] |
| SHOW
command |
SHOW name
SHOW ALL |
| START TRANSACTION
command |
START TRANSACTION [ transaction_mode [, …] ] |
| TRUNCATE TABLE
command |
TRUNCATE [ TABLE ] name |
| UPDATE
command |
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, …]
[ FROM from_list ] [ WHERE condition ] |
