Return to Revisions

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment.

###Uniqueness and arrays Arrays would have to be sorted consistently to make sense in any unique arrangement relying on the = operator because '{1,2}' <> '{2,1}'. I suggest look-up tables for genre, tag and origin with serial PK and unique entries, which allow fuzzy search for array elements. Then:

• either implement fully normalized n:m relationships that also provide referential integrity. Uniqueness of each set of references is harder to establish, you could use a MATERIALIZE VIEW (MV) with aggregated arrays as stepping stone.

• How to implement a many-to-many relationship in PostgreSQL?

• or operate with sorted arrays of FK references (which cannot yet be supported with FK keys). Tools from the additional module intarray may come in handy:

• Compare arrays for equality, ignoring order of elements

Either way, working with arrays directly or with a normalized schema and a materialized view, searching can be very efficient with the right index and operators:

• Can PostgreSQL index array columns?

###Alternative with functional unique index

The other option would be functional unique index (similar to what @Dave commented). But I would use a uuid data type to optimize index size and performance.

The cast from array to text is not IMMUTABLE (due to its generic implementation):

• Indexing an array for full text search

Hence you need a little helper function to declare it immutable:

CREATE OR REPLACE FUNCTION f_movie_uuid(_title text
                                      , _runtime int2
                                      , _released_in int2
                                      , _genres text[]
                                      , _tags text[]
                                      , _origin text[])
  RETURNS uuid LANGUAGE sql IMMUTABLE AS  -- faking IMMUTABLE
'SELECT md5(_title || _runtime::text || _released_in::text
         || _genres::text || _tags::text || _origin::text)::uuid';

Use it for the index definition:

CREATE UNIQUE INDEX movies_uni_idx
ON movies (f_movie_uuid(title,runtime,released_in,genres,tags,origin));

SQL Fiddle.

More Details:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
• Convert hex in text representation to decimal number

You might use the generated UUID as PK, but I would still rather use the serial column with it's 4 bytes, which is simple and cheap for FK references and all other purposes. The UUID would be a great option for distributed systems that need to generate a PK independently. Or for very huge tables, but there aren't nearly enough movies in our solar system for that.

###Pros and Cons The constraint is implemented with a UNIQUE index on the involved columns. Put relevant columns in the constraint definition first and you have useful index for other purposes as collateral benefit.

There are other small benefits:

• How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

The functional unique index is (potentially much) smaller in size, which can make it substantially faster. If your columns are not too big, the difference won't be much. There is also the small overhead cost for the calculation.

Concatenating all columns can introduce false positives ('foo ' || 'bar' = 'foob ' || 'ar', but that seems very unlikely for this case. Typos are so much more likely that you can safely ignore it here.

Aside:
If you are using Postgres 9.4 or later consider jsonb instead of json.