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 &&ltcommand_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 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:

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 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:

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:

START TRANSACTION:

Syntax:

TRUNCATE TABLE command:

Syntax:

UPDATE command:

Syntax:

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

Next Topicinstall PostgreSQL