ALTER TABLE CREATE DATABASE CREATE INDEX CREATE SCHEMA CREATE TABLE CREATE TABLE AS DROP DATABASE SELECT INTO Constraints Default values Extensions Foreign keys Functions Sequences

DDL limitations and other information for Aurora PostgreSQL Limitless Database

The following topics describe limitations or provide more information for DDL SQL commands in Aurora PostgreSQL Limitless Database.

ALTER TABLE

The ALTER TABLE command is generally supported in Aurora PostgreSQL Limitless Database. For more information, see ALTER TABLE in the PostgreSQL documentation.

Limitations

ALTER TABLE has the following limitations for supported options.

Removing a column

On sharded tables, you can't remove columns that are part of the shard key.
On reference tables, you can't remove primary key columns.

Changing a column's data type

The USING expression isn't supported.
On sharded tables, you can't change the type for columns that are part of the shard key.

Adding or removing a constraint

For details on what isn't supported, see Constraints.

Changing a column's default value

Default values are supported. For more information, see Default values.

Unsupported options

Some options aren't supported because they depend on unsupported features, such as triggers.

The following table-level options for ALTER TABLE aren't supported:

ALL IN TABLESPACE
ATTACH PARTITION
DETACH PARTITION
ONLY flag
RENAME CONSTRAINT

The following column-level options for ALTER TABLE aren't supported:

ADD GENERATED
DROP EXPRESSION [ IF EXISTS ]
DROP IDENTITY [ IF EXISTS ]
RESET
RESTART
SET
SET COMPRESSION
SET STATISTICS

CREATE DATABASE

In Aurora PostgreSQL Limitless Database, only limitless databases are supported.

While CREATE DATABASE is running, databases that were successfully created in one or more nodes might fail in other nodes, because database creation is a nontransactional operation. In this case, the database objects that were successfully created are automatically removed from all of the nodes within a predetermined amount of time to keep consistency in the DB shard group. During this time, re-creation of a database with the same name might result in an error indicating that the database already exists.

The following options are supported:

Collation:


CREATE DATABASE name WITH 
    [LOCALE = locale]
    [LC_COLLATE = lc_collate]
    [LC_CTYPE = lc_ctype]
    [ICU_LOCALE = icu_locale]
    [ICU_RULES = icu_rules]
    [LOCALE_PROVIDER = locale_provider]
    [COLLATION_VERSION = collation_version];

CREATE DATABASE WITH OWNER:


CREATE DATABASE name WITH OWNER = user_name;

The following options aren't supported:

CREATE DATABASE WITH TABLESPACE:


CREATE DATABASE name WITH TABLESPACE = tablespace_name;

CREATE DATABASE WITH TEMPLATE:


CREATE DATABASE name WITH TEMPLATE = template;

CREATE INDEX

CREATE INDEX CONCURRENTLY is supported for sharded tables:


CREATE INDEX CONCURRENTLY index_name ON table_name(column_name);

CREATE UNIQUE INDEX is supported for all table types:


CREATE UNIQUE INDEX index_name ON table_name(column_name);

CREATE UNIQUE INDEX CONCURRENTLY isn't supported:


CREATE UNIQUE INDEX CONCURRENTLY index_name ON table_name(column_name);

For more information, see UNIQUE. For general information on creating indexes, see CREATE INDEX in the PostgreSQL documentation.

Showing indexes

Not all indexes are visible on routers when you use \d table_name or similar commands. Instead, use the pg_catalog.pg_indexes view to get indexes, as shown in the following example.


SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"id"}';
CREATE TABLE items (id int PRIMARY KEY, val int);
CREATE INDEX items_my_index on items (id, val);

postgres_limitless=> SELECT * FROM pg_catalog.pg_indexes WHERE tablename='items';

 schemaname | tablename |   indexname    | tablespace |                                indexdef
------------+-----------+----------------+------------+------------------------------------------------------------------------
 public     | items     | items_my_index |            | CREATE INDEX items_my_index ON ONLY public.items USING btree (id, val)
 public     | items     | items_pkey     |            | CREATE UNIQUE INDEX items_pkey ON ONLY public.items USING btree (id)
(2 rows)

CREATE SCHEMA

CREATE SCHEMA with a schema element isn't supported:


CREATE SCHEMA my_schema CREATE TABLE (column_name INT);

This generates an error similar to the following:


ERROR: CREATE SCHEMA with schema elements is not supported

CREATE TABLE

Relations in CREATE TABLE statements aren't supported, for example:


CREATE TABLE orders (orderid int, customerId int, orderDate date) WITH (autovacuum_enabled = false);

IDENTITY columns aren't supported, for example:


CREATE TABLE orders (orderid INT GENERATED ALWAYS AS IDENTITY);

Aurora PostgreSQL Limitless Database supports up to 54 characters for sharded table names.

CREATE TABLE AS

To create a table using CREATE TABLE AS, you must use the rds_aurora.limitless_create_table_mode variable. For sharded tables, you must also use the rds_aurora.limitless_create_table_shard_key variable. For more information, see Creating limitless tables by using variables.


-- Set the variables.
SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"a"}';

CREATE TABLE ctas_table AS SELECT 1 a;

-- "source" is the source table whose columns and data types are used to create the new "ctas_table2" table.
CREATE TABLE ctas_table2 AS SELECT a,b FROM source;

You can't use CREATE TABLE AS to create reference tables, because they require primary key constraints. CREATE TABLE AS doesn't propagate primary keys to new tables.

For general information, see CREATE TABLE AS in the PostgreSQL documentation.

DROP DATABASE

You can drop databases that you've created.

The DROP DATABASE command runs asynchronously in the background. While it's running, you will receive an error if you try to create a new database with the same name.

SELECT INTO

SELECT INTO is functionally similar to CREATE TABLE AS. You must use the rds_aurora.limitless_create_table_mode variable. For sharded tables, you must also use the rds_aurora.limitless_create_table_shard_key variable. For more information, see Creating limitless tables by using variables.


-- Set the variables.
SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"a"}';

-- "source" is the source table whose columns and data types are used to create the new "destination" table.
SELECT * INTO destination FROM source;

Currently, the SELECT INTO operation is performed through the router, not directly through the shards. Therefore, performance can be slow.

For general information, see SELECT INTO in the PostgreSQL documentation.

Constraints

The following limitations apply to constraints in Aurora PostgreSQL Limitless Database.

CHECK

Simple constraints that involve comparison operators with literals are supported. More complex expressions and constraints that require function validations aren't supported, as shown in the following examples.


CREATE TABLE my_table (
    id  INT CHECK (id > 0)                                     -- supported
  , val INT CHECK (val > 0 AND val < 1000)                     -- supported
  , tag TEXT CHECK (length(tag) > 0)                           -- not supported: throws "Expression inside CHECK constraint is not supported"
  , op_date TIMESTAMP WITH TIME ZONE CHECK (op_date <= now())  -- not supported: throws "Expression inside CHECK constraint is not supported"
);

You can give constraints explicit names, as shown in the following example.


CREATE TABLE my_table (
    id  INT CONSTRAINT positive_id  CHECK (id > 0)
  , val INT CONSTRAINT val_in_range CHECK (val > 0 AND val < 1000)
);

You can use table-level constraint syntax with the CHECK constraint, as shown in the following example.


CREATE TABLE my_table (
    id INT CONSTRAINT positive_id  CHECK (id > 0)
  , min_val INT CONSTRAINT min_val_in_range CHECK (min_val > 0 AND min_val < 1000)
  , max_val INT
  , CONSTRAINT max_val_in_range CHECK (max_val > 0 AND max_val < 1000 AND max_val > min_val)
);

EXCLUDE

Exclusion constraints aren't supported in Aurora PostgreSQL Limitless Database.

FOREIGN KEY

For more information, see Foreign keys.

NOT NULL

NOT NULL constraints are supported with no restrictions.

PRIMARY KEY

Primary key implies unique constraints and therefore the same restrictions on unique constraints apply on primary key. This means:

If a table is converted into a sharded table, the shard key must be a subset of the primary key. That is, the primary key contains all columns of the shard key.
If a table is converted into a reference table, it must have a primary key.

The following examples illustrate the use of primary keys.


-- Create a standard table.
CREATE TABLE public.my_table (
    item_id INT
  , location_code INT
  , val INT
  , comment text
);

-- Change the table to a sharded table using the 'item_id' and 'location_code' columns as shard keys.
CALL rds_aurora.limitless_alter_table_type_sharded('public.my_table', ARRAY['item_id', 'location_code']);

Trying to add a primary key that doesn't contain a shard key:


-- Add column 'item_id' as the primary key.
-- Invalid because the primary key doesnt include all columns from the shard key:
-- 'location_code' is part of the shard key but not part of the primary key
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id); -- ERROR

-- add column "val" as primary key
-- Invalid because primary key does not include all columns from shard key:
--  item_id and location_code iare part of shard key but not part of the primary key
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id); -- ERROR

Trying to add a primary key that does contain a shard key:


-- Add the 'item_id' and 'location_code' columns as the primary key.
-- Valid because the primary key contains the shard key.
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id, location_code); -- OK

-- Add the 'item_id', 'location_code', and 'val' columns as the primary key.
-- Valid because the primary key contains the shard key.
ALTER TABLE public.my_table ADD PRIMARY KEY (item_id, location_code, val); -- OK

Change a standard table to a reference table.


-- Create a standard table.
CREATE TABLE zipcodes (zipcode INT PRIMARY KEY, details VARCHAR);

-- Convert the table to a reference table.
CALL rds_aurora.limitless_alter_table_type_reference('public.zipcode');

For more information on creating sharded and reference tables, see Creating Aurora PostgreSQL Limitless Database tables.

UNIQUE

In sharded tables, the unique key must contain the shard key, that is, the shard key must be a subset of the unique key. This is checked when changing the table type to sharded. In reference tables there's no restriction.


CREATE TABLE customer (
    customer_id INT NOT NULL
  , zipcode INT
  , email TEXT UNIQUE
);

Table-level UNIQUE constraints are supported, as shown in the following example.


CREATE TABLE customer (
    customer_id INT NOT NULL
  , zipcode INT
  , email TEXT
  , CONSTRAINT zipcode_and_email UNIQUE (zipcode, email)
);

The following example shows the use of a primary key and a unique key together. Both keys must include the shard key.


SET rds_aurora.limitless_create_table_mode='sharded';
SET rds_aurora.limitless_create_table_shard_key='{"p_id"}';
CREATE TABLE t1 (
p_id BIGINT NOT NULL,
c_id BIGINT NOT NULL,
PRIMARY KEY (p_id),
UNIQUE (p_id, c_id)
);

For more information, see Constraints in the PostgreSQL documentation.

Default values

Aurora PostgreSQL Limitless Database supports expressions in default values.

The following example shows the use of default values.


CREATE TABLE t (
    a INT DEFAULT 5,
    b TEXT DEFAULT 'NAN',
    c NUMERIC
);

CALL rds_aurora.limitless_alter_table_type_sharded('t', ARRAY['a']);
INSERT INTO t DEFAULT VALUES;
SELECT * FROM t;

 a |  b  | c 
---+-----+---
 5 | NAN |
(1 row)

Expressions are supported, as shown in the following example.


CREATE TABLE t1 (a NUMERIC DEFAULT random());

The following example adds a new column that is NOT NULL and has a default value.


ALTER TABLE t ADD COLUMN d BOOLEAN NOT NULL DEFAULT FALSE;
SELECT * FROM t;

 a |  b  | c | d 
---+-----+---+---
 5 | NAN |   | f
(1 row)

The following example alters an existing column with a default value.


ALTER TABLE t ALTER COLUMN c SET DEFAULT 0.0;
INSERT INTO t DEFAULT VALUES;
SELECT * FROM t;

 a |  b  | c   |  d  
---+-----+-----+-----
 5 | NAN |     | f
 5 | NAN | 0.0 | f
(2 rows)

The following example drops a default value.


ALTER TABLE t ALTER COLUMN a DROP DEFAULT;
INSERT INTO t DEFAULT VALUES;
SELECT * FROM t;

 a |  b  | c   |  d  
---+-----+-----+-----
 5 | NAN |     | f
 5 | NAN | 0.0 | f
   | NAN | 0.0 | f
(3 rows)

For more information, see Default values in the PostgreSQL documentation.

Extensions

The following PostgreSQL extensions are supported in Aurora PostgreSQL Limitless Database:

aurora_limitless_fdw – This extension is preinstalled. You can't drop it.
aws_s3 – This extension works in Aurora PostgreSQL Limitless Database similar to the way it does in Aurora PostgreSQL.

You can import data from an Amazon S3 bucket to an Aurora PostgreSQL Limitless Database DB cluster, or export data from an Aurora PostgreSQL Limitless Database DB cluster to an Amazon S3 bucket. For more information, see Importing data from Amazon S3 into an Aurora PostgreSQL DB cluster and Exporting data from an Aurora PostgreSQL DB cluster to Amazon S3.
btree_gin
citext
ip4r
pg_buffercache – This extension behaves differently in Aurora PostgreSQL Limitless Database from community PostgreSQL. For more information, see pg_buffercache differences in Aurora PostgreSQL Limitless Database.
pg_stat_statements
pg_trgm
pgcrypto
pgstattuple – This extension behaves differently in Aurora PostgreSQL Limitless Database from community PostgreSQL. For more information, see pgstattuple differences in Aurora PostgreSQL Limitless Database.
pgvector
plpgsql – This extension is preinstalled, but you can drop it.
PostGIS – Long transactions and table management functions aren't supported. Modifying the spatial reference table isn't supported.
unaccent
uuid

Most PostgreSQL extensions currently aren't supported in Aurora PostgreSQL Limitless Database. However, you can still use the shared_preload_libraries (SPL) configuration setting to load extensions into the Aurora PostgreSQL primary DB cluster. They are also loaded into Aurora PostgreSQL Limitless Database, but might not function correctly.

For example, you can load the pg_hint_plan extension, but loading it doesn't guarantee that the hints passed in query comments are used.

Note

You can't modify objects associated with the pg_stat_statements extension. For information on installing pg_stat_statements, see limitless_stat_statements.

You can use the pg_available_extensions and pg_available_extension_versions functions to find the extensions that are supported in Aurora PostgreSQL Limitless Database.

The following DDLs are supported for extensions:

CREATE EXTENSION

You can create extensions, as in PostgreSQL.


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

For more information, see CREATE EXTENSION in the PostgreSQL documentation.

ALTER EXTENSION

The following DDLs are supported:


ALTER EXTENSION name UPDATE [ TO new_version ]

ALTER EXTENSION name SET SCHEMA new_schema

For more information, see ALTER EXTENSION in the PostgreSQL documentation.

DROP EXTENSION

You can drop extensions, as in PostgreSQL.


DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

For more information, see DROP EXTENSION in the PostgreSQL documentation.

The following DDLs aren't supported for extensions:

ALTER EXTENSION

You can't add or drop member objects from extensions.


ALTER EXTENSION name ADD member_object

ALTER EXTENSION name DROP member_object

pg_buffercache differences in Aurora PostgreSQL Limitless Database

In Aurora PostgreSQL Limitless Database, when you install the pg_buffercache extension and use the pg_buffercache view, you receive buffer-related information only from the node to which you're currently connected: the router. Similarly, using the function pg_buffercache_summary or pg_buffercache_usage_counts provides information only from the connected node.

You can have numerous nodes and might need to access buffer information from any node to diagnose issues effectively. Therefore, Limitless Database provides the following functions:

rds_aurora.limitless_pg_buffercache(subcluster_id)
rds_aurora.limitless_pg_buffercache_summary(subcluster_id)
rds_aurora.limitless_pg_buffercache_usage_counts(subcluster_id)

By inputting the subcluster ID of any node, whether it's a router or shard, you can easily access the buffer information specific to that node. These functions are directly available when you install the pg_buffercache extension in the limitless database.

Note

Aurora PostgreSQL Limitless Database supports these functions for version 1.4 and higher of the pg_buffercache extension.

The columns shown in the limitless_pg_buffercache view differ slightly from those in the pg_buffercache view:

bufferid – Remains unchanged from pg_buffercache.
relname – Instead of displaying the file node number as in pg_buffercache, limitless_pg_buffercache presents the associated relname if available in the current database or shared system catalogs, otherwise NULL.
parent_relname – This new column, not present in pg_buffercache, displays the parent relname if the value in the relname column represents a partitioned table (in the case of sharded tables). Otherwise, it displays NULL.
spcname – Instead of displaying the tablespace object identifier (OID) as in pg_buffercache, limitless_pg_buffercache displays the tablespace name.
datname – Instead of displaying the database OID as in pg_buffercache, limitless_pg_buffercache displays the database name.
relforknumber – Remains unchanged from pg_buffercache.
relblocknumber – Remains unchanged from pg_buffercache.
isdirty – Remains unchanged from pg_buffercache.
usagecount – Remains unchanged from pg_buffercache.
pinning_backends – Remains unchanged from pg_buffercache.

The columns in limitless_pg_buffercache_summary and limitless_pg_buffercache_usage_counts views are the same as those in the regular pg_buffercache_summary and pg_buffercache_usage_counts views, respectively.

By using these functions, you can access detailed buffer cache information across all nodes in your Limitless Database environment, facilitating more effective diagnosis and management of your database systems.

pgstattuple differences in Aurora PostgreSQL Limitless Database

In Aurora PostgreSQL, the pgstattuple extension currently doesn't support foreign tables, partitioned tables, or partitioned indexes. However, in Aurora PostgreSQL Limitless Database, user-created objects are often among these unsupported types. While there are regular tables and indexes (for example, catalog tables and their indexes), most objects reside on foreign nodes, making them foreign objects for the router.

We recognize the importance of this extension for obtaining tuple-level statistics, which is crucial for tasks such as removing bloat and gathering diagnostic information. Therefore, Aurora PostgreSQL Limitless Database provides support for the pgstattuple extension in limitless databases.

Aurora PostgreSQL Limitless Database includes the following functions in the rds_aurora schema:

Tuple-level statistics functions

rds_aurora.limitless_pgstattuple(relation_name)

Purpose: Extract tuple-level statistics for standard tables and their indexes
Input: relation_name (text) – The name of the relation
Output: Columns consistent with those returned by the pgstattuple function in Aurora PostgreSQL

rds_aurora.limitless_pgstattuple(relation_name, subcluster_id)

Purpose: Extract tuple-level statistics for reference tables, sharded tables, catalog tables, and their indexes
Input:
- relation_name (text) – The name of the relation
- subcluster_id (text) – The subcluster ID of the node where the statistics are to be extracted
Output:
- For reference and catalog tables (including their indexes), columns are consistent with those in Aurora PostgreSQL.
- For sharded tables, the statistics represent only the partition of the sharded table residing on the specified subcluster.

Index statistics functions

rds_aurora.limitless_pgstatindex(relation_name)

Purpose: Extract statistics for B-tree indexes on standard tables
Input: relation_name (text) – The name of the B-tree index
Output: All columns except root_block_no are returned. The returned columns are consistent with the pgstatindex function in Aurora PostgreSQL.

rds_aurora.limitless_pgstatindex(relation_name, subcluster_id)

Purpose: Extract statistics for B-tree indexes on reference tables, sharded tables, and catalog tables.
Input:
- relation_name (text) – The name of the B-tree index
- subcluster_id (text) – The subcluster ID of the node where the statistics are to be extracted
Output:
- For reference and catalog table indexes, all columns (except root_block_no) are returned. The returned columns are consistent with Aurora PostgreSQL.
- For sharded tables, the statistics represent only the partition of the sharded table index residing on the specified subcluster. The tree_level column shows the average across all table slices on the requested subcluster.

rds_aurora.limitless_pgstatginindex(relation_name)

Purpose: Extract statistics for Generalized Inverted Indexes (GINs) on standard tables
Input: relation_name (text) – The name of the GIN
Output: Columns consistent with those returned by the pgstatginindex function in Aurora PostgreSQL

rds_aurora.limitless_pgstatginindex(relation_name, subcluster_id)

Purpose: Extract statistics for GIN indexes on reference tables, sharded tables, and catalog tables.
Input:
- relation_name (text) – The name of the index
- subcluster_id (text) – The subcluster ID of the node where the statistics are to be extracted
Output:
- For reference and catalog table GIN indexes, columns are consistent with those in Aurora PostgreSQL.
- For sharded tables, the statistics represent only the partition of the sharded table index residing on the specified subcluster.

rds_aurora.limitless_pgstathashindex(relation_name)

Purpose: Extract statistics for hash indexes on standard tables
Input: relation_name (text) – The name of the hash index
Output: Columns consistent with those returned by the pgstathashindex function in Aurora PostgreSQL

rds_aurora.limitless_pgstathashindex(relation_name, subcluster_id)

Purpose: Extract statistics for hash indexes on reference tables, sharded tables, and catalog tables.
Input:
- relation_name (text) – The name of the index
- subcluster_id (text) – The subcluster ID of the node where the statistics are to be extracted
Output:
- For reference and catalog table hash indexes, columns are consistent with Aurora PostgreSQL.
- For sharded tables, the statistics represent only the partition of the sharded table index residing on the specified subcluster.

Page count functions

rds_aurora.limitless_pg_relpages(relation_name)

Purpose: Extract the page count for standard tables and their indexes
Input: relation_name (text) – The name of the relation
Output: Page count of the specified relation

rds_aurora.limitless_pg_relpages(relation_name, subcluster_id)

Purpose: Extract the page count for reference tables, sharded tables, and catalog tables (including their indexes)
Input:
- relation_name (text) – The name of the relation
- subcluster_id (text) – The subcluster ID of the node where the page count is to be extracted
Output: For sharded tables, the page count is the sum of pages across all table slices on the specified subcluster.

Approximate tuple-level statistics functions

rds_aurora.limitless_pgstattuple_approx(relation_name)

Purpose: Extract approximate tuple-level statistics for standard tables and their indexes
Input: relation_name (text) – The name of the relation
Output: Columns consistent with those returned by the pgstattuple_approx function in Aurora PostgreSQL

rds_aurora.limitless_pgstattuple_approx(relation_name, subcluster_id)

Purpose: Extract approximate tuple-level statistics for reference tables, sharded tables, and catalog tables (including their indexes)
Input:
- relation_name (text) – The name of the relation
- subcluster_id (text) – The subcluster ID of the node where the statistics are to be extracted
Output:
- For reference and catalog tables (including their indexes), columns are consistent with those in Aurora PostgreSQL.
- For sharded tables, the statistics represent only the partition of the sharded table residing on the specified subcluster.

Note

Currently, Aurora PostgreSQL Limitless Database doesn't support the pgstattuple extension on materialized views, TOAST tables, or temporary tables.

In Aurora PostgreSQL Limitless Database, you must provide the input as text, although Aurora PostgreSQL supports other formats.

Foreign keys

Foreign key (FOREIGN KEY) constraints are supported with some limitations:

CREATE TABLE with FOREIGN KEY is supported only for standard tables. To create a sharded or reference table with FOREIGN KEY, first create the table without a foreign key constraint. Then modify it using the following statement:
```
ALTER TABLE ADD CONSTRAINT;
```
Converting a standard table to a sharded or reference table isn't supported when the table has a foreign key constraint. Drop the constraint, then add it after conversion.
The following limitations apply to table types for foreign key constraints:
- A standard table can have a foreign key constraint to another standard table.
- A sharded table can have a foreign key constraint if the parent and child tables are collocated and the foreign key is a superset of the shard key.
- A sharded table can have a foreign key constraint to a reference table.
- A reference table can have a foreign key constraint to another reference table.

Topics

Foreign key options
Examples

Foreign key options

Foreign keys are supported in Aurora PostgreSQL Limitless Database for some DDL options. The following table lists options that are supported and not supported between Aurora PostgreSQL Limitless Database tables.

DDL option	Reference to reference	Sharded to sharded (collocated)	Sharded to reference	Standard to standard
`DEFERRABLE`	Yes	Yes	Yes	Yes
`INITIALLY DEFERRED`	Yes	Yes	Yes	Yes