Precedence of a TRIGGER vs. a CHECK on a column - Mailing list pgsql-general

Hi,

I've got a table:

<code language="SQL">
CREATE TABLE "common"."dynamic_enum"
  (
        "pk_id"         integer                         DEFAULT
nextval('"common"."pw_seq"')
    ,   "enum_type"     common.non_empty_name
    ,   "value"         integer             NOT NULL    DEFAULT
nextval('"common"."de_local_seq"')
    ,   "name"          common.not_all_digits
    ,   "display_name"  varchar(256)
    ,   "description"   varchar(4000)
    ,   "sort_order"    common.sort_order_type
    ,   "is_internal"   boolean             NOT NULL    DEFAULT false
    ,   LIKE "common"."usage_tracking_columns" INCLUDING DEFAULTS
  )
  WITHOUT OIDS
  ;
</code>

Where common.non_empty_name is defined as:

<code language="SQL">
CREATE DOMAIN common.non_empty_name AS varchar(256) NOT NULL
     CONSTRAINT Not_Empty CHECK ( VALUE<>'' );
</code>

I'm using COPY to load some data and I want to set the "enum_type" which is
not present in the file which contains the to-be-loaded data.  So, I define
a trigger:

<code language="PL/pgSQL">
CREATE OR REPLACE FUNCTION "merchandise".trg_insert_de_temp()
    RETURNS trigger AS '
BEGIN
    IF ( NEW."enum_type" IS NULL) THEN
        NEW."enum_type" =''group_code'';
    END IF;
    RETURN NEW;
END;
  ' LANGUAGE plpgsql VOLATILE;
</code>
<code language="SQL">
CREATE TRIGGER zz_set_enum_type_temp BEFORE INSERT ON
"common"."dynamic_enum"
    FOR EACH ROW EXECUTE PROCEDURE "merchandise".trg_insert_de_temp();

</code>

But, when I do the COPY I get:

<snip type="psql-output">
psql:load_yurman_merchandise.de.sql:59: ERROR:  domain non_empty_name does
not allow null values
CONTEXT:  COPY dynamic_enum, line 1: "BRACELET  Bracelet"
</snip>

So it seems that the CHECK definied for the non_empty_name domain is being
applied before the trigger is executed.  Yet, it seems that NON NULL
constraints are applied after triggers get called.

Questions:
    1.  Is the just-described ordering accurate?
    2.  Is that intended (e.g., the way it "should" be because of, say, SQL
standard)
    3.  Is there a work-around (short of changing the definition for the
relevant column)?

Thanks,

== Ezra Epstein