![]() This sounds a bit overcomplicated for such a simple (and popular, I suppose) requirement. This, however, sounds like a really ugly hack.Īlternatively, it should be possible to use CREATE CONSTRAINT TRIGGER and manually check for case-insensitive uniqueness (of course a regular index would still be necessary). One possible workaround would be to create additional column with the same content as my_column, but upper case, updated via a trigger after each update/insert, then create a deferrable unique constraint on this artificial column. If deferrable constraint is not needed, it is as simple as creating unique index with function, e.g.: CREATE UNIQUE INDEX my_unique_index ON sample_table(UPPER(my_column)) ĭeferred constraint check requires creating the constraint explicitly, e.g.: ALTER TABLE sample_tableĪDD CONSTRAINT my_unique_constraint UNIQUE(my_column)Īnd unfortunately it is not possible to use arbitrary functions in unique constraint. Let's assume the following basic table: CREATE TABLE sample_table ( This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule.Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive? That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. However, two null values are never considered equal in this comparison. In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. Time: 75.413 # insert into test (id, country) values (4, 'USA') ĮRROR: duplicate key value violates unique constraint "uniq_test_c"ĭETAIL: Key (country)=(USA) already exists. Time: 78.431 # insert into test (id, country) values (3, 'USA') Time: 77.451 # create unique index uniq_test_c on test(country) where state is null To alter an existing table use the ALTER TABLE syntax you already tried. CREATE TABLE camembert ( handle int, id int, num text, PRIMARY KEY (handle, id, num) ) In the case of a UNIQUE constraint, you probably also want all three columns to be marked NOT NULL. ![]() Time: 254.043 # delete from test where id in (3,4) 4 Answers Sorted by: 503 psql 's inline help: \h ALTER TABLE Also documented in the postgres docs (an excellent resource, plus easy to read, too). You need a multicolumn UNIQUE or PRIMARY KEY constraint. # create unique index uniq_test_c on test(country) where state is null ĮRROR: could not create unique index "uniq_test_c"ĭETAIL: Key (country)=(USA) is duplicated. Set default value for the column, which will case “duplicate key value violates” error.Ģ. Time: 74.928 # insert into test (id, country) values (4, 'USA') ġ. Time: 72.490 # insert into test (id, country) values (3, 'USA') Time: 79.928 # insert into test values (2, 'USA', 'OREGON') ORA-00001: unique constraint (DEV.UNIQ_TEST_CS) violatedīehaviour in PostgreSQL # insert into test values (1, 'USA','SEATTLE') Insert into test (id, country) values (4, 'USA') There must also be matching child-table constraints for all CHECK constraints of the parent, except those marked non-inheritable (that is, created with ALTER. SQL> insert into test (id, country) values (4, 'USA') SQL> insert into test (id, country) values (3, 'USA') SQL> insert into test values (2, 'USA', 'OREGON') Let look at the way how null is handle with unique constraint in place.īehaviour in Oracle SQL> insert into test values (1, 'USA','SEATTLE') ![]() Time: 81.062 # alter table test add constraint uniq_test_cs unique (country, state) Time: 78.866 # alter table test add constraint pk_test_id primary key (id) PostgreSQL # create table test (id numeric(2,0), SQL> alter table test add constraint uniq_test_cs unique (country, state) SQL> alter table test add constraint pk_test_id primary key (id) Oracle SQL> create table test (id number (2,0), An important behavior in PostgreSQL to know about is the duplicate null values do not violate unique constraints.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |