The following bug has been logged online:
Bug reference: 4741
Logged by: Brice Maron & Duchesne P-A
Email address: bma...@naturalsciences.be
PostgreSQL version: 8.3.7
Operating system: i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20071124 (Red Hat 4.1.2-42)
Description: Domain constraint violation with trigger
Details:
Hi,
we've got a problem with a NOT Null constraint on a domain in a trigger.
When, in a trigger, we put a null into the not nullable domain, postgresql
doesn't throw an error.
We extracted the simplified test case below from our application:
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test_function() RETURNS TRIGGER AS $function$
BEGIN
SELECT NULL INTO new.text;
RETURN new;
END;
$function$ LANGUAGE plpgsql;
DROP TABLE IF EXISTS test;
-- Table without a domain throws an error
CREATE TABLE test(id integer not null, text varchar default '' not null);
CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE test_function();
INSERT INTO test VALUES(1);
-- ERROR: null value in column "text" violates not-null constraint
DROP TABLE IF EXISTS test;
-- Table with the same trigger AND a domain let a null value go into the
table
CREATE DOMAIN test_domain AS varchar DEFAULT '' NOT NULL;
CREATE TABLE test(id integer not null, text test_domain);
CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE test_function();
INSERT INTO test VALUES(1);
-- INSERT 0 1
SELECT * FROM test WHERE text IS null;
-- id | text
-- ----+------
-- 1 |
-- (1 row)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
