Thread: PostgreSQL 14.7 "ALTER TABLE IF EXISTS" fails - ERROR: schema/relation "" does not exist

Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trademarks of Sky Limited and Sky International AG and are used under licence.

Sky UK Limited (Registration No. 2906991), Sky-In-Home Service Limited (Registration No. 2067075), Sky Subscribers Services Limited (Registration No. 2340150) and Sky CP Limited (Registration No. 9513259) are direct or indirect subsidiaries of Sky Limited (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD

"Salavessa, Joao (Senior Developer)" <Joao.Salavessa@sky.uk> writes:
> In PostgreSQL 14.7 (AWS RDS) the “IF EXISTS” seems to be ignored for the “ALTER TABLE” statement.
> Example – the query “ALTER TABLE IF EXISTS dummy.relation DROP CONSTRAINT IF EXISTS dummy” (when schema dummy doesn’t
exist,or when schema dummy exists but relation doesn’t) works on 14.6 but fails in 14.7. 
> Few outputs below:

> ALTER TABLE IF EXISTS dummy.relation DROP CONSTRAINT IF EXISTS dummy  -- running in PostgreSQL 14.6
> NOTICE:  relation "relation" does not exist, skipping
> OK

It still works like that for me in 14.latest.  Perhaps you installed an
event trigger that is doing something incautious?

            regards, tom lane

Hi Tom,

Thanks for the quick reply.

I expanded the tests by creating AWS RDS PostgreSQL databases from scratch for versions 14.6, 14.7, 15.2 and running
thementioned statements.
 
It's working in 14.6 but fails in both 14.7 and 15.2 with the mentioned issue.

I've also tried 14.7 locally and it's working fine here too so I guess you can safely ignore this bug and I'll be
raisingan issue with AWS support.
 

Regards,
João

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 09 March 2023 14:51
To: Salavessa, Joao (Senior Developer) <Joao.Salavessa@sky.uk>
Cc: pgsql-bugs@lists.postgresql.org
Subject: [EXTERNAL] Re: PostgreSQL 14.7 "ALTER TABLE IF EXISTS" fails - ERROR: schema/relation "<name>" does not exist

"Salavessa, Joao (Senior Developer)" <Joao.Salavessa@sky.uk> writes:
> In PostgreSQL 14.7 (AWS RDS) the “IF EXISTS” seems to be ignored for the “ALTER TABLE” statement.
> Example – the query “ALTER TABLE IF EXISTS dummy.relation DROP CONSTRAINT IF EXISTS dummy” (when schema dummy doesn’t
exist,or when schema dummy exists but relation doesn’t) works on 14.6 but fails in 14.7.
 
> Few outputs below:

> ALTER TABLE IF EXISTS dummy.relation DROP CONSTRAINT IF EXISTS dummy  -- running in PostgreSQL 14.6
> NOTICE:  relation "relation" does not exist, skipping
> OK

It still works like that for me in 14.latest.  Perhaps you installed an
event trigger that is doing something incautious?

                        regards, tom lane
--------------------------------------------------------------------
This email is from an external source. Please do not open attachments or click links from an unknown or suspicious
origin.Phishing attempts can be reported by using the report message button in Outlook or sending them as an attachment
tophishing@sky.uk. Thank you
 
--------------------------------------------------------------------

Information in this email including any attachments may be privileged, confidential and is intended exclusively for the
addressee.The views expressed may not be official policy, but the personal views of the originator. If you have
receivedit in error, please notify the sender by return e-mail and delete it from your system. You should not
reproduce,distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to
monitorall e-mail communication through our internal and external networks. SKY and the SKY marks are trademarks of Sky
Limitedand Sky International AG and are used under licence.
 

Sky UK Limited (Registration No. 2906991), Sky-In-Home Service Limited (Registration No. 2067075), Sky Subscribers
ServicesLimited (Registration No. 2340150) and Sky CP Limited (Registration No. 9513259) are direct or indirect
subsidiariesof Sky Limited (Registration No. 2247735). All of the companies mentioned in this paragraph are
incorporatedin England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD