`alter table if exists` raises an error on Amazon RDS Postgresql 15.2

Question

On most instances of Postgres I've tried, this query works just fine if `my_table` does not exist:

```
alter table if exists public.my_table add column foo boolean;
NOTICE:  relation "my_table" does not exist, skipping
ALTER TABLE
```

I've tried on multiple Postgres versions, including `15.2`.

However, on AWS RDS specifically, their `15.2` version will raise an error if the table does not exist:

```
=> alter table if exists public.my_table add column foo boolean;
ERROR:  relation "public.my_table" does not exist
```

This does not appear to be an issue with other versions of RDS Postgres.

Any idea why this might be happening? Is there a parameter that's flipped on in 15.2 by default that causes this to happen?

Answer

I've just come across this same error, it appears to be related to specific builds of Postgres on RDS.

[This stack overflow question](https://stackoverflow.com/questions/75838386/alter-table-if-exists-raises-an-error-on-amazon-rds-postgresql-15-2) suggests that the error is present in RDS Postgres versions 11.19, 12.14, 13.10 and 14.7, and is resolved in the next minor version: 11.20, 12.15, 13.11 and 14.8.

I'd imagine that upgrading to the next version of Postgres on RDS would resolve the issue.

Answer

It is possible that the behavior you are observing with the alter table if exists statement on AWS RDS 15.2 is due to differences in configuration settings compared to other Postgres installations you have used.

One configuration setting that may be relevant is search_path (SHOW search_path;), which determines the order in which schemas are searched when looking for database objects. By default, the search_path on AWS RDS may include the "$user", public schema, while on other installations it may only include public. If the table my_table does not exist in any of the schemas listed in the search_path, the if exists clause may not be able to correctly skip the alter table statement.

To verify if this is the case, you can try modifying the search_path to only include public before running the alter table if exists statement, like so:
set search_path = public;
alter table if exists public.my_table add column foo boolean;

If this works without raising an error, then it suggests that the issue may indeed be related to the search_path configuration.
You can also try using the fully qualified name of the table, including the schema, to see if this avoids the error on AWS RDS 15.2:

alter table if exists "public"."my_table" add column foo boolean;

If neither of these options work, it may be worth checking the AWS RDS documentation or contacting AWS support to see if there are any other configuration differences that could be causing this behavior.

`alter table if exists` raises an error on Amazon RDS Postgresql 15.2

관련 콘텐츠