- Newest
- Most votes
- Most comments
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.
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.
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.
Relevant content
- Accepted Answerasked 8 months ago
- asked 3 years ago
- AWS OFFICIALUpdated 10 months ago
Hey there – this was a good idea, but I already checked if search_path settings made an impact and they didn't.