Syntax

This section describes the syntax added to RDS for 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 pseudocolumn for non-partitioned tables
• Creating indexes on expressions: 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
• Stored procedure compatibility enhancements, including using stored procedure names as end labels, supporting FOR VAR IN SELECT-CLAUSE, specifying end labels for LOOP statements; and specifying default values for IN parameters
• 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
• sequence CURRVAL and sequence NEXTVAL
• Creating a schema with the same name while creating a new user
• Deleting FROM from the table record syntax
• XML data type pseudocolumn COLUMN_VALUE
• OUTER JOIN (+)
• Operators between the data types INTERVAL and number: +, -, >, <, >=, <=, and <>
• DML operations for partition tables: SELECT, INSERT, UPDATE, and DELETE
• Composite partitioning for partitioned tables
• Expressions used as partition boundaries
• Trigger DDL: schema-qualified naming for triggers
• Time format: IYY
• CREATE/ALTER MATERIALIZED VIEW
• CREATE TYPE
• CREATE PROFILE
• ENABLE/DISABLE syntax for column constraints
• TABLESPACE options for partitioned tables
• DROP TABLE tablename [CASCADE CONSTRAINTS] [PURGE]
• EXECUTE IMMEDIATE for dynamic SQL in stored procedures. The current version does not support dynamic execution of anonymous blocks containing DECLARE statements.
• FUNCTION definition
• CONNECT BY queries: LEVEL, CONNECT_BY_ROOT, and CONNECT_BY_ISLEAF pseudocolumns, the SYS_CONNECT_BY_PATH function, CONNECT_BY_ROOT operations, and ORDER SIBLINGS
• TIME data type precision
• 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 operations: array_name.extend, array_name.count, array_name.first, array_name.last
• ROLLUP, CUBE, and GROUPING SETS Group By for grouping_id([expr1[, expr2[, ...exprn]]]) and group_id()
• Query statements without a GROUP BY clause that return sorted results: SELECT SUM(colname) FROM tbl ORDER BY colname