PostgreSQL Syntax

We can see the syntax of all postgreSQL commands by using help command. Follow the following steps to see the syntax of all commands in postgreSQL.

After installing postgreSQL, open the psql as: Program Files > PostgreSQL 9.2 > SQL Shell(psql)
Use the following command to see the syntax of a specific command. postgres-# \help &<command_name>

All PostgreSQL commands

Here, we are providing a list of all postgreSQL commands and their syntax:

ABORT command:

Syntax:

ALTER AGGREGATE command:

Syntax:

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

ALTER CONVERSION command:

Syntax:

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

ALTER DATABASE command:

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

ALTER DOMAIN command:

Syntax:

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:

Syntax:

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

ALTER GROUP command:

Syntax:

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

ALTER INDEX command:

Syntax:

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

ALTER LANGUAGE command:

Syntax:

ALTER OPERATOR command:

Syntax:

ALTER OPERATOR name ( { lefttype | NONE } , { righttype | NONE } )
OWNER TO new_owner

ALTER OPERATOR CLASS command:

Syntax:

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:

Syntax:

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

ALTER SEQUENCE command:

Syntax:

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

ALTER TABLE command:

Syntax:

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:

Syntax:

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER command:

Syntax:

ALTER TYPE command:

Syntax:

ALTER USER command:

Syntax:

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

ANALYSE command:

Syntax:

BEGIN command:

Syntax:

CHECKPOINT command:

Syntax:

CLOSE command:

Syntax:

CLUSTER command:

Syntax:

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

COMMIT command:

Syntax:

COPY command:

Syntax:

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:

Syntax:

CREATE AGGREGATE name (
BASETYPE = input_data_type,
SFUNC = sfunc,
STYPE = state_data_type
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ]
)

CREATE CAST command:

Syntax:

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:

Syntax:

CREATE CONSTRAINT TRIGGER name
AFTER events ON
table_name constraint attributes
FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

CREATE CONVERSION command:

Syntax:

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

CREATE DATABASE command:

Syntax:

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ TABLESPACE [=] tablespace ] ]

CREATE DOMAIN command:

Syntax:

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

CREATE FUNCTION command:

Syntax:

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:

Syntax:

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

CREATE INDEX command:

Syntax:

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

CREATE LANGUAGE command:

Syntax:

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

CREATE OPERATOR command:

Syntax:

 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:

Syntax:

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:

Syntax:

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CREATE SCHEMA command:

Syntax:

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

CREATE SEQUENCE command:

Syntax:

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:

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 tablespace ]

CREATE TABLE AS command:

Syntax:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

CREATE TABLESPACE command:

Syntax:

CRFEATE TRIGGER command:

Syntax:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )

CREATE TYPE command:

Syntax:

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:

Syntax:

CREATE VIEW command:

Syntax:

DEALLOCATE command:

Syntax:

DECLARE command:

Syntax:

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

DELETE command:

Syntax:

DROP AGGREGATE command:

Syntax:

DROP CAST command:

Syntax:

DROP CONVERSION command:

Syntax:

DROP DATABASE command:

Syntax:

DROP DOMAIN command:

Syntax:

DROP FUNCTION command:

Syntax:

DROP GROUP command:

Syntax:

DROP INDEX command:

Syntax:

DROP LANGUAGE command:

Syntax:

DROP OPERATOR command:

Syntax:

DROP OPERATOR name ( { left_type | NONE } , { right_type | NONE } )
[ CASCADE | RESTRICT ]

DROP OPERATOR CLASS command:

Syntax:

DROP RULE command:

Syntax:

DROP SCHEMA command:

Syntax:

DROP SEQUENCE command:

Syntax:

DROP TABLE command:

Syntax:

DROP TABLESPACE command:

Syntax:

DROP TRIGGER command:

Syntax:

DROP TYPE command:

Syntax:

DROP USER command:

Syntax:

DROP VIEW command:

Syntax:

END command:

Syntax:

EXECUTE command:

Syntax:

EXPLAIN command:

Syntax:

FETCH command:

Syntax:

INSERT command:

Syntax:

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

LISTEN command:

Syntax:

LOAD command:

Syntax:

LOCK command

Syntax:

MOVE command:

Syntax:

NOTIFY command:

Syntax:

PREPARE command:

Syntax:

REINDEX command:

Syntax:

RESET command:

Syntax:

RESET name
RESET ALL

ROLLBACK command:

Syntax:

ROLLBACK TO SAVEPOINT command:

Syntax:

SAVEPOINT command:

Syntax:

SELECT command:

Syntax:

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:

Syntax:

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:

Syntax:

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

SET CONSTRAINTS command:

Syntax:

SET TRANSACTION command:

Syntax:

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

SHOW command:

Syntax:

SHOW name
SHOW ALL

START TRANSACTION:

Syntax:

TRUNCATE TABLE command:

Syntax:

UPDATE command:

Syntax:

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

Next TopicPostgreSQL Datatypes

← prev next →

PostgreSQL Tutorial

PostgreSQL Database

PostgreSQL Table

PostgreSQL Schema

PostgreSQL Queries

PostgreSQL Clause

PostgreSQL Conditions

PostgreSQL Views

PostgreSQL Join

PostgreSQL Advance

Interview

Quiz

PostgreSQL Syntax

All PostgreSQL commands

ABORT command:

ALTER AGGREGATE command:

ALTER CONVERSION command:

ALTER DATABASE command:

ALTER DOMAIN command:

ALTER FUNCTION command:

ALTER GROUP command:

ALTER INDEX command:

ALTER LANGUAGE command:

ALTER OPERATOR command:

ALTER OPERATOR CLASS command:

ALTER SCHEMA command:

ALTER SEQUENCE command:

ALTER TABLE command:

ALTER TABLESPACE command:

ALTER TRIGGER command:

ALTER TYPE command:

ALTER USER command:

ANALYSE command:

BEGIN command:

CHECKPOINT command:

CLOSE command:

CLUSTER command:

COMMIT command:

COPY command:

CREATE AGGREGATE command:

CREATE CAST command:

CREATE CONSTRAINT TRIGGER command:

CREATE CONVERSION command:

CREATE DATABASE command:

CREATE DOMAIN command:

CREATE FUNCTION command:

CREATE GROUP command:

CREATE INDEX command:

CREATE LANGUAGE command:

CREATE OPERATOR command:

CREATE OPERATOR CLASS command:

CREATE RULE command:

CREATE SCHEMA command:

CREATE SEQUENCE command:

CREATE TABLE:

CREATE TABLE AS command:

CREATE TABLESPACE command:

CRFEATE TRIGGER command:

CREATE TYPE command:

CREATE USER command:

CREATE VIEW command:

DEALLOCATE command:

DECLARE command:

DELETE command:

DROP AGGREGATE command:

DROP CAST command:

DROP CONVERSION command:

DROP DATABASE command:

DROP DOMAIN command:

DROP FUNCTION command:

DROP GROUP command:

DROP INDEX command:

DROP LANGUAGE command:

DROP OPERATOR command:

DROP OPERATOR CLASS command:

DROP RULE command:

DROP SCHEMA command:

DROP SEQUENCE command:

DROP TABLE command:

DROP TABLESPACE command: