CODEWITHSUNDEEP
postgresqlinsertcaseconcatenation
GabSP
GabSP

Reputation: 53

PostgreSQL Insert into with case and concat

I'm trying to make an insert trigger that will take two string fields (x and y) from table A and concatenate them into a single field in table B. If y is not null, I want to add a ' - ' between x and y when I concatenate. So far, my code looks like that:

BEGIN
    INSERT INTO B(xy,z)
        SELECT y,z,
            CASE WHEN y IS NOT NULL then concat('some prefix',x,' - ',y)
                ELSE concat('some prefix',x)
            END
        FROM(SELECT NEW.x, NEW.y NEW.z) as n;
        WHERE [some conditions]
    RETURN NEW;
END;

As I understand it, it should be placing 'some prefix x - y' or 'some prefix x' in the xy field of table B and z from A in the z of B, but I get the error "INSERT has more expressions than target columns". Does anybody know what I'm doing wrong?

Upvotes: 4

Views: 6554

Answers (1)

Mureinik
Mureinik

Reputation: 311948

Your outer select list has three terms - y, z and the case expression, just remove y and reorder the other two and you should be OK:

INSERT INTO B(xy,z)
SELECT
    CASE WHEN y IS NOT NULL then concat('some prefix',x,' - ',y)
        ELSE concat('some prefix',x)
    END, -- First expression, goes into b.xy
    z -- SEcond expression, goes into b.z
FROM(SELECT NEW.x, NEW.y NEW.z) as n;
WHERE [some conditions]

Upvotes: 4

Related Questions