Odd 'except' and 'default' interaction behavior

From: phil(at)netroedge(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: rob_fightmaster(at)ren(dot)netroedge(dot)com
Subject: Odd 'except' and 'default' interaction behavior
Date: 2001-03-24 00:11:34
Message-ID: 20010323161134.Q10211@Stimpy.netroedge.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


We noticed a strange behavior involving 'except' and 'default'
interaction. Example from psql:

wpbb=# \d robtest
Table "robtest"
Attribute | Type | Modifier
-----------+---------+-------------
id | integer | default '1'
userid | integer |
articleid | integer |

wpbb=# insert into robtest (userid,articleid) select '1'::int4,'2'::int4 except select '2'::int4,'5'::int4;
ERROR: Each UNION | EXCEPT | INTERSECT query must have the same number of columns.

wpbb=# alter table robtest alter column id drop default;
ALTER

wpbb=# insert into robtest (userid,articleid) select '1'::int4,'2'::int4 except select '2'::int4,'5'::int4;
INSERT 306530 1

Notice that error regarding the usage of except and the # of columns.
Strange. Yet, that exact same query works fine when we remove a
default on a column on the table which isn't being referenced.

I'm not sure if this is a bug, a poorly written error message, or my
ignorance. :') We are using Postgresql 7.0.3 release under Linux:

phil=# select version();
version
----------------------------------------------------------------
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3 (1 row)

Thanks!!

Phil

--
Philip Edelbrock -- IS Manager -- Edge Design, Corvallis, OR
phil(at)netroedge(dot)com -- http://www.netroedge.com/~phil
PGP F16: 01 D2 FD 01 B5 46 F4 F0 3A 8B 9D 7E 14 7F FB 7A

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Eisentraut 2001-03-24 10:30:42 Re: Tests randomly failed
Previous Message Giles Lean 2001-03-23 23:05:54 Re: NetBSD 1.4.2/i386 needs to link psql with -ltermcap