Can PostgreSQL have a uniqueness constraint on array elements?

Question

I'm trying to come up with a PostgreSQL schema for host data that's currently in an LDAP store. Part of that data is the list of hostnames a machine can have, and that attribute is generally the key that most people use to find the host records.

One thing I'd like to get out of moving this data to an RDBMS is the ability to set a uniqueness constraint on the hostname column so that duplicate hostnames can't be assigned. This would be easy if hosts could only have one name, but since they can have more than one it's more complicated.

I realize that the fully-normalized way to do this would be to have a hostnames table with a foreign key pointing back to the hosts table, but I'd like to avoid having everybody need to do joins for even the simplest query:

select hostnames.name,hosts.*
  from hostnames,hosts
 where hostnames.name = 'foobar'
   and hostnames.host_id = hosts.id;

I figured using PostgreSQL arrays could work for this, and they certainly make the simple queries simple:

select * from hosts where names @> '{foobar}';

When I set a uniqueness constraint on the hostnames attribute, though, it of course treats the entire list of names as the unique value instead of each name. Is there a way to make each name unique across every row instead?

If not, does anyone know of another data-modeling approach that would make more sense?

Answer 1

The righteous path

You might want to reconsider normalizing your schema. It is not necessary for everyone to "join for even the simplest query". Create a VIEW for that.

Table could look like this:

CREATE TABLE hostname (   hostname_id serial PRIMARY KEY , host_id     int  REFERENCES host(host_id) ON UPDATE CASCADE ON DELETE CASCADE , hostname    text UNIQUE ); 

The surrogate primary key hostname_id is optional. I prefer to have one. In your case hostname could be the primary key. But many operations are faster with a simple, small integer key. Create a foreign key constraint to link to the table host.
Create a view like this:

CREATE VIEW v_host AS SELECT h.*      , array_agg(hn.hostname) AS hostnames --   , string_agg(hn.hostname, ', ') AS hostnames  -- text instead of array FROM   host h JOIN   hostname hn USING (host_id) GROUP  BY h.host_id;   -- works in v9.1+ 

Starting with pg 9.1, the primary key in the GROUP BY covers all columns of that table in the SELECT list. The release notes for version 9.1:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause

Queries can use the view like a table. Searching for a hostname will be much faster this way:

SELECT * FROM   host h JOIN   hostname hn USING (host_id) WHERE  hn.hostname = 'foobar'; 

^{Provided you have an index on host(host_id), which should be the case as it should be the primary key. Plus, the UNIQUE constraint on hostname(hostname) implements the other needed index automatically.}

In Postgres 9.2+ a multicolumn index would be even better if you can get an index-only scan out of it:

CREATE INDEX hn_multi_idx ON hostname (hostname, host_id); 

Starting with Postgres 9.3, you could use a MATERIALIZED VIEW, circumstances permitting. Especially if you read much more often than you write to the table.

The dark side (what you actually asked)

If I can't convince you of the righteous path, I'll assist on the dark side, too. I am flexible. :)

Here is a demo how to enforce uniqueness of hostnames. I use a table hostname to collect hostnames and a trigger on the table host to keep it up to date. Unique violations raise an exception and abort the operation.

CREATE TABLE host(hostnames text[]); CREATE TABLE hostname(hostname text PRIMARY KEY);  --  pk enforces uniqueness 

Trigger function:

CREATE OR REPLACE FUNCTION trg_host_insupdelbef()   RETURNS trigger AS $func$ BEGIN -- split UPDATE into DELETE & INSERT IF TG_OP = 'UPDATE' THEN    IF OLD.hostnames IS DISTINCT FROM NEW.hostnames THEN  -- keep going    ELSE RETURN NEW;  -- exit, nothing to do    END IF; END IF;  IF TG_OP IN ('DELETE', 'UPDATE') THEN    DELETE FROM hostname h    USING  unnest(OLD.hostnames) d(x)    WHERE  h.hostname = d.x;     IF TG_OP = 'DELETE' THEN RETURN OLD;  -- exit, we are done    END IF; END IF;  -- control only reaches here for INSERT or UPDATE (with actual changes) INSERT INTO hostname(hostname) SELECT h FROM   unnest(NEW.hostnames) h;  RETURN NEW; END $func$ LANGUAGE plpgsql; 

Trigger:

CREATE TRIGGER host_insupdelbef BEFORE INSERT OR DELETE OR UPDATE OF hostnames ON host FOR EACH ROW EXECUTE PROCEDURE trg_host_insupdelbef(); 

SQL Fiddle with test run.

Use a GIN index on the array column host.hostnames and array operators to work with it:

• Why isn't my PostgreSQL array index getting used (Rails 4)?
• Check if any of a given array of values are present in a Postgres array

Answer 2

In case anyone still needs what was in the original question:

CREATE TABLE testtable(
    id serial PRIMARY KEY,
    refs integer[],
    EXCLUDE USING gist( refs WITH && )
);

INSERT INTO testtable( refs ) VALUES( ARRAY[100,200] );
INSERT INTO testtable( refs ) VALUES( ARRAY[200,300] );

and this would give you:

ERROR:  conflicting key value violates exclusion constraint "testtable_refs_excl"
DETAIL:  Key (refs)=({200,300}) conflicts with existing key (refs)=({100,200}).

Checked in Postgres 9.5 on Windows.

Note that this would create an index using the operator &&. So when you are working with testtable, it would be times faster to check ARRAY[x] && refs than x = ANY( refs ).

P.S. Generally I agree with the above answer. In 99% cases you'd prefer a normalized schema. Please try to avoid "hacky" stuff in production.