Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 676
GabSP Avatar asked Oct 16 '25 10:10

GabSP


1 Answers

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]
like image 135
Mureinik Avatar answered Oct 19 '25 00:10

Mureinik



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!