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
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 |