Avatar of renesisx
renesisx

asked on 

How to use CASE and EXISTS?

PostgreSQL question...

I want something like this:
CASE WHEN EXISTS(SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_name = 'FK_ProductPurchased_Customer') THEN
ALTER TABLE "ProductPurchased" DROP CONSTRAINT "FK_ProductPurchased_Customer"
END;

But that's not valid SQL.

Do you know a way to do that with valid PostgreSQL SQL?
DatabasesPostgreSQL

Avatar of undefined
Last Comment
renesisx
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

In SQL you cannot use CASE as a decicion loop, such as...

IF (some criteria) THEN
   'do this
else
   'do that
end if

Instead, use the SQL IF construct (no THEN or END IF required, but good to surround commands with begin..end)
IF EXISTS(SELECT constraint_name FROM information_schema.table_constraints WHERE constraint_name = 'FK_ProductPurchased_Customer')    
   begin
   ALTER TABLE "ProductPurchased" DROP CONSTRAINT "FK_ProductPurchased_Customer"
   end

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

..or..

SELECT @some_variable = constraint_name FROM information_schema.table_constraints WHERE constraint_name = 'FK_ProductPurchased_Customer')  

IF @some_variable IS NOT NULL
   begin
   'do your stuff here
   end
Avatar of renesisx
renesisx

ASKER

Can you do that in PostgreSQL though? As I understood it, it doesn't have an IF statement?
ASKER CERTIFIED SOLUTION
Avatar of dominik-jesiolowski
dominik-jesiolowski

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

My comment was for SQL Server; I didn't read the PostgreSQL requirement of your question.  Please disregard my earlier comments.
Avatar of renesisx
renesisx

ASKER

Thank you Dominik! I wouldn't have figured that one out on my own - it was harder than I imagined.
Avatar of renesisx
renesisx

ASKER

Imhorn: No problem, thanks for trying :)
Databases
Databases

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo