CODEWITHSUNDEEP
postgresqlsqlalchemypostgresql-9.2
tomis
tomis

Reputation: 1971

Postgres: unique on array and varchar column

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

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.

Upvotes: 1

Views: 1788

Answers (1)

zzzeek
zzzeek

Reputation: 75127

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

Upvotes: 1

Related Questions