1

I would like to ask if it is possible to set UNIQUE on array column - I need to check array items if are uniqued.

Secondly, I wish to have also second column to be included in this.

To imagine what I need, I'm including example: imagine, you have entries with domains and aliases. Column domain is varchar having main domain in it, aliases is array which can be empty. As logical, nothing in column domain can be in aliases as well as right opposite.

If there is any option how to do it, I would be glad for showing how. And the best will be to include help how to do it in sqlalchemy (table declaration, using in TurboGears).

  • Postgresql: 9.2
  • sqlalchemy: 0.7

UPDATE:

I have found, how to do multi-column unique in sqlalchemy, however it does not work on array:

client_table = Table('client', metadata,
    Column('id', types.Integer, autoincrement = True, primary_key = True),
    Column('name', types.String),
    Column('domain', types.String),
    Column('alias', postgresql.ARRAY(types.String)),
    UniqueConstraint('domain', 'alias', name = 'domains')
)

Then desc:

wb=# \d+ client
                                            Table "public.client"
 Column |        Type         |                      Modifiers                      | Storage  | Description 
--------+---------------------+-----------------------------------------------------+----------+-------------
 id     | integer             | not null default nextval('client_id_seq'::regclass) | plain    | 
 name   | character varying   |                                                     | extended | 
 domain | character varying   | not null                                            | extended | 
 alias  | character varying[] |                                                     | extended | 
Indexes:
    "client_pkey" PRIMARY KEY, btree (id)
    "domains" UNIQUE CONSTRAINT, btree (domain, alias)

And select (after test insert):

wb=# select * from client;
 id | name  |    domain     |          alias           
----+-------+---------------+--------------------------
  1 | test1 | www.test.com  | {www.test1.com,test.com}
  2 | test2 | www.test1.com | 
  3 | test3 | www.test.com  | 

Thanks in advance.

4
  • UNIQUE CONSTRAINTs defined over columns that are not defined as NOT NULL cannot guarantee uniqueness. Commented Jul 13, 2013 at 19:10
  • Hm, thank you for good point (domain definitelly should be as NOT NULL), however, maybe I have missed something, but NULL values are ommited from unique checking, aren't they? I think I was using this like that in MySQL... Commented Jul 13, 2013 at 21:41
  • No, NULLs are unknowns so UNIQUE cannot apply to columns that are not defined as NOT NULL. I've put together a simple SQL Fiddle to demonstrate this. To have the column be nullable, but still have a UNIQUE constraint, you would need to COALESCE the nullable column to a value. Eg. COALESCE(domain,'xyz') Commented Jul 13, 2013 at 23:52
  • Pity that you have not insert that as a answer, would give you as accepted... After some reading and with your help, I found out that 1. I'm stupind cause unique on more columns is not working for values separately but on combination 2. nullable columns does not respect unique as expected 3. this has to be done programicaly (which is not so big problem, I was thinking this will be "to be sure")... I would leave domain as unique as this is more important column and in case of having same value in alias done by some stupid man here, I would ignore that... Commented Jul 14, 2013 at 11:19

1 Answer 1

1

figure out how to do this in pure Postgresql syntax, then use DDL to emit it exactly.

1
  • Thank you for second part of question, would be useful. However, I this don't know how to do it in postgres at all... Commented Jul 13, 2013 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.