Updated on 2022-08-16 GMT+08:00

Syntax

This section describes the syntax added to PostgreSQL Enhanced Edition on the basis of PostgreSQL 11 open-source edition. The following are supported:

  • CREATE SEQUENCE
  • CREATE/ALTER DATABASE
  • CREATE/ALTER VIEW
  • CREATE TABLE
  • CREATE TABLESPACE
  • CLUSTER
  • FORALL
  • CREATE/DROP DIRECTORY
  • ALTER TABLE ADD CONSTRAINT USING INDEX
  • Table names or table aliases for target columns in the INSERT INTO statement
  • ROWNUM in non-partitioned tables
  • CREATE INDEX ON COLUMN_EXPR
  • ALTER TABLE MODIFY
  • Specifying length units for VARCHAR and CHARACTER data types
  • TYPE/NAME/VERSION/VALUE/INTERVAL alias
  • Stored procedures
  • DATE
  • HASH-, RANGE-, and LIST-partitioned table creation
  • MERGE
    MERGE [HINT] INTO table_name USING ({subquery | table_name | view_name}) alias ON (condition) merge_update_clause merge_insert_clause;
  • Time interval operation:
    INTERVAL YEAR TO MONTH,INTERVAL DAY (l) TO SECOND (P);
  • CREATE TRIGGER with BODY:
    CREATE TRIGGER name... {DECLARE ... BEGIN | BEGIN} body END;
  • Stored procedure cursor syntax:
    CURSOR cursor_name [ parameter_list ] IS select_statement, TYPE type_name IS REF CURSOR;
  • Stored procedure cursor variables:
    SQL%ISOPEN,SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,cursor%ISOPEN,cursor%FOUND,cursor%NOTFOUND,cursor%ROWCOUNT;
  • Scheduled task advanced package:
    DBMS_JOB.SUBMIT,DBMS_JOB.ISUBMIT,DBMS_JOB.REMOVE,DBMS_JOB.BROKEN,DBMS_JOB.CHANGE,DBMS_JOB.WHAT,DBMS_JOB.NEXT_DATE,DBMS_JOB.INTERVAL;
  • CREATE USER:
    {DEFAULT COLLATION | DEFAULT TABLESPACE | [LOCAL] TEMPORARY TABLESPACE} Clause;
  • Session attribute modification:
    ALTER SESSION SET param_name = value;
  • Anonymous blocks
  • Cross-mode access to stored procedures
  • SQLCODE built-in variables in stored procedures
  • Enhanced syntax compatibility in stored procedures: stored procedure names can be used as end tags; FOR VAR IN SELECT-CLAUSE is supported; end tags can be specified for LOOP statements; default value of IN can be specified.
  • Subqueries with no alias specified
  • NOCYCLE in CREATE SEQUENCE
  • Replacing PASSWORD with IDENTIFIED BY in CREATE/ALTER USER
  • Specifying table names or alias in UPDATE SET
  • (columnname)=(value) in UPDATE SET
  • ALTER TABLE support for MODIFY NOT NULL and ENABLE
  • Null character string equivalent to NULL
  • sequencCURRVAL and sequencNEXTVAL
  • Creating users and schemas with same names at the same time
  • Deleting FROM from the table record syntax
  • XML data type pseudo column COLUMN_VALUE
  • OUTER JOIN (+)
  • Operators between the data types INTERVAL and number: +, -, >, <, >=, <=, and <>
  • Partition table DML operations: SELECT, INSERT, UPDATE, and DELETE
  • Composite partitioning of partition tables
  • Expressions used as partition boundaries
  • Trigger DDL: schema
  • Time format: IYY
  • CREATE/ALTER MATERIALIZED VIEW
  • CREATE TYPE
  • CREATE PROFILE
  • Enable/disable syntax for column constraints
  • Tablespace options specified by partitioned tables
  • DROP TABLE tablename [CASCADE CONSTRAINTS] [PURGE]
  • Stored procedure dynamic SQL syntax EXECUTE IMMEDIATE. The current edition does not support dynamic execution of anonymous blocks with DECLARE.
  • FUNCTION definition
  • CONNECT BY queries: LEVEL, CONNECT_BY_ROOT, and CONNECT_BY_ISLEAF pseudo columns; sys_connect_by_path, CONNECT_BY_ROOT, and ORDER SIBLINGS
  • TIME data type precision
  • Supported for virtual columns: column_name datatype [GENERATED ALWAYS] AS (expression) [VIRTUAL]
  • One-dimensional array definition: CREATE OR REPLACE TYPE array_name AS VARRAY (len) OF typename
  • One-dimensional array: array_name.extend, array_name.count, array_name.first, array_name.last
  • ROLLUP, CUBE, and GROUPING SETS Group By supported for grouping_id([expr1[, expr2[, ...exprn]]]) and group_id()
  • Sorting query statements returned by non-grouping fields: SELECT SUM(colname) FROM tbl ORDER BY colname