pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with unique index
3+ messages / 3 participants
[nested] [flat]
* Problem with unique index
@ 2002-01-13 20:23 Sharon Cowling <sharon.cowling@sslnz.com>
0 siblings, 2 replies; 3+ messages in thread
From: Sharon Cowling @ 2002-01-13 20:23 UTC (permalink / raw)
To: pgsql-sql
I have a table which I need to have 2 unique indexes on: Drivers licence and Firearms Licence, my problem is, not everyone has a licence so I need the ability for the column to not be entered.
However I get an error that I cannot insert a duplicate key into the unique index.
Below is the view, rule and insert.
As you can see I am excluding the 2 licences from the view, rule and insert, although they exist in the table.
This is because in the application I test to see if it has been entered and then run a different sql insert depending on the data entered.
I really need these to be unique values, I was sure that a null value would be ok, help please!
CREATE VIEW addperson_view
AS SELECT
p.person_id, p.firstname, p.lastname, p.dob, p.street, p.suburb, p.city, p.homephone, p.workphone, p.mobile, p.type, p.date_approved, p.approved_by, p.vehicle_type, p.vehicle_rego, p.notes, fp.location1, fp.location2, fp.location3
FROM person p, forest_person fp;
CREATE RULE addperson_rule AS ON INSERT TO addperson_view
DO INSTEAD(
INSERT INTO person
VALUES
(new.person_id, new.firstname, new.lastname, new.dob, new.street, new.suburb, new.city, new.homephone, new.workphone, new.mobile, new.type, new.date_approved, new.approved_by, new.vehicle_type, new.vehicle_rego, new.notes);
INSERT INTO forest_person
VALUES
(new.person_id,new.location1,new.location2,new.location3);
);
user=>insert into
addperson_view
(person_id, firstname, lastname, dob, street, suburb, city, homephone, workphone, mobile, type, date_approved, approved_by, vehicle_type, vehicle_rego, notes, location1, location2, location3)
values(nextval('seq_person_id'), 'Sharon', 'Cowling', '16-10-78', 'My Street', 'My Suburb', 'My City', '5555568','5555567','0255556674','Other', '09-01-02', 'test', 'Nissan','AAA121','Test Data Notes','Forest1','Forest2','Forest3');
ERROR: Cannot insert a duplicate key into unique index person_drivers_licence_key
(It also errors on firearms licence)
Table Structure:
CREATE TABLE person(
person_id INT NOT NULL,
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
dob date NOT NULL,
street VARCHAR(50) NOT NULL,
suburb VARCHAR(50),
city VARCHAR(50) NOT NULL,
homephone INT,
workphone INT,
mobile INT,
type VARCHAR(30) NOT NULL,
date_approved DATE NOT NULL,
approved_by VARCHAR(50) NOT NULL,
vehicle_type VARCHAR(50),
vehicle_rego VARCHAR(6),
drivers_licence VARCHAR(10) UNIQUE,
firearms_licence VARCHAR(20) UNIQUE,
notes VARCHAR(80),
PRIMARY KEY (person_id));
Regards,
Sharon Cowling
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with unique index
@ 2002-01-13 20:50 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Sharon Cowling <sharon.cowling@sslnz.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-01-13 20:50 UTC (permalink / raw)
To: Sharon Cowling <sharon.cowling@sslnz.com>; +Cc: pgsql-sql
On Sun, 13 Jan 2002, Sharon Cowling wrote:
> CREATE RULE addperson_rule AS ON INSERT TO addperson_view DO INSTEAD(
> INSERT INTO person VALUES (new.person_id, new.firstname, new.lastname,
> new.dob, new.street, new.suburb, new.city, new.homephone,
> new.workphone, new.mobile, new.type, new.date_approved,
> new.approved_by, new.vehicle_type, new.vehicle_rego, new.notes);
I may be confused, but if you don't want to specify values for
drivers_licence and firearms_licence in the above insert, aren't you doing
to need to list the columns since notes comes after those columns?
> Table Structure:
> CREATE TABLE person(
> person_id INT NOT NULL,
> firstname VARCHAR(25) NOT NULL,
> lastname VARCHAR(25) NOT NULL,
> dob date NOT NULL,
> street VARCHAR(50) NOT NULL,
> suburb VARCHAR(50),
> city VARCHAR(50) NOT NULL,
> homephone INT,
> workphone INT,
> mobile INT,
> type VARCHAR(30) NOT NULL,
> date_approved DATE NOT NULL,
> approved_by VARCHAR(50) NOT NULL,
> vehicle_type VARCHAR(50),
> vehicle_rego VARCHAR(6),
> drivers_licence VARCHAR(10) UNIQUE,
> firearms_licence VARCHAR(20) UNIQUE,
> notes VARCHAR(80),
> PRIMARY KEY (person_id));
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with unique index
@ 2002-01-13 20:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sharon Cowling <sharon.cowling@sslnz.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-01-13 20:53 UTC (permalink / raw)
To: Sharon Cowling <sharon.cowling@sslnz.com>; +Cc: pgsql-sql
Sharon Cowling <sharon.cowling@sslnz.com> writes:
> INSERT INTO person
> VALUES
> (new.person_id, new.firstname, new.lastname, new.dob, new.street, new.suburb, new.city, new.homephone, new.workphone, new.mobile, new.type, new.date_approved, new.approved_by, new.vehicle_type, new.vehicle_rego, new.notes);
This is going to insert into the first N columns of person, and if I'm
counting correctly that means "new.notes" is going to wind up in the
drivers_licence column. Perhaps you want to call out the target columns
explicitly in the INSERT command.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-01-13 20:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-13 20:23 Problem with unique index Sharon Cowling <sharon.cowling@sslnz.com>
2002-01-13 20:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-13 20:53 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox