CodesJava

Easy learning with example program codes

Syntax of PostgreSQL

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.

 

COMMANDSYNTAX
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 commandCHECKPOINT
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 ]

 

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status