postgres.email
❮

could not find replacement targetlist entry for attno -6

  • Jump to comment-1
    jian he<jian.universality@gmail.com>
    Jan 27, 2026, 8:27 AM UTC
    Hi.
    While reviewing ON CONFLICT DO SELECT, I found an elog(ERROR) behavior, which
    may a bug. It is also unclear whether ``INSERT tableoid::regclass`` is
    expected to
    return "hat_data" or "hats". The following test case is copied from
    src/test/regress/sql/rules.sql
    ----------
    DROP TABLE IF EXISTS hats, hat_data;
CREATE TABLE hats (hat_name char(10) primary key, hat_color char(10));
CREATE TABLE hat_data (hat_name char(10), hat_color char(10));
CREATE UNIQUE INDEX hat_data_unique_idx on hat_data (hat_name COLLATE
"C" bpchar_pattern_ops);
CREATE RULE hat_upsert AS ON INSERT TO hats
DO INSTEAD
INSERT INTO hat_data VALUES (
NEW.hat_name,
NEW.hat_color)
ON CONFLICT (hat_name)
DO UPDATE
SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color
WHERE excluded.hat_color <>  'forbidden' AND hat_data.* != excluded.*
RETURNING *;

INSERT INTO hats VALUES ('h8', 'black') RETURNING *, tableoid::regclass;
ERROR:  could not find replacement targetlist entry for attno -6
    --
    jian
    https://www.enterprisedb.com/
    View in PostgreSQL Archives →
    • Jump to comment-1
      Dean Rasheed<dean.a.rasheed@gmail.com>
      Jan 27, 2026, 11:55 AM UTC
      On Tue, 27 Jan 2026 at 08:27, jian he <jian.universality@gmail.com> wrote:

      While reviewing ON CONFLICT DO SELECT, I found an elog(ERROR) behavior, which
      may a bug.
      Hmm, AFAICS this has never worked.
      Here's a simpler example (taking ON CONFLICT out of the equation,
      since it has nothing to do with that):
      DROP TABLE IF EXISTS foo, bar;
CREATE TABLE foo (a int);
CREATE TABLE bar (a int);
CREATE RULE foo_ins AS ON INSERT TO foo
DO INSTEAD INSERT INTO bar VALUES (new.a) RETURNING *;

INSERT INTO foo VALUES (1) RETURNING tableoid, a;
      This gives the same error, because tableoid (or any other system
      attribute) is not in the rule's RETURNING list, and so cannot be
      returned by the outer query. Also, there's no way for the user to add
      support for returning system attributes, because the rule's RETURNING
      list must have the same number (and types) of (user) columns as the
      relation the rule is on.
      Arguably then, this is correct behaviour, within the limitations of
      the rules system, although the error message is not very friendly.
      Given the near-deprecated nature of rules, it's also arguable that
      it's not worth trying to fix this, except perhaps to improve the error
      message.
      Regards,
      Dean
      View in PostgreSQL Archives →