如何使用案例表达式，然后在Postgres SQL中的语句的其他部分中使用插入语句

Question

我的代码下面，但在其他部分中丢下语法错误。

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)
);

这是应该运行的实际查询，感谢您的帮助：

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;

Answer 1

如果我正确理解您的逻辑，则要在名称为-时插入OccupationID，否则您要插入-：

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

我不确定当前这样做时是否可以在VALUES子句中使用子查询，但是无论如何，您的问题更适合INSERT INTO ... SELECT。

注意：如果OccupationID列是数字，那么您必须使用OccupationID::text之类的内容将其施放给文本，以使CASE表达式起作用。