0

I know this has been asked and answered but I'm still unable to get this trigger function to insert correctly. I know I have the same number of values being inserted but keep getting the has more expressions than target. The only difference in columns structure, is that namespace2.t1 has one extra column that is not included in namespace1.t1; which is why I can't just SELECT * from namespace2.t1

CREATE 
OR REPLACE FUNCTION upsert_trigger_function() RETURNS TRIGGER AS $$ 
BEGIN
  INSERT INTO
    namespace1.t1 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11) 
    SELECT
(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11) 
    FROM
      namespace2.t1 
      ON CONFLICT (col1) DO 
      UPDATE
      SET
        col2 = namespace2.t1.col2,
        ... 
      WHERE
        namespace2.t1.col1 = col1;
RETURN NEW;
END;
LANGUAGE 'plpgsql';

And my trigger:

CREATE TRIGGER upsert_namespace1_t1 after INSERT 
OR 
UPDATE
  ON namespace2.t2 FOR EACH ROW EXECUTE PROCEDURE upsert_trigger_function();
5
  • Can you try to reproduce this in a fiddle (e.g. dbfiddle.uk/?rdbms=postgres_11) Commented Oct 1, 2019 at 7:36
  • Get rid of the useless (and in this case wrong) parentheses around the column list in the SELECT statement. Commented Oct 1, 2019 at 7:37
  • @a_horse_with_no_name, when I remove that It tells me I have an invalid reference to FROM t1 Commented Oct 1, 2019 at 7:48
  • 1
    That is a different error (and thus question), but you probably want col2 = EXCLUDED.col2, ... in the UPDATE part. Commented Oct 1, 2019 at 7:55
  • @a_horse_with_no_name, cool. Will do. Thank you for the link to the question. I wasn't able to find that answer. Now I understand the return I was getting. Thanks :D Commented Oct 1, 2019 at 8:02

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.