I'm diving into Aurora DSQL and trying to add a column with a NOT NULL constraint to an existing table. I tried using the `ALTER COLUMN ... SET NOT NULL` command after adding the column, but I got this error: `unsupported ALTER TABLE ALTER COLUMN ... SET NOT NULL statement`. Then, I attempted to use `ADD COLUMN ... NOT NULL DEFAULT 'temp'`, but it threw another error: `ALTER TABLE ADD COLUMN with constraint not supported`. This seems wild for a system meant for production. Is there really no way to add a required column? Am I missing something?
3 Answers
You're not missing anything! Aurora DSQL currently doesn't support directly adding a NOT NULL column. It's a known limitation from the early DSQL release. Right now, you can only add nullable columns. Just add your column as nullable, backfill the existing rows with a default value like 'temp', and then handle the NOT NULL logic in your application instead. Hopefully, they’ll add this feature in future updates.
Definitely check the AWS documentation for DSQL limitations. It can get confusing transitioning from traditional SQL due to certain restrictions. If DSQL isn't doing it for you, exploring RDS Proxy could lead you to a better solution!
DSQL is aimed at some pretty niche use cases and comes with a lot of limitations compared to standard SQL. Are you sure DSQL is the best choice for your application? If you're looking for more robust SQL support for a serverless setup, RDS Proxy might be a better fit for your needs!
Thanks! I was kind of worried I was missing something obvious. I'll look more into RDS Proxy to see if it fits my project better.