Amazon RedShift: Unique Column not being honored
amazon-redshift, amazon-web-services
Solution
Figured out the problem
Amazon RedShift does not enforce constraints...
As explained here http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html
They said they may get around to changing it at some point.
NEW 11/21/2013 RDS has added support for PostGres, if you need unique and such an postgres rds instance is now the best way to go.
Problem
I use the following query to create my table. ``` create table t1 (url varchar(250) unique); ``` Then I insert about 500 urls, twice. I am expecting that the second time I had the URLs that no new entries show up in my table, but instead my count value doubles for: ``` select count(*) from t1; ``` What I want is that when I try and add a url that is already in my table, it is skipped. Have I declared something in my table deceleration incorrect? I am using RedShift from AWS. Sample ``` urlenrich=# insert into seed(url, source) select 'http://www.google.com', '1'; INSERT 0 1 urlenrich=# select * from seed; url | wascrawled | source | date_crawled -----------------------+------------+--------+-------------- http://www.google.com | 0 | 1 | (1 row) urlenrich=# insert into seed(url, source) select 'http://www.google.com', '1'; INSERT 0 1 urlenrich=# select * from seed; url | wascrawled | source | date_crawled -----------------------+------------+--------+-------------- http://www.google.com | 0 | 1 | http://www.google.com | 0 | 1 | (2 rows) ``` Output of \d seed urlenrich=# \d seed ``` Table "public.seed" Column | Type | Modifiers --------------+-----------------------------+----------- url | character varying(250) | wascrawled | integer | default 0 source | integer | not null date_crawled | timestamp without time zone | Indexes: "seed_url_key" UNIQUE, btree (url) ```