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

0

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?

acco
asked a year ago1201 views
2 Answers
0

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.

AWS
answered a year ago
  • Hey there – this was a good idea, but I already checked if search_path settings made an impact and they didn't.

0

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

This stack overflow question 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.

answered 8 months ago
  • We upgraded from 14.5 to 14.8, then it started happens, so upgrade to that version seems does not help. I did not find any solution for it, "set search_path = public;" doesn't work.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions