Problems with unique key constraint.(CASE INSENSITIVE)

[dycharles]

I'm stack with this problem with some time now.

Here is my problem.

CREATE TABLE category
(
catdb_id bigserial NOT NULL,
catdb_name character varying,
catdb_remarks character varying,
CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
)

INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted

How can i make this two values which i inserted above to be equal? Any solution. Thank you.

[amitpatel66]

I'm stack with this problem with some time now.

Here is my problem.

CREATE TABLE category
(
catdb_id bigserial NOT NULL,
catdb_name character varying,
catdb_remarks character varying,
CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
)

INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted

How can i make this two values which i inserted above to be equal? Any solution. Thank you.

Convert the value to either UPPER or lower case everytime you do an insert because the data inserted in different case will not be the same

[dycharles]

Thanks for the reply.

I already do that. But i want to retain the original values or position that is being typed by the user. What will be my other solution? any suggestion? Thanks again.

[rski]

I'm stack with this problem with some time now.

Here is my problem.

CREATE TABLE category
(
catdb_id bigserial NOT NULL,
catdb_name character varying,
catdb_remarks character varying,
CONSTRAINT catdb_id_pk PRIMARY KEY (catdb_id)
)

INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted

How can i make this two values which i inserted above to be equal? Any solution. Thank you.

Creating index should be enough
CREATE UNIQUE INDEX in_category_cs ON category ((lower(catdb_name)))

[dycharles]

What if the user will insert this three value.
INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dyChaRleS')
---- successfully inserted

How can they be equal in unique constraint or in unique index? Thanks

[rski]

What if the user will insert this three value.
INSERT INTO category(catdb_name) VALUES('DyCharleS')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dycharles')
---successfully inserted
INSERT INTO category(catdb_name) VALUES('dyChaRleS')
---- successfully inserted

How can they be equal in unique constraint or in unique index? Thanks

They are equal because index is using the lower() function.

[dycharles]

Thanks, great help.

More Power