1

Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table?

Assume the following table that serves as a source for the custom type:

CREATE TABLE source
(
  id character varying(20) NOT NULL,
  weight smallint NOT NULL,
  CONSTRAINT source_pkey PRIMARY KEY (id),
  CONSTRAINT source_weight_check CHECK (weight > 0 AND weight < 6)
)

Now, I intend to create the following data table:

CREATE TABLE final
(
  name character varying(20) NOT NULL,
  data source NOT NULL,
  CONSTRAINT final_pkey PRIMARY KEY (name),
)

When inserting records into the final table, the check constraint will not be observed.

Requirements:

  1. I'd like to avoid creating my own type based of source, because when source changes I'll be forced to remember to also update the custom type.
  2. I realize that table-based custom types obey domain check constraints. But, on my particular case, the weight field in the source database is ill-suited for a domain. Its check constraints varies from table to table. FYI, it's a value used in calculating weighted averages.
  • Why not simply use a plain foreign key instead of defining a table as the attribute of another table (I didn't even know this was possible). – a_horse_with_no_name Dec 20 '14 at 11:17
  • The data field in the final table is just a composite field that shares the structure of the source table. It has no link with the source table. In Postgres any table definition is a data type. – Aperneto Dec 20 '14 at 13:45
0

A record will only be null if all of its fields are null or if the record itself is null. If you want to check if any of the fields is null then check one by one:

create table final
(
    name character varying(20) not null,
    data source check (
        (data).id is not null and (data).weight is not null
    ),
    constraint final_pkey primary key (name)
);

insert into final (name, data) values ('a', null);
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, null).

insert into final (name, data) values ('a', (null, null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (,)).

insert into final (name, data) values ('a', ('1', null));
ERROR:  new row for relation "final" violates check constraint "final_data_check"
DETAIL:  Failing row contains (a, (1,)).

insert into final (name, data) values ('a', ('1', 1));
INSERT 0 1
  • So essentially I need to replicate the check constraint on the table that includes the composite type? – Aperneto Dec 21 '14 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.