Return to Answer

• Indexing an array for full text search

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

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

• 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 constraints). Tools from the additional module intarray may come in handy:

• Compare arrays for equality, ignoring order of elements

• Can PostgreSQL index array columns?

• Indexing an array for full text search

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

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

• 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 constraints). Tools from the additional module intarray may come in handy:

• Compare arrays for equality, ignoring order of elements

• Can PostgreSQL index array columns?

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.

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

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

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.

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

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

###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.

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:

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

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

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

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

There are other specific benefits, here is a list:

###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 constraints). 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?

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