SQL语法

  abort ::= ABORT [ TRANSACTION |  WORK ]  
  

  CREATE TABLE sample(k1 int,  k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));  
  

  BEGIN TRANSACTION; 
SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ;  
INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3,  'a'), (1, 3.0, 4, 'b');  
  

  BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ; 
INSERT INTO sample(k1, k2, v1, v2) VALUES (2, 2.0, 3,  'a'), (2, 3.0, 4, 'b');  
  

  SELECT * FROM sample; -- run in first shell

k1 | k2 | v1 |  v2

----+----+----+----

1 | 2 |   3 | a

1 | 3 |   4 | b

(2 rows)  
  

    SELECT * FROM sample; -- run in second shell

k1 | k2 | v1 |  v2

----+----+----+----

2 | 2 |   3 | a

2 | 3 |   4 | b

(2 rows)  
  

  COMMIT TRANSACTION; -- run in first shell.  
  

  ABORT TRANSACTION; -- run second shell.  
  

  SELECT * FROM sample; -- run in first shell.

k1 | k2 | v1 |  v2

----+----+----+----

1 | 2 |   3 | a

1 | 3 |   4 | b

(2 rows)
  

  SELECT * FROM sample; -- run in second shell.

k1 | k2 | v1 |  v2

----+----+----+----

1 | 2 |   3 | a

1 | 3 |   4 | b

(2 rows)  
  

   alter_database ::= ALTER DATABASE name
                     [ [ WITH ] alter_database_option [ ... ]
                       | RENAME TO name
                       | OWNER TO { new_owner
 	                              |  CURRENT_USER
 	                              |  SESSION_USER }
                        SET TABLESPACE new_tablespace
                       | SET configuration_parameter { TO | = }
                          { value | DEFAULT }
                       | SET configuration_parameter FROM CURRENT
                       | RESET configuration_parameter
                       | RESET ALL ]
 		
alter_database_option ::= ALLOW_CONNECTIONS allowconn
                             | CONNECTION LIMIT  connlimit
                             | IS_TEMPLATE  istemplate     
  

  alter_default_priv ::= ALTER DEFAULT PRIVILEGES
                           [ FOR { ROLE | USER } role_name [ , ... ] ]
                           [ IN SCHEMA schema_name [ , ... ] ]
                           abbr_grant_or_revoke
														
abbr_grant_or_revoke ::= a_grant_table
                              | a_grant_seq
                              | a_grant_func
                              | a_grant_type
                              | a_grant_schema
                              | a_revoke_table
                              | a_revoke_seq
                              | a_revoke_func
                              | a_revoke_type
                              | a_revoke_schema
													
a_grant_table ::= GRANT { grant_table_priv [ , ... ]
                            | ALL [ PRIVILEGES  ] } ON TABLES TO
                    grant_role_spec [ , ... ] [ WITH GRANT  OPTION ]
									
a_grant_seq ::= GRANT { grant_seq_priv [ , ... ]
                          | ALL [ PRIVILEGES ] } ON SEQUENCES TO
                  grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
												
a_grant_func ::= GRANT { EXECUTE | ALL [ PRIVILEGES ] }  ON
                   { FUNCTIONS | ROUTINES } TO grant_role_spec [ , ... ]
                   [ WITH GRANT OPTION ]
										
a_grant_type ::= GRANT { USAGE | ALL [ PRIVILEGES ] }  ON TYPES TO
                   grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
										
a_grant_schema ::= GRANT { USAGE | CREATE | ALL [  PRIVILEGES ] } ON
                     SCHEMAS TO grant_role_spec [ , ... ]
                     [ WITH GRANT OPTION ]
										
a_revoke_table ::= REVOKE [ GRANT OPTION FOR ]
                      { grant_table_priv [ , ... ] | ALL [ PRIVILEGES ] }
                      ON TABLES FROM grant_role_spec [ , ... ]
                      [ CASCADE | RESTRICT ]
											
a_revoke_seq ::= REVOKE [ GRANT OPTION FOR ]
                    { grant_seq_priv [ , ... ] | ALL  [ PRIVILEGES ] } ON
                    SEQUENCES FROM grant_role_spec [ , ... ]
                    [ CASCADE | RESTRICT ]
									
a_revoke_func ::= REVOKE [ GRANT OPTION FOR ]
                    { EXECUTE | ALL [ PRIVILEGES ] } ON
                    { FUNCTIONS | ROUTINES } FROM grant_role_spec
                    [ , ... ] [ CASCADE | RESTRICT ]
										
a_revoke_type ::= REVOKE [ GRANT OPTION FOR ]
                    { USAGE | ALL [ PRIVILEGES ] } ON TYPES FROM
                    grant_role_spec [ , ... ] [ CASCADE |  RESTRICT ]
										
a_revoke_schema ::= REVOKE [ GRANT OPTION FOR ]
                    { USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS
                    FROM grant_role_spec [ , ... ]
                    [ CASCADE | RESTRICT ]
											
grant_table_priv ::= SELECT
                       | INSERT
                       | UPDATE
                       | DELETE
                       | TRUNCATE
                       | REFERENCES
                       | TRIGGER
												
grant_seq_priv ::= USAGE | SELECT | UPDATE

grant_role_spec ::= [ GROUP ] role_name
                       | PUBLIC
                       | CURRENT_USER
                       | SESSION_USER  
  

    ALTER DEFAULT PRIVILEGES IN  SCHEMA marketing GRANT SELECT ON TABLES TO PUBLIC;  
  

    ALTER DEFAULT PRIVILEGES  REVOKE INSERT ON TABLES FROM john; 
  

  alter_domain_default ::= ALTER DOMAIN name
                          { SET DEFAULT expression | DROP DEFAULT }													

alter_domain_rename ::= ALTER DOMAIN name RENAME TO  name  
  

  CREATE DOMAIN idx DEFAULT 5 CHECK (VALUE > 0);

ALTER DOMAIN idx DROP DEFAULT;

ALTER DOMAIN idx RENAME TO idx_new;

DROP DOMAIN idx_new;  
  

  alter_group ::= ALTER GROUP role_specification { ADD |  DROP } USER
                  role_name [ , ... ]
										
role_specification ::= role_name | CURRENT_USER |  SESSION_USER

alter_group_rename ::= ALTER GROUP role_name RENAME TO  new_role_name  
  

  alter_policy ::= ALTER POLICY name ON table_name
                    [ TO { role_name
                            | PUBLIC
                            | CURRENT_USER
                            | SESSION_USER } [ , ... ] ]
                    [ USING ( using_expression ) ]
                    [ WITH CHECK ( check_expression ) ]
															
alter_policy_rename ::= ALTER POLICY name ON table_name  RENAME TO
                          new_name  
  

    ALTER POLICY p1 ON table_foo RENAME TO p2;  
  

   ALTER POLICY p1 ON table_foo TO PUBLIC; 
  

  alter_role ::= ALTER ROLE role_specification
                  [ [ WITH ] alter_role_option [  , ... ] ]
									
alter_role_option ::= SUPERUSER
                         | NOSUPERUSER
                         | CREATEDB
                         | NOCREATEDB
                         | CREATEROLE
                         | NOCREATEROLE
                         | INHERIT
                         | NOINHERIT
                         | LOGIN
                         | NOLOGIN
                         | CONNECTION LIMIT connlimit
                         | [ ENCRYPTED ]  PASSWORD ' password '
                         | PASSWORD NULL
                         | VALID UNTIL ' timestamp '
										
role_specification ::= role_name | CURRENT_USER |  SESSION_USER

alter_role_rename ::= ALTER ROLE role_name RENAME TO  new_role_name

alter_role_config ::= ALTER ROLE { role_specification |  ALL }
                         [ IN DATABASE database_name ] config_setting
												
config_setting ::= SET config_param { TO | = }
                         { config_value | DEFAULT }
                         | SET config_param FROM CURRENT
                         | RESET config_param
                         | RESET ALL  
  

  ALTER ROLE John WITH  PASSWORD 'new_password'; 
  

  ALTER ROLE John RENAME TO  Jane;  
  

  ALTER ROLE Jane SET  default_transaction_isolation='serializable';  
  

  alter_sequence ::= ALTER SEQUENCE [ IF EXISTS ] name
                      alter_sequence_options
											
name ::= '<Text Literal>'

alter_sequence_options ::= [ AS seq_data_type ]
                                 [ INCREMENT [ BY ]  increment ]
                                 [ MINVALUE  minvalue | NO MINVALUE ]
                                 [ MAXVALUE  maxvalue | NO MAXVALUE ]
                                 [ START [ WITH ]  start ]
                                 [ RESTART [ [ WITH  ] restart ] ]
                                 [ CACHE cache ]
                                 [ OWNED BY  table_name.table_column | NONE ]  
  

  alter_table ::= ALTER TABLE [ ONLY ] name [ * ]  alter_table_action
                   [  , ... ]
											
alter_table_action ::= ADD [ COLUMN ] column_name  data_type
                           [ alter_column_constraint [ ... ] ]
                           | RENAME TO table_name
                           | DROP [ COLUMN ] column_name
                             [ RESTRICT | CASCADE ]
                           | ADD  alter_table_constraint
                           | DROP CONSTRAINT constraint_name
                             [ RESTRICT | CASCADE ]
                           | RENAME [ COLUMN ] column_name TO column_name
                           | DISABLE ROW LEVEL SECURITY
                           | ENABLE ROW LEVEL SECURITY
                           | FORCE ROW LEVEL SECURITY
                           | NO FORCE ROW LEVEL SECURITY
														
alter_table_constraint ::= [ CONSTRAINT constraint_name  ]
                                 { CHECK (  expression )
                                   | UNIQUE (  column_names )
                                     index_parameters
                                   | FOREIGN KEY (  column_names )
                                     references_clause  }
                                 [ DEFERRABLE | NOT  DEFERRABLE ]
                                 [ INITIALLY  DEFERRED
                                   | INITIALLY  IMMEDIATE ]
																	
alter_column_constraint ::= [ CONSTRAINT  constraint_name ]
                                 { NOT NULL
                                   | NULL
                                   | CHECK (  expression )
                                   | DEFAULT  expression
                                   | UNIQUE  index_parameters
                                   |  references_clause }
                                [ DEFERRABLE |  NOT DEFERRABLE ]
                                [ INITIALLY  DEFERRED
                                   | INITIALLY  IMMEDIATE ]  
  

  alter_user ::= ALTER USER role_specification
                  [  [ WITH ] alter_role_option [ , ... ] ]
                  
alter_role_option ::= SUPERUSER
                         | NOSUPERUSER
                         | CREATEDB
                         | NOCREATEDB
                         | CREATEROLE
                         | NOCREATEROLE
                         | INHERIT
                         | NOINHERIT
                         | LOGIN
                         | NOLOGIN
                         | CONNECTION LIMIT connlimit
                         | [ ENCRYPTED ] PASSWORD '  password '
                         | PASSWORD NULL
                         | VALID UNTIL ' timestamp '
                         
role_specification ::= role_name | CURRENT_USER |  SESSION_USER

alter_user_rename ::= ALTER USER role_name RENAME TO  new_role_name

alter_user_config ::= ALTER USER { role_specification |  ALL }
                         [ IN DATABASE database_name ] config_setting
                         
config_setting ::= SET config_param { TO | = }
                       { config_value | DEFAULT }
                       | SET config_param FROM CURRENT
                       | RESET config_param
                       | RESET ALL  
  

  BEGIN [ TRANSACTION | WORK ]  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));  
  

  BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ;   INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3,  'a'), (1, 3.0, 4, 'b');  
  

  BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL  REPEATABLE READ;   INSERT INTO sample(k1, k2, v1, v2) VALUES (2, 2.0, 3,  'a'), (2, 3.0, 4, 'b');  
  

  SELECT * FROM sample; -- run in first shell
k1 | k2 | v1 |  v2
----+----+----+----
1 | 2 |   3 | a
1 | 3 |   4 | b
(2 rows)

SELECT * FROM sample; -- run in second shell
k1 | k2 | v1 |  v2
----+----+----+----
2 | 2 |   3 | a
2 | 3 |   4 | b
(2 rows) 
  

  COMMIT TRANSACTION; -- run  in first shell.  
  

  ABORT TRANSACTION; -- run  second shell.  
  

  SELECT * FROM sample; -- run in first shell.
k1 | k2 | v1 |  v2
----+----+----+----
1 | 2 |   3 | a
1 | 3 |   4 | b
(2 rows)
  

  comment_on ::= COMMENT ON
                {  ACCESS METHOD object_name
                   | AGGREGATE aggregate_name ( aggregate_signature )
                   | CAST ( source_type AS target_type )
                   | COLLATION object_name
                   | COLUMN relation_name . column_name
                   | CONSTRAINT constraint_name ON table_name
                   | CONSTRAINT constraint_name ON DOMAIN domain_name
                   | CONVERSION object_name
                   | DATABASE object_name
                   | DOMAIN object_name
                   | EXTENSION object_name
                   | EVENT TRIGGER object_name
                   | FOREIGN DATA WRAPPER object_name
                   | FOREIGN TABLE object_name
                   | FUNCTION function_name [ ( function_signature ) ]
                   | INDEX object_name
                   | LARGE OBJECT large_object_oid
                   | OPERATOR operator_name ( operator_signature )
                   | OPERATOR CLASS object_name USING index_method
                   | OPERATOR FAMILY object_name USING index_method
                   | POLICY policy_name ON table_name
                   | [ PROCEDURAL ] LANGUAGE object_name
                   | PROCEDURE procedure_name
                     [ ( [ [ argmode ] [ argname ] argtype [ , ... ] ] ) ]
                   | PUBLICATION object_name
                   | ROLE object_name
                   | ROUTINE routine_name
                     [ ( [ [ argmode ] [ argname ] argtype [ , ... ] ] ) ]
                   | RULE rule_name ON table_name
                   | SCHEMA object_name
                   | SEQUENCE object_name
                   | SERVER object_name
                   | STATISTICS object_name
                   | SUBSCRIPTION object_name
                   | TABLE object_name
                   | TABLESPACE object_name
                   | TEXT SEARCH CONFIGURATION object_name
                   | TEXT SEARCH DICTIONARY object_name
                   | TEXT SEARCH PARSER object_name
                   | TEXT SEARCH TEMPLATE object_name
                   | TRANSFORM FOR type_name  LANGUAGE lang_name
                   | TRIGGER trigger_name ON table_name
                   | TYPE object_name
                   | VIEW object_name } IS { '<Text Literal>' | NULL } 
  

  COMMENT ON DATABASE postgres IS 'Default database';

COMMENT ON INDEX index_name IS 'Special index';  
  

   COMMENT ON TABLE some_table IS NULL;  
  

  commit ::= COMMIT [ TRANSACTION  | WORK ] 
  

  COMMIT [ TRANSACTION | WORK  ]  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2)); 
  

  BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3,  'a'), (1, 3.0, 4, 'b');
  

  BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO sample(k1, k2, v1, v2) VALUES (2, 2.0, 3,  'a'), (2, 3.0, 4, 'b');
  

  SELECT * FROM sample; -- run in first shell
k1 | k2 | v1 |  v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)
  

  SELECT * FROM sample; -- run in second shell
k1 | k2 | v1 |  v2
----+----+----+----
2 | 2 | 3 | a
2 | 3 | 4 | b
(2 rows)  
  

  COMMIT TRANSACTION; -- run in first shell.
  

  ABORT TRANSACTION; -- run second shell. 
  

  SELECT * FROM sample; -- run in first shell.
k1 | k2 | v1 |  v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows) 
  

  SELECT * FROM sample; -- run in second shell.
k1 | k2 | v1 |  v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)
  

  copy_from ::= COPY table_name [ ( column_name [ , ... ]  ) ] FROM
                {  'filename' | PROGRAM 'command' | STDIN }
                [ [  WITH ] ( option [ , ... ] ) ]
                
copy_to ::= COPY { table_name [ ( column_names ) ] | (  query ) } TO
                {  'filename' | PROGRAM 'command' | STDOUT }
                [ [  WITH ] ( option [ , ... ] ) ]
                
copy_option ::= FORMAT format_name
                  |  OIDS [ boolean ]
                  |  FREEZE [ boolean ]
                  |  DELIMITER 'delimiter_character'
                  |  NULL 'null_string'
                  |  HEADER [ boolean ]
                  |  QUOTE 'quote_character'
                  |  ESCAPE 'escape_character'
                  |  FORCE_QUOTE { ( column_names ) | * }
                  |  FORCE_NOT_NULL ( column_names )
                  |  FORCE_NULL ( column_names )
                  |  ENCODING 'encoding_name'
  

  create_aggregate ::= create_aggregate_normal
                        | create_aggregate_order_by
                        | create_aggregate_old
                        
create_aggregate_normal ::= CREATE AGGREGATE  aggregate_name (
                                { aggregate_arg [  , ... ] | * } ) ( SFUNC
                                = sfunc , STYPE =  state_data_type
                                [ ,  aggregate_normal_option [ ... ] ] )
                                
create_aggregate_order_by ::= CREATE AGGREGATE  aggregate_name (
                                [ aggregate_arg  [ , ... ] ] ORDER BY
                                aggregate_arg [  , ... ] ) ( SFUNC =
                                sfunc , STYPE =  state_data_type
                                [ ,  aggregate_order_by_option [ ... ] ]
                                )
                                
create_aggregate_old ::= CREATE AGGREGATE  aggregate_name ( BASETYPE =
                            base_type , SFUNC = sfunc , STYPE =
                            state_data_type
                            [ , aggregate_old_option [ ... ] ] )
                            

aggregate_arg ::= [ aggregate_argmode ] [ argname ]  argtype

aggregate_normal_option ::= SSPACE = state_data_size
                               | FINALFUNC =  ffunc
                               | FINALFUNC_EXTRA
                               |  FINALFUNC_MODIFY =
                                  { READ_ONLY |  SHAREABLE | READ_WRITE }
                               | COMBINEFUNC =  combinefunc
                               | SERIALFUNC =  serialfunc
                               | DESERIALFUNC =  deserialfunc
                               | INITCOND =  initial_condition
                               | MSFUNC = msfunc
                               | MINVFUNC = minvfunc
                               | MSTYPE =  mstate_data_type
                               | MSSPACE =  mstate_data_size
                               | MFINALFUNC =  mffunc
                               |  MFINALFUNC_EXTRA
                               | MFINALFUNC_MODIFY =
                                  { READ_ONLY |  SHAREABLE | READ_WRITE }
                               | MINITCOND =  minitial_condition
                               | SORTOP =  sort_operator
                               | PARALLEL =
                                  { SAFE |  RESTRICTED | UNSAFE }
                                  
aggregate_order_by_option ::= SSPACE = state_data_size
                                 | FINALFUNC =  ffunc
                                 |  FINALFUNC_EXTRA
                                 |  FINALFUNC_MODIFY =
                                    { READ_ONLY |  SHAREABLE | READ_WRITE }
                                 | INITCOND =  initial_condition
                                 | PARALLEL =
                                    { SAFE | RESTRICTED | UNSAFE }
                                 | HYPOTHETICAL
                                 
aggregate_old_option ::= SSPACE = state_data_size
                           | FINALFUNC = ffunc
                           | FINALFUNC_EXTRA
                           | FINALFUNC_MODIFY =
                              { READ_ONLY |  SHAREABLE | READ_WRITE }
                           | COMBINEFUNC = combinefunc
                           | SERIALFUNC = serialfunc
                           | DESERIALFUNC = deserialfunc
                           | INITCOND =  initial_condition
                           | MSFUNC = msfunc
                           | MINVFUNC = minvfunc
                           | MSTYPE = mstate_data_type
                           | MSSPACE = mstate_data_size
                           | MFINALFUNC = mffunc
                           | MFINALFUNC_EXTRA
                           | MFINALFUNC_MODIFY =
                              { READ_ONLY |  SHAREABLE | READ_WRITE }
                           | MINITCOND = minitial_condition
                           | SORTOP = sort_operator  
  

  CREATE AGGREGATE sumdouble (float8) (
           STYPE = float8,
           SFUNC = float8pl,
           MSTYPE = float8,
           MSFUNC = float8pl,
           MINVFUNC = float8mi
           );
           
CREATE TABLE normal_table(
          f  float8,
          i  int
        );
        
INSERT INTO normal_table(f, i) VALUES
          (0.1, 9),
          (0.9, 1);
          
SELECT sumdouble(f), sumdouble(i) FROM normal_table;  
  

  CREATE AGGREGATE my_percentile_disc(float8 ORDER BY  anyelement) (
           STYPE = internal,
           SFUNC = ordered_set_transition,
           FINALFUNC = percentile_disc_final,
           FINALFUNC_EXTRA = true,
           FINALFUNC_MODIFY = read_write
        );
        
SELECT my_percentile_disc(0.1), my_percentile_disc(0.9)
           WITHIN GROUP (ORDER BY typlen)
           FROM  pg_type;  
  

  CREATE AGGREGATE oldcnt(
          SFUNC = int8inc,
          BASETYPE = 'ANY',
          STYPE = int8,
          INITCOND = '0'
       );
       
SELECT oldcnt(*) FROM pg_aggregate; 
  

  CREATE AGGREGATE newcnt(*) (
          SFUNC = int8inc,
          STYPE = int8,
          INITCOND = '0',
          PARALLEL = SAFE
       );
       
SELECT newcnt(*) FROM pg_aggregate;
  

  create_cast ::= create_cast_with_function
                    |  create_cast_without_function
                    |  create_cast_with_inout
                    
create_cast_with_function ::= CREATE CAST (  cast_signature ) WITH
                                  FUNCTION function_name
                                  [ (  function_signature ) ]
                                  [ AS ASSIGNMENT  | AS IMPLICIT ]
                                  
create_cast_without_function ::= CREATE CAST (  cast_signature ) WITHOUT  FUNCTION
                                   [ AS  ASSIGNMENT | AS IMPLICIT ]
                                   
create_cast_with_inout ::= CREATE CAST ( cast_signature  ) WITH INOUT
                              [ AS ASSIGNMENT |  AS IMPLICIT ]
                              
cast_signature ::= source_type AS target_type
  

  CREATE FUNCTION sql_to_date(integer) RETURNS date AS $$
          SELECT $1::text::date
          $$  LANGUAGE SQL IMMUTABLE STRICT;
          
CREATE CAST (integer AS date) WITH FUNCTION  sql_to_date(integer) AS ASSIGNMENT;  SELECT CAST (3 AS date);
  

  CREATE TYPE myfloat4;

CREATE FUNCTION myfloat4_in(cstring) RETURNS myfloat4
           LANGUAGE internal IMMUTABLE STRICT  PARALLEL SAFE AS 'float4in';
           
CREATE FUNCTION myfloat4_out(myfloat4) RETURNS cstring
           LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'float4out';
           
CREATE TYPE myfloat4 (
           INPUT = myfloat4_in,
           OUTPUT = myfloat4_out,
           LIKE  = float4
        );
        
SELECT CAST('3.14'::myfloat4 AS float4);

CREATE CAST (myfloat4 AS float4) WITHOUT FUNCTION;

SELECT CAST('3.14'::myfloat4 AS float4);  
  

  CREATE TYPE myint4;

CREATE FUNCTION myint4_in(cstring) RETURNS myint4
           LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int4in';
           
CREATE FUNCTION myint4_out(myint4) RETURNS cstring
           LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int4out';
           
CREATE TYPE myint4 (
           INPUT = myint4_in,
           OUTPUT = myint4_out,
           LIKE  = int4
        );
        
SELECT CAST('2'::myint4 AS int4);

CREATE CAST (myint4 AS int4) WITH INOUT;

SELECT CAST('2'::myint4 AS int4);
  

  create_database ::= CREATE DATABASE name [  create_database_options ]

create_database_options ::= [ WITH ] [ OWNER [ = ]  user_name ]
                                [ TEMPLATE [ = ]  template ]
                                [ ENCODING [ = ]  encoding ]
                                [ LC_COLLATE [ =  ] lc_collate ]
                                [ LC_CTYPE [ = ]  lc_ctype ]
                                [ TABLESPACE [ = ]  tablespace_name ]
                                [  ALLOW_CONNECTIONS [ = ] allowconn ]
                                [ COLOCATED [ = ]  { 'true' | 'false' } ] 
  

  CREATE DATABASE company WITH  COLOCATED = true;  
  

  create_domain ::= CREATE DOMAIN name [ AS ] data_type
                    [ DEFAULT expression ]
                    [ [ domain_constraint [ ... ] ] ]
                    
domain_constraint ::= [ CONSTRAINT constraint_name ]
                          { NOT NULL | NULL | CHECK ( expression ) } 
  

  CREATE DOMAIN phone_number AS TEXT CHECK(VALUE ~ '^\d{3}-\d{3}-\d{4}$');
  

  CREATE TABLE person(first_name TEXT, last_name TEXT, phone_number phone_number);  
  

  create_extension ::= CREATE EXTENSION [ IF NOT EXISTS ]  extension_name
                       [ WITH ] [ SCHEMA schema_name ]
                       [ VERSION version ] [ CASCADE ]  
  

  CREATE SCHEMA myschema;
  

  CREATE EXTENSION pgcrypto WITH SCHEMA myschema VERSION  '1.3';
  

  CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;
  

  create_function ::= CREATE [ OR REPLACE ] FUNCTION name  (
                      [ arg_decl [ , ... ] ] )
                      [ RETURNS type_name
                         | RETURNS TABLE ( { column_name type_name }
                            [ , ... ] ) ]
                      { LANGUAGE lang_name
                         | TRANSFORM { FOR TYPE type_name } [ , ... ]
                         | WINDOW
                         | IMMUTABLE
                         | STABLE
                         | VOLATILE
                         | [ NOT ] LEAKPROOF
                         | CALLED ON NULL INPUT
                         | RETURNS NULL ON NULL INPUT
                         | STRICT
                         | [ EXTERNAL ] SECURITY INVOKER
                         | [ EXTERNAL ] SECURITY DEFINER
                         | PARALLEL { UNSAFE |  RESTRICTED | SAFE }
                         | COST int_literal
                         | ROWS int_literal
                         | SET configuration_parameter
                            { TO value | = value | FROM CURRENT }
                         | AS 'definition'
                         | AS 'obj_file' 'link_symbol' } [ ... ]

arg_decl ::= [ argmode ] [ argname ] argtype
                [ {  DEFAULT | = } expression ] 
  

  CREATE FUNCTION mul(integer, integer) RETURNS integer
    AS 'SELECT $1  * $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

SELECT mul(2,3), mul(10, 12);
  

  mul | mul
-----+-----
    6 | 120
(1 row)  
  

  CREATE OR REPLACE FUNCTION inc(i integer) RETURNS  integer AS $$
        BEGIN
               RAISE NOTICE 'Incrementing %', i ;
               RETURN i + 1;
        END;

$$ LANGUAGE plpgsql;

SELECT inc(2), inc(5), inc(10);  
  

  NOTICE:   Incrementing 2
NOTICE:   Incrementing 5
NOTICE:   Incrementing 10
inc | inc | inc
-----+-----+-----
3 |  6 |   11
(1 row)  
  

  create_group  ::= CREATE GROUP role_name
                    [ [ WITH ] role_option [ ,  ... ] ]

role_option  ::= SUPERUSER
                   | NOSUPERUSER
                   | CREATEDB
                   | NOCREATEDB
                   | CREATEROLE
                   | NOCREATEROLE
                   | INHERIT
                   | NOINHERIT
                   | LOGIN
                   | NOLOGIN
                   | CONNECTION LIMIT connlimit
                   | [ ENCRYPTED ] PASSWORD ' password '
                   | PASSWORD NULL
                   | VALID UNTIL ' timestamp '
                   | IN ROLE role_name [ , ... ]
                   | IN GROUP role_name [ , ...  ]
                   | ROLE role_name [ , ... ]
                   | ADMIN role_name [ , ... ]
                   | USER role_name [ , ... ]
                   | SYSID uid  
  

  CREATE GROUP SysAdmin WITH  CREATEDB CREATEROLE;
  

  create_index ::= CREATE [ UNIQUE ] INDEX [ [ IF NOT  EXISTS ] name ]
                   ON [ ONLY ] table_name ( index_elem [ , ... ] )
                   [ INCLUDE ( column_name [ , ... ] ) ]
                   [ WHERE predicate ]

index_elem ::= { column_name | ( expression ) }
                  [  operator_class_name ] [ HASH | ASC | DESC ]
                  [  NULLS { FIRST | LAST } ] 
  

  CREATE TABLE products(id int PRIMARY KEY,
                               name text,
                               code text);

CREATE UNIQUE INDEX ON products(code);
  

  \d products
             Table "public.products"
Column | Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id   | integer |      | not null |
name  | text    |      |     |
code  | text    |      |     |

Indexes:
    "products_pkey" PRIMARY KEY, lsm (id HASH)
    "products_code_idx" UNIQUE, lsm (code HASH)  
  

  CREATE INDEX products_name ON products(name ASC);
\d products_name
    Index "public.products_name"
Column | Type |  Key? | Definition
--------+------+------+------------
name  | text | yes | name
lsm, for table "public.products
  

  CREATE INDEX products_name_code ON products(name)  INCLUDE (code);
\d products_name_code;
 Index  "public.products_name_code"
 Column | Type |  Key? | Definition
--------+------+------+------------
name  | text | yes | name
code  | text | no  | code
lsm, for table "public.products"
  

  CREATE TABLE employees (id int PRIMARY KEY, first_name  TEXT, last_name TEXT) SPLIT INTO 10 TABLETS;

CREATE INDEX ON employees(first_name, last_name) SPLIT  INTO 10 TABLETS;
  

  create table shipments(id int, delivery_status text,  address text, delivery_date date);

create index shipment_delivery on  shipments(delivery_status, address, delivery_date) where delivery_status !=  'delivered'; 
  

  create_operator ::= CREATE OPERATOR operator_name (
                       { FUNCTION = function_name
                          | PROCEDURE = procedure_name }
                       [ , operator_option [ ... ] ] )

operator_option ::= LEFTARG = left_type
                       | RIGHTARG = right_type
                       | COMMUTATOR = com_op
                       | NEGATOR = neg_op
                       | RESTRICT = res_proc
                       | JOIN = join_proc
                       | HASHES
                       | MERGES
  

  CREATE OPERATOR @#@ (
           rightarg = int8,
           procedure = numeric_fac
        );

SELECT @#@ 5;  
  

  reate_operator_class ::= CREATE OPERATOR CLASS  operator_class_name
                             [ DEFAULT ] FOR  TYPE data_type USING
                             index_method AS  operator_class_as [ , ... ]

operator_class_as ::= OPERATOR strategy_number  operator_name
                         [ ( operator_signature ) ] [ FOR SEARCH ]
                         | FUNCTION support_number
                            [ ( op_type [ , ... ] ) ] function_name (
                            function_signature )
                         | STORAGE storage_type
  

  CREATE OPERATOR CLASS my_op_class
         FOR  TYPE int4
         USING  btree AS
         OPERATOR 1 <,
         OPERATOR 2 <=;
  

  create_policy ::= CREATE POLICY name ON table_name
                     [ AS { PERMISSIVE | RESTRICTIVE } ]
                     [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
                     [ TO { role_name
                             | PUBLIC
                             | CURRENT_USER
                             | SESSION_USER } [ , ... ] ]
                     [ USING ( using_expression ) ]
                     [ WITH CHECK ( check_expression ) ]  
  

  CREATE POLICY p1 ON document
  USING (dlevel  <= (SELECT level FROM user_account WHERE user = current_user)); 
  

  CREATE POLICY p_restrictive ON document AS RESTRICTIVE  TO user_bob    USING (cid  <> 44); 
  

  CREATE POLICY p2 ON document FOR INSERT WITH CHECK  (dauthor = current_user);  
  

  create_procedure ::= CREATE [ OR REPLACE ] PROCEDURE  name (
                       [ arg_decl [ , ... ] ] )
                       { LANGUAGE lang_name
                          | TRANSFORM { FOR TYPE type_name } [ , ... ]
                          | [ EXTERNAL ] SECURITY INVOKER
                          | [ EXTERNAL ] SECURITY DEFINER
                          | SET configuration_parameter
                             { TO value | = value | FROM  CURRENT }
                          | AS 'definition'
                          | AS 'obj_file' 'link_symbol' } [ ... ]

arg_decl ::= [ argmode ] [ argname ] argtype
                 [ {  DEFAULT | = } expression ]  
  

  CREATE TABLE accounts (
   id integer  PRIMARY KEY,
   name text NOT  NULL,
   balance  decimal(15,2) NOT NULL
);

INSERT INTO accounts VALUES (1, 'Jane', 100.00);
INSERT INTO accounts VALUES (2, 'John', 50.00);

SELECT * from accounts; 
  

  CREATE OR REPLACE PROCEDURE transfer(integer, integer,  decimal)
LANGUAGE plpgsql
AS $$
BEGIN
   IF $3 <=  0.00 then RAISE EXCEPTION 'Can only transfer positive amounts'; END IF;
   IF $1 = $2 then  RAISE EXCEPTION 'Sender and receiver cannot be the same'; END IF;
   UPDATE accounts  SET balance = balance - $3 WHERE id = $1;
   UPDATE accounts  SET balance = balance + $3 WHERE id = $2;
   COMMIT;
END;
$$;
  

  CALL transfer(1, 2, 20.00);
SELECT * from accounts;
  

  create_role ::= CREATE ROLE role_name
                  [ [ WITH ] role_option [ , ... ] ]

role_option ::= SUPERUSER
                  | NOSUPERUSER
                  | CREATEDB
                  | NOCREATEDB
                  | CREATEROLE
                  | NOCREATEROLE
                  | INHERIT
                  | NOINHERIT
                  | LOGIN
                  | NOLOGIN
                  | CONNECTION LIMIT connlimit
                  | [ ENCRYPTED ] PASSWORD ' password '
                  | PASSWORD NULL
                  | VALID UNTIL ' timestamp '
                  | IN ROLE role_name [ , ... ]
                  | IN GROUP role_name [ , ... ]
                  | ROLE role_name [ , ... ]
                  | ADMIN role_name [ , ... ]
                  | USER role_name [ , ... ]
                  | SYSID uid 
  

  CREATE ROLE John LOGIN;
  

  CREATE ROLE Jane LOGIN  PASSWORD 'password';
  

  CREATE ROLE SysAdmin  CREATEDB CREATEROLE; 
  

  create_rule  ::= CREATE [ OR REPLACE ] RULE rule_name AS ON rule_event TO table_name [ WHERE  condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command [ ; ... ] )  }

rule_event  ::= SELECT | INSERT | UPDATE | DELETE

command  ::= SELECT | INSERT | UPDATE | DELETE | NOTIFY  
  

  CREATE TABLE t1(a int4, b int4);
CREATE TABLE t2(a int4, b int4);
CREATE RULE t1_to_t2 AS ON INSERT TO t1 DO INSTEAD
INSERT INTO t2 VALUES (new.a, new.b);
INSERT INTO t1 VALUES (3, 4);
SELECT * FROM t1;
  

  create_schema_name ::= CREATE SCHEMA [ IF NOT EXISTS ]  schema_name
                         [ AUTHORIZATION role_specification ]

create_schema_role ::= CREATE SCHEMA [ IF NOT EXISTS ]  AUTHORIZATION                                                role_specification [ schema_element [ ... ] ]

role_specification ::= role_name | CURRENT_USER | SESSION_USER
  

  CREATE SCHEMA IF NOT EXIST branch;
  

  CREATE ROLE John;
CREATE SCHEMA AUTHORIZATION john;
  

  CREATE SCHEMA branch
            CREATE TABLE dept(
               dept_id INT NOT NULL,
               dept_name TEXT NOT NULL,
               PRIMARY KEY (dept_id)
            ); 
  

  create_sequence ::= CREATE SEQUENCE [ IF NOT EXISTS ]  sequence_name
                       sequence_options

sequence_name ::= '<Text Literal>'

sequence_options ::= [ INCREMENT [ BY ] increment ]
                         [ MINVALUE minvalue | NO MINVALUE ]
                         [ MAXVALUE maxvalue | NO MAXVALUE ]
                         [ START [ WITH ] start ] [ CACHE cache ]
                         [ [ NO ] CYCLE ]
  

  CREATE TABLE t(k SERIAL)
  

  CREATE SEQUENCE t_k_seq CACHE 10000;
CREATE TABLE t(k integer NOT NULL DEFAULT  nextval('t_k_seq'));
  

  CREATE SEQUENCE s; 
  

  SELECT nextval('s');  
  

  CREATE SEQUENCE s2 CACHE  10000;
  

  SELECT nextval('s2');
 nextval
---------
          1
(1 row)  
  

  SELECT nextval('s2');
nextval
---------
      10001
(1 row)
  

  CREATE SEQUENCE s3 START 0 MINVALUE 0;
CREATE SEQUENCE
SELECT nextval('s3');
nextval
---------
          0
(1 row)
  

  create_table ::= CREATE [ TEMPORARY | TEMP ] TABLE [ IF  NOT EXISTS ]
                    table_name ( [ table_elem [ , ... ] ] )
                    [ WITH ( { COLOCATED = { 'true' | 'false' }
                                 | storage_parameters } )
                       | WITHOUT OIDS ]
                    [ SPLIT { INTO integer TABLETS
                               | AT VALUES ( split_row [ , ... ] ) } ]

table_elem ::= column_name data_type [  column_constraint [ ... ] ]
                 | table_constraint

column_constraint ::= [ CONSTRAINT constraint_name ]
                          { NOT NULL
                             | NULL
                             | CHECK ( expression )
                             | DEFAULT expression
                             | UNIQUE index_parameters
                             | PRIMARY KEY
                             | references_clause }
                          [ DEFERRABLE | NOT DEFERRABLE ]
                          [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

table_constraint ::= [ CONSTRAINT constraint_name ]
                          { CHECK ( expression )
                             | UNIQUE ( column_names ) index_parameters
                             | PRIMARY KEY ( key_columns )
                             | FOREIGN KEY ( column_names )
                                references_clause }
                          [ DEFERRABLE | NOT DEFERRABLE ]
                          [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

key_columns ::= hash_columns [ , range_columns ] |  range_columns

hash_columns ::= column_name [ HASH ] | ( column_name [  , ... ] ) HASH

range_columns ::= { column_name { ASC | DESC } } [ ,  ... ]

storage_parameters ::= storage_parameter [ , ... ]

storage_parameter ::= param_name [ = param_value ]

index_parameters ::= [ INCLUDE ( column_names ) ]
                         [ WITH ( storage_parameters ) ]

references_clause ::= REFERENCES table_name [  column_name [ , ... ] ]
                         [ MATCH FULL | MATCH PARTIAL | MATCH  SIMPLE ]
                         [ ON DELETE key_action ]
                         [ ON UPDATE key_action ]

split_row ::= ( column_value [ , ... ] )  
  

  CREATE TABLE sample(k1 int,
                              k2 int,
                              v1 int,
                              v2 text,
                              PRIMARY KEY  (k1, k2));  
  

  CREATE TABLE range(k1 int,
                              k2 int,
                              v1 int,
                              v2 text,
                              PRIMARY KEY (k1  ASC, k2 DESC));  
  

  CREATE TABLE student_grade(student_id int,
                                          class_id int,
                                          term_id  int,
                                          grade  int CHECK (grade >= 0 AND grade <= 10),
                                          PRIMARY  KEY (student_id, class_id, term_id));
  

  CREATE TABLE cars(id int PRIMARY KEY,
                             brand text CHECK  (brand in ('X', 'Y', 'Z')),
                             model text NOT  NULL,
                             color text NOT  NULL DEFAULT 'WHITE' CHECK (color in ('RED', 'WHITE', 'BLUE')));  
  

  CREATE TABLE products(id int PRIMARY KEY,
                                 descr text);

CREATE TABLE orders(id int PRIMARY KEY,
                                 pid int REFERENCES  products(id) ON DELETE CASCADE,
                                 amount int); 
  

  CREATE TABLE translations(message_id int UNIQUE,
                                       message_txt text);
  

  CREATE TABLE tracking (id  int PRIMARY KEY) SPLIT INTO 10 TABLETS;  
  

  CREATE DATABASE company WITH colocated = true;

CREATE TABLE employee(id INT PRIMARY KEY, name TEXT)  WITH (colocated = false);
  

  create_table_as ::= CREATE TABLE [ IF NOT EXISTS ] table_name
                      [ ( column_name [ , ... ] ) ]   AS query
                      [ WITH [ NO ] DATA ]  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));  
  

  INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4,  'b'), (3, 4.0, 5, 'c');  
  

  CREATE TABLE selective_sample SELECT * FROM sample  WHERE k1 > 1;  
  

  SELECT * FROM selective_sample ORDER BY k1; 
  

  create_trigger ::= CREATE TRIGGER name { BEFORE | AFTER  | INSTEAD OF }
                      { event [ OR ... ] } ON table_name
                      [ FROM table_name ] [ NOT  DEFERRABLE ]
                      [ FOR [ EACH ] { ROW | STATEMENT } ]
                      [ WHEN ( condition ) ] EXECUTE
                      { FUNCTION | PROCEDURE } function_name (
                      function_arguments )

event ::= INSERT
            | UPDATE [ OF column_name [ , ... ] ]
            | DELETE
            | TRUNCATE  
  

  CREATE EXTENSION insert_username;

CREATE EXTENSION moddatetime;

CREATE TABLE posts (
   id int primary key,
   content text,
   username text not null,
   moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER insert_usernames
   BEFORE INSERT OR UPDATE ON posts
   FOR EACH ROW
   EXECUTE  PROCEDURE insert_username (username);
   
CREATE TRIGGER update_moddatetime
   BEFORE UPDATE  ON posts
   FOR EACH ROW
   EXECUTE  PROCEDURE moddatetime (moddate);  
  

  SET ROLE memfire;
INSERT INTO posts VALUES(1, 'desc1');

SET ROLE postgres;
INSERT INTO posts VALUES(2, 'desc2');
INSERT INTO posts VALUES(3, 'desc3');

SET ROLE memfire;
INSERT INTO posts VALUES(4, 'desc4');

SELECT * FROM posts ORDER BY id;
  

  UPDATE posts SET content = 'desc1_updated' WHERE id = 1;
UPDATE posts SET content = 'desc3_updated' WHERE id = 3;

SELECT * FROM posts ORDER BY id;
  

  create_composite_type ::= CREATE TYPE type_name AS (
                            [ composite_type_elem [ , ... ] ] )

create_enum_type ::= CREATE TYPE type_name AS ENUM (
                        [ label [ , ... ] ] )

create_range_type ::= CREATE TYPE type_name AS RANGE (  SUBTYPE =
                         subtype [ , range_type_option [ ... ] ] )

create_base_type ::= CREATE TYPE type_name ( INPUT =  input_function ,
                            OUTPUT = output_function
                            [ , base_type_option [ ... ] ] )

create_shell_type ::= CREATE TYPE type_name

composite_type_elem ::= attribute_name data_type [  COLLATE collation ]

range_type_option ::= SUBTYPE_OPCLASS =  subtype_operator_class
                         | COLLATION = collation
                         | CANONICAL = canonical_function
                         | SUBTYPE_DIFF = subtype_diff_function

base_type_option ::= RECEIVE = receive_function
                        | SEND = send_function
                        | TYPMOD_IN = type_modifier_input_function
                        | TYPMOD_OUT = type_modifier_output_function
                        | INTERNALLENGTH = { internallength | VARIABLE }
                        | PASSEDBYVALUE
                        | ALIGNMENT = alignment
                        | STORAGE = storage
                        | LIKE = like_type
                        | CATEGORY = category
                        | PREFERRED = preferred
                        | DEFAULT = default
                        | ELEMENT = element
                        | DELIMITER = delimiter
                        | COLLATABLE = collatable 
  

  CREATE TYPE feature_struct AS (id INTEGER, name TEXT);
CREATE TABLE feature_tab_struct (feature_col feature_struct);
  

  CREATE TYPE feature_enum AS ENUM ('one', 'two', 'three');
CREATE TABLE feature_tab_enum (feature_col  feature_enum);
  

  CREATE TYPE feature_range AS RANGE (subtype=INTEGER);
CREATE TABLE feature_tab_range (feature_col feature_range);  
  

  CREATE TYPE int4_type;
CREATE FUNCTION int4_type_in(cstring) RETURNS int4_type
            LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int4in';
CREATE FUNCTION int4_type_out(int4_type) RETURNS  cstring
            LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int4out';
CREATE TYPE int4_type (
            INPUT = int4_type_in,
            OUTPUT = int4_type_out,
            LIKE = int4
         );
CREATE TABLE int4_table (t int4_type);  
  

  CREATE TYPE shell_type;  
  

  create_user ::= CREATE USER role_name
                  [ [ WITH ] role_option [ , ... ] ]

role_option ::= SUPERUSER
                  | NOSUPERUSER
                  | CREATEDB
                  | NOCREATEDB
                  | CREATEROLE
                  | NOCREATEROLE
                  | INHERIT
                  | NOINHERIT
                  | LOGIN
                  | NOLOGIN
                  | CONNECTION LIMIT connlimit
                  | [ ENCRYPTED ] PASSWORD ' password '
                  | PASSWORD NULL
                  | VALID UNTIL ' timestamp '
                  | IN ROLE role_name [ , ... ]
                  | IN GROUP role_name [ , ... ]
                  | ROLE role_name [ , ... ]
                  | ADMIN role_name [ , ... ]
                  | USER role_name [ , ... ]          |  SYSID uid 
  

  CREATE USER John WITH PASSWORD 'password'; 
  

  GRANT ALL ON DATABASE MemFire TO John;  
  

  REVOKE ALL ON DATABASE MemFire FROM John;  
  

  create_view ::= CREATE VIEW qualified_name
                   [ ( column_list ) ] AS select
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));
  

  INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3,  'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');
  

  CREATE VIEW sample_view AS SELECT * FROM sample WHERE  v2 != 'b' ORDER BY k1 DESC;
  

  SELECT * FROM sample_view;
  

  deallocate ::= DEALLOCATE [ PREPARE ] { name | ALL } 
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));
PREPARE ins (bigint, double precision, int, text) AS
               INSERT INTO sample(k1, k2, v1, v2) VALUES ($1, $2, $3, $4);
DEALLOCATE ins;  
  

  delete ::= [ WITH [ RECURSIVE ] with_query [ , ... ]  ] DELETE FROM
              [ ONLY  ] table_name [ * ] [ [ AS ] alias ]
              [  WHERE condition | WHERE CURRENT OF cursor_name ]
              [ returning_clause ]

returning_clause ::= RETURNING { * | {  output_expression
                                         [ [ AS ]  output_name ] }
                                         [ , ...  ] }  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));
INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4,  'b'), (3, 4.0, 5, 'c'); 
  

  SELECT * FROM sample ORDER BY k1;
k1 | k2 | v1 | v2
----+----+----+----
   1 | 2 |   3 | a
   2 | 3 |   4 | b
   3 | 4 |   5 | c
(3 rows)
  

  DELETE FROM sample WHERE k1 = 2 AND k2 = 3;
SELECT * FROM sample ORDER BY k1; 
  

  do ::= DO [ LANGUAGE lang_name  ] code
  

  DO $$DECLARE r record;
BEGIN
   FOR r IN  SELECT table_schema, table_name FROM information_schema.tables
              WHERE table_type = 'VIEW' AND table_schema = 'public'
   LOOP
      EXECUTE  'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' ||
quote_ident(r.table_name) || ' TO webuser';
      END LOOP;
END$$;
  

  drop_aggregate ::= DROP AGGREGATE [ IF EXISTS ]
                     { aggregate_name ( aggregate_signature ) }
                     [ , ... ] [ CASCADE | RESTRICT ]

aggregate_signature ::= * | aggregate_arg [ , ... ]
                              | [ aggregate_arg [ , ... ] ] ORDER  BY
                                  aggregate_arg [ ,  ... ]  
  

  CREATE AGGREGATE newcnt(*) (
          sfunc = int8inc,
          stype = int8,
          initcond = '0',
          parallel = safe
       );
DROP AGGREGATE newcnt(*);
  

  DROP AGGREGATE IF EXISTS newcnt(*);
CREATE AGGREGATE newcnt(*) (
           sfunc = int8inc,
           stype = int8,
           initcond = '0',
           parallel = safe
        );
DROP AGGREGATE IF EXISTS newcnt(*);  
  

  CREATE AGGREGATE newcnt(*) (
           sfunc = int8inc,
           stype = int8,
           initcond = '0',
           parallel = safe
        );
CREATE VIEW cascade_view AS
           SELECT newcnt(*) FROM pg_aggregate;
-- The following should error:
DROP AGGREGATE newcnt(*) RESTRICT;
-- The following should error:
DROP AGGREGATE newcnt(*);
DROP AGGREGATE newcnt(*) CASCADE;
  

  drop_cast ::= DROP CAST [ IF EXISTS ] ( cast_signature  )
               [ CASCADE | RESTRICT ]  
  

  CREATE FUNCTION sql_to_date(integer) RETURNS date AS $$
           SELECT $1::text::date
           $$  LANGUAGE SQL IMMUTABLE STRICT;
CREATE CAST (integer AS date) WITH FUNCTION  sql_to_date(integer) AS ASSIGNMENT;
DROP CAST (integer AS date);  
  

  drop_database ::= DROP DATABASE [ IF EXISTS ]  database_name 
  

  drop_domain ::= DROP DOMAIN [ IF EXISTS ] name [ , ...  ]
                  [ CASCADE | RESTRICT ]  
  

  CREATE DOMAIN idx DEFAULT 5 CHECK (VALUE > 0);
DROP DOMAIN idx;  
  

  CREATE DOMAIN idx DEFAULT 5 CHECK (VALUE > 0);
CREATE TABLE t (k idx primary key);
DROP DOMAIN idx CASCADE;  
  

  drop_extension ::= DROP EXTENSION [ IF EXISTS ]  extension_name
                     [ , ... ] [ CASCADE | RESTRICT ]  
  

  DROP EXTENSION IF EXISTS cube;  
  

  CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
DROP EXTENSION IF EXISTS cube RESTRICT;  
  

  DROP EXTENSION IF EXISTS cube CASCADE;  
  

  drop_function ::= DROP { FUNCTION | PROCEDURE } [ IF  EXISTS ]
                    { name [ ( [ argtype_decl [ , ... ] ] ) ] }
                    [ , ... ] [ CASCADE | RESTRICT ]

argtype_decl ::= [ argmode ] [ argname ] argtype  
  

   DROP FUNCTION IF EXISTS inc(i integer), mul(integer,  integer) CASCADE;
  

  drop_group ::= DROP GROUP [ IF EXISTS ] role_name [ ,  ... ]  
  

  DROP GROUP SysAdmin;
  

  drop_operator ::= DROP OPERATOR [ IF EXISTS ]
                    { operator_name ( operator_signature ) } [ , ... ]
                    [ CASCADE | RESTRICT ]

operator_signature ::= { left_type | NONE } , {  right_type | NONE } 
  

  CREATE OPERATOR @#@ (
          rightarg = int8,
          procedure = numeric_fac
       );
DROP OPERATOR @#@ (NONE, int8);  
  

  drop_operator_class ::= DROP OPERATOR CLASS [ IF EXISTS  ]
                            operator_class_name USING index_method
                            [ CASCADE | RESTRICT ]  
  

  drop_owned ::= DROP OWNED BY role_specification [ , ...  ]
                  [ CASCADE | RESTRICT ]

role_specification ::= role_name | CURRENT_USER |  SESSION_USER  
  

  drop owned by john;
  

  drop_policy ::= DROP POLICY [ IF EXISTS ] name ON  table_name
                  [ CASCADE | RESTRICT ]  
  

  DROP POLICY p1 ON table_foo; 
  

  drop_procedure ::= DROP PROCEDURE [ IF EXISTS ]
                     { name [ ( [ argtype_decl [ , ... ] ] ) ] }
                     [ , ... ] [ CASCADE | RESTRICT ]

argtype_decl ::= [ argmode ] [ argname ] argtype
  

  DROP PROCEDURE IF EXISTS transfer(integer, integer, dec) CASCADE;
  

  drop_role ::= DROP ROLE [ IF EXISTS ] role_name [ , ... ]
  

  DROP ROLE John;  
  

  drop_rule ::= DROP RULE [ IF EXISTS ] rule_name ON  table_name
                [ CASCADE | RESTRICT ]  
  

  CREATE TABLE t1(a int4, b int4);
CREATE TABLE t2(a int4, b int4);
CREATE RULE t1_to_t2 AS ON INSERT TO t1 DO INSTEAD
             INSERT INTO t2 VALUES (new.a, new.b);
DROP RULE t1_to_t2 ON t1;  
  

  drop_sequence ::= DROP SEQUENCE [ IF EXISTS ]  sequence_name
                    [ CASCADE | RESTRICT ]  
  

  CREATE TABLE t(k SERIAL, v INT);
DROP SEQUENCE t_k_seq;
DROP SEQUENCE t_k_seq CASCADE;  
  

  drop_table  ::= DROP TABLE [ IF EXISTS ] table_name [ , ... ]  
  

  drop_trigger  ::= DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
  

  DROP  TRIGGER update_moddatetime ON posts;
  

  drop_type  ::= DROP TYPE [ IF EXISTS ] type_name [ , ... ] [ CASCADE | RESTRICT ]  
  

  CREATE  TYPE feature_struct AS (id INTEGER, name TEXT);

DROP  TYPE feature_struct;  
  

  DROP  TYPE IF EXISTS feature_shell;
  

  CREATE  TYPE feature_enum AS ENUM ('one', 'two', 'three');
CREATE TABLE feature_tab_enum (feature_col feature_enum);
DROP TYPE feature_tab_enum  CASCADE;
  

  CREATE  TYPE feature_range AS RANGE (subtype=INTEGER);
CREATE TABLE feature_tab_range  (feature_col feature_range); -- The following should error: DROP TYPE  feature_range RESTRICT; DROP TABLE feature_tab_range;
DROP TYPE feature_range  RESTRICT;  
  

  drop_user  ::= DROP USER [ IF EXISTS ] role_name [ , ... ]  
  

  DROP USER John;
  

  end  ::= END [ TRANSACTION | WORK ]
  

  execute_statement  ::= EXECUTE name [ ( expression [ , ... ] ) ]  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
  

  PREPARE  ins (bigint, double precision, int, text) AS INSERT INTO sample(k1, k2, v1,  v2) VALUES ($1, $2, $3, $4);  
  

  EXECUTE  ins(1, 2.0, 3, 'a'); EXECUTE ins(2, 3.0, 4, 'b');
  

  SELECT * FROM sample ORDER BY k1;
  

  explain  ::= EXPLAIN [ [ ANALYZE ] [ VERBOSE ] | ( option [ , ... ] ) ] statement

option  ::= ANALYZE [ boolean ] | VERBOSE [ boolean ] | COSTS [ boolean ] | BUFFERS [  boolean ] | TIMING [ boolean ] | SUMMARY [ boolean ] | FORMAT { TEXT | XML |  JSON | YAML }  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1,  k2));
INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4,  'b'), (3, 4.0, 5, 'c'); EXPLAIN SELECT * FROM sample WHERE k1 = 1; QUERY PLAN

Foreign  Scan on sample (cost=0.00..112.50 rows=1000 width=44) (1 row)  
  

  grant_table  ::= GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |  TRIGGER } [ , ... ] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [ , ...  ] | ALL TABLES IN SCHEMA schema_name [ , ... ] } TO grant_role_spec [ , ... ]  [ WITH GRANT OPTION ]

grant_table_col  ::= GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_names ) [  ,(column_names ... ] | ALL [ PRIVILEGES ] ( column_names ) } ON { [ TABLE ]  table_name [ , ... ] } TO grant_role_spec [ , ... ] [ WITH GRANT OPTION ]

grant_seq  ::= GRANT { { USAGE | SELECT | UPDATE } [ , ... ] | ALL [ PRIVILEGES ] } ON {  SEQUENCE sequence_name [ , ... ] | ALL SEQUENCES IN SCHEMA schema_name [ ,  sequence_name [ ... ] ] } TO grant_role_spec [ , ... ] [ WITH GRANT OPTION ]

grant_db  ::= GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [ , ... ] | ALL [  PRIVILEGES ] } ON DATABASE database_name [ , ... ] TO grant_role_spec [ , ...  ] [ WITH GRANT OPTION ]

grant_domain  ::= GRANT { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name [ , ... ] TO  grant_role_spec [ , ... ] [ WITH GRANT OPTION ]

grant_schema  ::= GRANT { { CREATE | USAGE } [ , ... ] | ALL [ PRIVILEGES ] } ON SCHEMA  schema_name [ , ... ] TO grant_role_spec [ , ... ] [ WITH GRANT OPTION ]

grant_type  ::= GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name [ , ... ] TO  grant_role_spec [ , ... ] [ WITH GRANT OPTION ]

grant_role  ::= GRANT role_name [ , ... ] TO role_name [ , ... ] [ WITH ADMIN OPTION ]  

grant_role_spec  ::= [ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER  
  

  GRANT  SELECT ON stores TO PUBLIC;
  

  GRANT SysAdmins TO John;
  

  insert  ::= [ WITH [ RECURSIVE ] with_query [ , ... ] ] INSERT INTO table_name [ AS  alias ] [ ( column_names ) ]
{ DEFAULT VALUES | VALUES ( column_values ) [ ,(column_values ... ] |  subquery }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ returning_clause ]

returning_clause  ::= RETURNING { * | { output_expression [ [ AS ] output_name ] } [ , ... ] }

column_values  ::= { expression | DEFAULT } [ , ... ]

conflict_target  ::= ( { column_name | expression } [ , ... ] ) [ WHERE condition ] | ON  CONSTRAINT constraint_name

conflict_action  ::= DO NOTHING | DO UPDATE SET update_item [ , ... ] [ WHERE condition ]  
  

  CREATE  TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

INSERT  INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');

SELECT  * FROM sample ORDER BY k1;
  

  DROP  TABLE IF EXISTS sample CASCADE;

CREATE  TABLE sample( id int CONSTRAINT sample_id_pk PRIMARY KEY, c1 text CONSTRAINT  sample_c1_NN NOT NULL, c2 text CONSTRAINT sample_c2_NN NOT NULL);

INSERT  INTO sample(id, c1, c2) VALUES (1, 'cat' , 'sparrow'), (2, 'dog' ,  'blackbird'), (3, 'monkey' , 'thrush');

SELECT  id, c1, c2 FROM sample ORDER BY id;

INSERT INTO sample(id, c1, c2) VALUES (3,  'horse' , 'pigeon'), (4, 'cow' , 'robin') ON CONFLICT DO NOTHING; 
  

  lock_table  ::= LOCK [ TABLE ] { { [ ONLY ] name [ * ] } [ , ... ] } [ IN lockmode MODE ]  [ NOWAIT ]  lockmode  ::= ACCESS SHARE| ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE  | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE  
  

  prepare_statement ::= PREPARE name [ ( data_type [ ,  ... ] ) ] AS statement
  

  CREATE  TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

PREPARE  ins (bigint, double precision, int, text) AS INSERT INTO sample(k1, k2, v1,  v2) VALUES ($1, $2, $3, $4);

EXECUTE  ins(1, 2.0, 3, 'a');

EXECUTE  ins(2, 3.0, 4, 'b');

SELECT  * FROM sample ORDER BY k1;  
  

  reassign_owned  ::= REASSIGN OWNED BY role_specification [ , ... ] TO role_specification

role_specification  ::= role_name | CURRENT_USER | SESSION_USER  
  

  reassign  owned by john to memfire;
  

  reset_stmt  ::= RESET { name | ALL }  
  

  revoke_table  ::= REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE |  TRUNCATE | REFERENCES | TRIGGER } [ , ... ] | ALL [ PRIVILEGES ] } ON { [  TABLE ] table_name [ , ... ] | ALL TABLES IN SCHEMA schema_name [ , ... ] }  FROM { [ GROUP ] role_name | PUBLIC } [ , ... ] [ CASCADE | RESTRICT ]

revoke_table_col  ::= REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } (  column_names ) [ ,(column_names ... ] | ALL [ PRIVILEGES ] ( column_names ) }  ON [ TABLE ] table_name [ , ... ] FROM { [ GROUP ] role_name | PUBLIC } [ ,  ... ] [ CASCADE | RESTRICT ]

revoke_seq  ::= REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [ , ... ] | ALL  [ PRIVILEGES ] } ON { SEQUENCE sequence_name [ , ... ] | ALL SEQUENCES IN  SCHEMA schema_name [ , ... ] } FROM { [ GROUP ] role_name | PUBLIC } [ , ...  ] [ CASCADE | RESTRICT ]

revoke_db  ::= REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [ ,  ... ] | ALL [ PRIVILEGES ] } ON DATABASE database_name [ , ... ] FROM { [  GROUP ] role_name | PUBLIC } [ , ... ] [ CASCADE | RESTRICT ]

revoke_domain  ::= REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN  domain_name [ , ... ] FROM { [ GROUP ] role_name | PUBLIC } [ , ... ] [  CASCADE | RESTRICT ]

revoke_schema  ::= REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [ , ... ] | ALL [  PRIVILEGES ] } ON SCHEMA schema_name [ , ... ] FROM { [ GROUP ] role_name |  PUBLIC } [ , ... ] [ CASCADE | RESTRICT ]

revoke_type  ::= REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON TYPE  type_name [ , ... ] FROM { [ GROUP ] role_name | PUBLIC } [ , ... ] [ CASCADE  | RESTRICT ]

revoke_role  ::= REVOKE [ ADMIN OPTION FOR ] role_name [ , ... ] FROM role_name [ , ... ]  [ CASCADE | RESTRICT ]
  

  REVOKE  SELECT ON stores FROM PUBLIC; REVOKE SysAdmins FROM John;
  

  rollback  ::= ROLLBACK [ TRANSACTION | WORK ] 
  

  select  ::= [ WITH [ RECURSIVE ] { with_query [ , ... ] } ] SELECT [ ALL | DISTINCT [  ON { ( expression [ , ... ] ) } ] ] [ * | { { expression | fn_over_window } [  [ AS ] name ] } [ , ... ] ] [ FROM { from_item [ , ... ] } ]
[ WHERE condition ]
[ GROUP BY { grouping_element [ , ... ] } ]
[ HAVING { condition [ , ... ] } ]
[ WINDOW { { name AS window_definition } [ , ... ] } ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY {  order_expr [ , ... ] } ]
[ LIMIT [ integer | ALL ] ]
[ OFFSET integer [ ROW | ROWS ] ]

fn_over_window  ::= fn_invocation [ FILTER ( WHERE { boolean_expression [ , ... ] } ) ] OVER  { window_definition | name }

order_expr  ::= expression [ ASC | DESC | USING operator_name ] [ NULLS { FIRST | LAST }  ]  
  

  CREATE  TABLE sample1(k1 bigint, k2 float, v text, PRIMARY KEY (k1, k2));

CREATE  TABLE sample2(k1 bigint, k2 float, v text, PRIMARY KEY (k1, k2));

INSERT  INTO sampleINSERT INTO sample2(k1, k2, v) VALUES (1, 2.5, 'foo'), (1, 4.5,  'bar');

INSERT INTO sample2(k1, k2, v) VALUES (1,  2.5, 'foo'), (1, 4.5, 'bar');

SELECT  a.k1, a.k2, a.v as av, b.v as bv FROM sample1 a LEFT JOIN sample2 b ON (a.k1  = b.k1 and a.k2 = b.k2) WHERE a.k1 = 1 AND a.k2 IN (2.5, 3.5) ORDER BY a.k2  DESC;  
  

  set  ::= SET [ SESSION | LOCAL ] { configuration_parameter { TO | = } { value |  DEFAULT } | TIME ZONE { timezone | LOCAL | DEFAULT } }  
  

  set_constraints  ::= SET CONSTRAINTS { ALL | name [ , ... ] } { DEFERRED | IMMEDIATE }
  

  set_role  ::= SET [ SESSION | LOCAL ] ROLE { role_name | NONE } reset_role ::= RESET  ROLE
  

  select  session_user, current_user;

set  role john;

select  session_user, current_user; 
  

  set_session_authorization  ::= SET [ SESSION | LOCAL ] SESSION AUTHORIZATION { role_name | DEFAULT }

reset_session_authorization  ::= RESET SESSION AUTHORIZATION  
  

  Set session user to John.

select  session_user, current_user;

SET  select session_user, current_user; 
  

  set  ::= SET [ SESSION | LOCAL ] { configuration_parameter { TO | = } { value |  DEFAULT } | TIME ZONE { timezone | LOCAL | DEFAULT } }  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3,  'a'), (1, 3.0, 4, 'b');
COMMIT TRANSACTION; -- run in first shell.  
  

  show_stmt  ::= SHOW { name | ALL } 
  

  show_transaction  ::= SHOW TRANSACTION ISOLATION LEVEL
  

  truncate  ::= TRUNCATE [ TABLE ] { { [ ONLY ] name [ * ] } [ , ... ] }  
  

  CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text,  PRIMARY KEY (k1, k2));
INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4,  'b'), (3, 4.0, 5, 'c');
SELECT * FROM sample ORDER BY k1;
TRUNCATE sample; SELECT * FROM sample;  
  

  update  ::= [ WITH [ RECURSIVE ] with_query [ , ... ] ] UPDATE [ ONLY ] table_name [  * ] [ [ AS ] alias ] SET update_item [ , ... ] [ WHERE condition | WHERE  CURRENT OF cursor_name ] [ returning_clause ]

returning_clause  ::= RETURNING { * | { output_expression [ [ AS ] output_name ] } [ , ... ] }

update_item  ::= column_name = column_value | ( column_names ) = [ ROW ] ( column_values )  | ( column_names ) = ( query )

column_values  ::= { expression | DEFAULT } [ , ... ]

column_names  ::= column_name [ , ... ]  
  

  CREATE  TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));

INSERT  INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');

SELECT  * FROM sample ORDER BY k1; UPDATE sample SET v1 = v1 + 3, v2 = '7' WHERE k1 =  2 AND k2 = 3;

SELECT  * FROM sample ORDER BY k1;