CODEWITHSUNDEEP
postgresqlsql-insert
James Wanyoike
James Wanyoike

Reputation: 1

How do I use case expression then use an insert statement in the else part of the statement in postgres sql

I have below code but throws a syntax error in the else part.

INSERT INTO tbl1 ("Name") 
values 
((SELECT CASE 
           WHEN EXISTS(select "OccupationID" from "tbloccupations" where "Name" = '-') 
              THEN (select "OccupationID" from "tbloccupations" where "Name" = '-') 
           ELSE (INSERT INTO "tbloccupations" ("Name") VALUES ('-') RETURNING "OccupationID") 
           END)
);

This the actual query that is supposed to run thanks for your help:

ALTER TABLE tblcustomers DISABLE TRIGGER ALL;
INSERT INTO public.tblcustomers
("CustomerID","Surname","OtherNames","Sex","DateOfBirth","OccupationID","Residence","PostalAddress","TownID",
 "NextOfKin","NextOfKinRelationship","NextOfKinContact","EmailAddress","DateRegistered","IDTypeID","IDNumber",
 "OutPatientNo","InPatientNo","Telephone1","Telephone2","PostalCode","Note","ReferenceNo","NationalityID",
 "RegisteredAtCompanyBranchID","RegisteredBySysUID","IsActive") 
 VALUES('-2','-','-','0','12/12/2012 12:00:00 AM',
        (SELECT CASE WHEN EXISTS(select "OccupationID" from "tbloccupations" where "Name" = '-') 
         THEN(select "OccupationID" from "tbloccupations" where "Name" = '-') 
         ELSE(INSERT INTO "tbloccupations" ("Name") VALUES ('-') RETURNING "OccupationID") END),
        '-','-',
        (SELECT CASE WHEN EXISTS(select "TownID" from "tbltowns" where "Name" = '-') THEN
         (select "TownID" from "tbltowns" where "Name" = '-') 
         ELSE(INSERT INTO "tbltowns" ("Name") VALUES ('-') RETURNING "TownID") END),
        '-','-','-','-','9/10/2014 12:00:00 AM','0','-','0','0','-','-','-','-','-','0','0','0','1') 
        ON CONFLICT DO NOTHING;ALTER TABLE tblcustomers ENABLE TRIGGER ALL;

Upvotes: 0

Views: 95

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

If I understand your logic correctly, you want to insert the OccupationID when the name is a -, otherwise you want to insert -:

INSERT INTO tbl1 (Name)
SELECT CASE WHEN Name = '-' THEN OccupationID ELSE '-' END
FROM tbloccupations
RETURNING OccupationID;

I'm not sure if it's possible to use subqueries in the VALUES clause as you are currently doing it, but in any case your problem better fits an INSERT INTO ... SELECT.

Note: If the OccupationID column be numeric, then you'll have to cast it to text using something like OccupationID::text, in order to make the CASE expression work.

Upvotes: 1

Related Questions