Aurora DSQL add column with constraints and remove column

Aurora DSQL does support adding columns to tables, but there are some limitations regarding constraints. According to the documentation, you can add columns using the CREATE TABLE and ALTER TABLE commands.

For column constraints, Aurora DSQL supports several types including NOT NULL, NULL, CHECK, DEFAULT, GENERATED ALWAYS AS, UNIQUE, and PRIMARY KEY. When creating a table, you can define these constraints as part of the column definition.

Regarding removing columns, Aurora DSQL does support this functionality with certain limitations:

• You cannot remove columns that are part of the shard key on sharded tables
• You cannot remove primary key columns on reference tables

As for your specific concern about adding columns with NOT NULL constraints, this is supported in Aurora DSQL's implementation of CREATE TABLE and ALTER TABLE commands.

Regarding jsonb column type, the documentation doesn't specifically mention support or lack of support for this data type in the provided sources.

It's important to note that Aurora DSQL manages DDL schema changes as distributed transactions due to its multi-AZ distributed architecture. This means that when you run DDL operations like adding or removing columns, you might encounter concurrency control errors if another transaction is updating the same resource simultaneously.

Since Aurora DSQL is PostgreSQL compatible and supports core relational features, it's designed to provide many of the capabilities you would expect from PostgreSQL, though with some differences in implementation due to its distributed nature.
Sources
Supported subsets of SQL commands in Aurora DSQL - Amazon Aurora DSQL
DDL limitations and other information for Aurora PostgreSQL Limitless Database - Amazon Aurora
DDL and distributed transactions in Aurora DSQL - Amazon Aurora DSQL
Unsupported PostgreSQL features in Aurora DSQL - Amazon Aurora DSQL