Postgresql – Force some composite type fields to be unique

postgresql

I want to create multiple image paths based on the language and size of the image. I decided to CREATE TYPE and then use it in the table.

CREATE TABLE asset (
  id bigserial not null constraint primary key,
  main_image img[]
);

CREATE TYPE img AS (
  lang varchar(2) not null,
  size varchar(32) not null,
  img_path varchar(255) not null
);

How do I make something like this: unique(lang, size) for my composite type? I want this combo to be unique.

How do I restrict any inserts that try to add an img_path for lang & size combo that already has img_path defined? I have tried to CREATE DOMAIN, but it seems like it is not intended for it.

Best Answer

First of all, you can't add constraints to types therefore the type definition you've provided will error out.

Domains as you've already observed won't work since they can only accomodate a single type at a go rather than a composition of types.

You also don't have to add a not null constraint to a primary key - it's redundant, Postgres by default disallows null values for primary key columns.

The best approach will be to normalize the table asset so that you can maintain the restrictions you require since both domain definitions and type defintions can't accomodate these restrictions.

For the sake of completion, here's a suggestion for the table defintions.

Given that an asset has multiple images but an image can only belong to one asset, a one-to-many relationship captures this perfectly as shown below. Furthermore, given how your code is currently structured, different assets can have the same (lang, size) combo. Therefore, the unique constraint is added across both the asset_id, lang and size

create table asset (
  id bigserial primary key,
  /* other asset columns ... */
);

create table img (
    asset_id bigint references asset(id) not null,
    lang varchar(2) not null,
    size varchar(32) not null,
    img_path varchar(255) not null,
    unique(asset_id, lang, size)
);

Note, the unique constraint creates an index hence the checks will be fast. Therefore, as expected, the following insert fails:

insert into img
values 
    (1, 'en', '32 kb', 'bar path'),
    (1, 'en', '32 kb', 'foo path');
Related Question