Thread: BUG #2980: check constraint fails on update

The following bug has been logged online:

Bug reference:      2980
Logged by:          Walter Cruz
Email address:      walter.php@gmail.com
PostgreSQL version: 8.1.7
Operating system:   Linux
Description:        check constraint fails on update
Details:

Hi All. A simple table to test:

CREATE TABLE simples
(
  coluna varchar(10)
)
WITHOUT OIDS;

ALTER TABLE simples
  ADD CONSTRAINT simples_coluna_check CHECK (coluna::text = 'girafa'::text
OR coluna::text = 'bode'::text);

INSERT INTO simples (coluna) VALUES ('girafa');
(ok)

INSERT INTO simples (coluna) VALUES ('bode');
(ok)

INSERT INTO simples (coluna) VALUES ('macaco'); (violates constraints, ok).


UPDATE simples set coluna = 'girafa';

FAILS!

ERROR:  attribute 1 has wrong type
DETAIL:  Table has type character varying, but query expects character
varying.

"PostgreSQL 8.1.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)"

Works with 8.1.3 windows, 8.1.4 ubuntu, 8.2 windows.

This is a known issue with 8.1.7. 8.1.8 was released shortly after 8.1.7
  because of this.

Walter Cruz wrote:
-> The following bug has been logged online:
>
> Bug reference:      2980
> Logged by:          Walter Cruz
> Email address:      walter.php@gmail.com
> PostgreSQL version: 8.1.7
> Operating system:   Linux
> Description:        check constraint fails on update
> Details:
>
> Hi All. A simple table to test:
>
> CREATE TABLE simples
> (
>   coluna varchar(10)
> )
> WITHOUT OIDS;
>
> ALTER TABLE simples
>   ADD CONSTRAINT simples_coluna_check CHECK (coluna::text = 'girafa'::text
> OR coluna::text = 'bode'::text);
>
> INSERT INTO simples (coluna) VALUES ('girafa');
> (ok)
>
> INSERT INTO simples (coluna) VALUES ('bode');
> (ok)
>
> INSERT INTO simples (coluna) VALUES ('macaco'); (violates constraints, ok).
>
>
> UPDATE simples set coluna = 'girafa';
>
> FAILS!
>
> ERROR:  attribute 1 has wrong type
> DETAIL:  Table has type character varying, but query expects character
> varying.
>
> "PostgreSQL 8.1.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
> 20061115 (prerelease) (Debian 4.1.1-21)"
>
> Works with 8.1.3 windows, 8.1.4 ubuntu, 8.2 windows.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com