PostgreSQL Syntax

PostgreSQL Syntax for beginners and professionals with examples database, table, create, select, insert, update, delete, join, function, index, clause, trigger, view, procedure etc.

<< Back to POSTGRESQL

next → ← prev

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:

ABORT [ WORK | TRANSACTION ]

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 LANGUAGE name RENAME TO new_name

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 TRIGGER name ON table RENAME TO new_name

ALTER TYPE command:

Syntax:

ALTER TYPE name OWNER TO new_owner

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:

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

BEGIN command:

Syntax:

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

CHECKPOINT command:

Syntax:

CHECKPOINT

CLOSE command:

Syntax:

CLOSE name

CLUSTER command:

Syntax:

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

COMMIT command:

Syntax:

COMMIT [ WORK | TRANSACTION ]

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:

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

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 USER name [ [ WITH ] option [ ... ] ]

CREATE VIEW command:

Syntax:

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DEALLOCATE command:

Syntax:

DEALLOCATE [ PREPARE ] plan_name

DECLARE command:

Syntax:

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

DELETE command:

Syntax:

DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE command:

Syntax:

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

DROP CAST command:

Syntax:

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

DROP CONVERSION command:

Syntax:

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE command:

Syntax:

DROP DATABASE name

DROP DOMAIN command:

Syntax:

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

DROP FUNCTION command:

Syntax:

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

DROP GROUP command:

Syntax:

DROP GROUP name

DROP INDEX command:

Syntax:

DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

DROP LANGUAGE command:

Syntax:

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

DROP OPERATOR command:

Syntax:

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

DROP OPERATOR CLASS command:

Syntax:

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

DROP RULE command:

Syntax:

DROP RULE name ON relation [CASCADE | RESTRICT ]

DROP SCHEMA command:

Syntax:

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE command:

Syntax:

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLE command:

Syntax:

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLESPACE command:

Syntax:

DROP TABLESPACE tablespace_name

DROP TRIGGER command:

Syntax:

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE command:

Syntax:

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

DROP USER command:

Syntax:

DROP USER name

DROP VIEW command:

Syntax:

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

END command:

Syntax:

END [ WORK | TRANSACTION ]

EXECUTE command:

Syntax:

EXECUTE plan_name [ (parameter [, ...] ) ]

EXPLAIN command:

Syntax:

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

FETCH command:

Syntax:

FETCH [ direction { FROM | IN } ] cursor_name

INSERT command:

Syntax:

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

LISTEN command:

Syntax:

LISTEN name

LOAD command:

Syntax:

LOAD 'filename'

LOCK command

Syntax:

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

MOVE command:

Syntax:

MOVE [ direction { FROM | IN } ] cursor_name

NOTIFY command:

Syntax:

NOTIFY name

PREPARE command:

Syntax:

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX command:

Syntax:

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

RESET command:

Syntax:

RESET name
RESET ALL

ROLLBACK command:

Syntax:

ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT command:

Syntax:

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT command:

Syntax:

SAVEPOINT savepoint_name

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 CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

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:

START TRANSACTION [ transaction_mode [, ...] ]

TRUNCATE TABLE command:

Syntax:

TRUNCATE [ TABLE ] name

UPDATE command:

Syntax:

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

Next Topicinstall PostgreSQL

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

PostgreSQL Syntax

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:

DROP TRIGGER command:

DROP TYPE command:

DROP USER command:

DROP VIEW command:

END command:

EXECUTE command:

EXPLAIN command:

FETCH command:

INSERT command:

LISTEN command: