PostgreSQL Syntax

by

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 ]