ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

From: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types
Date: 2015-08-03 15:53:08
Message-ID: CAEzk6fdzJ3xYQZGbcuYM2rBd2BuDkUksmK=mY9UYYDugg_GgZg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

We've come across a weirdness with ON CONFLICT, where UPSERTing a smallint
value produces an error:

db=# INSERT INTO brokentab(id, k1,k2,k3,k4,k5,k6,k7, smallval) VALUES
(5,0,0,0,1,0,1,0, 0) ON CONFLICT (id, k1,k2,k3,k4,k5,k6,k7) DO UPDATE SET
smallval=EXCLUDED.smallval;
ERROR: attribute 29 has wrong type
DETAIL: Table has type integer, but query expects smallint.

If you change the SET to smallval=0 the problem goes away, although using
SET smallval=CAST(EXCLUDED.smallval AS smallint) - or indeed AS int -
doesn't help at all.

If I create a copy of the table using

CREATE mytab (LIKE brokentab INCLUDING ALL);
INSERT INTO mytab SELECT * FROM brokentab;

the new table does not exhibit the same problem (so I'm assuming it's not
easily reproducible and giving you a creation script isn't going to help).

VACUUM FULL on the table makes no difference.

Is there anything you guys can suggest that I can do to help narrow down
the problem?

Linux Centos 6.5, kernel 2.6.32-431.el6.i686, pgsql alpha1, built from
source using gcc 4.4.7.

Thanks

Geoff

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2015-08-03 16:00:43 Re: Planner debug views
Previous Message Alvaro Herrera 2015-08-03 15:52:41 Re: Minimum tuple threshold to decide last pass of VACUUM