Thread: ERROR: duplicate key violates unique constraint "client_alerts_PK"
Hi all, I am having an issue, where I create a new table, setup the constraints for Auto Increment and Primary Key etc, then manually add a bunch of data using pgadmin similar to the following: insert into clients_alerts (id, client_id, alert_message, alert_complete, date, staff_id) values('1','39','This is some alert text','0','2007-05-04 12:42:05','14'); That works fine, but after say, 30 of those, I have an ID of '30' (of course ;) ) but if I look at the sequence it says that the Current Value is only at 1. This then creates an issue when I want to add data via RealBasic, as it bails until I've tried 30+ times. I can of course go in and manually change the Current Value to 30 but I have an install base of over 30 servers and each has their own levels of data so each number would be different. The question is then: Is there a way to have the Current Value update when I initially add that data with the insert into statements? Any help greatly appreciated... Rob
Hi Rob, In your insert, do not explicitly specify a value for the primary key, instead use NEXTVAL('client_alerts_seq') which will return the next valid value and increment the sequence. i.e:insert into clients_alerts (id, client_id, alert_message, alert_complete, date, staff_id) values(NEXTVAL('client_alerts_seq'),'39','This is some alert text','0','2007-05-04 12:42:05','14'); Laurent On 9/12/07, Robert Starr <rob@surrenderdorothy.com.au> wrote: > Hi all, > > I am having an issue, where I create a new table, setup the constraints > for Auto Increment and Primary Key etc, then manually add a bunch of > data using pgadmin similar to the following: > insert into clients_alerts (id, client_id, alert_message, > alert_complete, date, staff_id) values('1','39','This is some alert > text','0','2007-05-04 12:42:05','14'); > > That works fine, but after say, 30 of those, I have an ID of '30' (of > course ;) ) but if I look at the sequence it says that the Current > Value is only at 1. > > This then creates an issue when I want to add data via RealBasic, as it > bails until I've tried 30+ times. I can of course go in and manually > change the Current Value to 30 but I have an install base of over 30 > servers and each has their own levels of data so each number would be > different. > > The question is then: Is there a way to have the Current Value update > when I initially add that data with the insert into statements? > > Any help greatly appreciated... > > Rob > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Laurent Yaish a écrit : > Hi Rob, > > In your insert, do not explicitly specify a value for the primary key, > instead use NEXTVAL('client_alerts_seq') which will return the next > valid value and increment the sequence. > > i.e: > insert into clients_alerts (id, client_id, alert_message, > alert_complete, date, staff_id) > values(NEXTVAL('client_alerts_seq'),'39','This is some alert > text','0','2007-05-04 12:42:05','14'); > Or, better, he doesn't have to put a value for it as it is already the default value for this column : insert into clients_alerts (client_id, alert_message, alert_complete, date, staff_id) values('39','This is some alert text','0','2007-05-04 12:42:05','14'); Regards. -- Guillaume.
my bad... I missed the post about the default value.. On 9/12/07, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Laurent Yaish a écrit : > > Hi Rob, > > > > In your insert, do not explicitly specify a value for the primary key, > > instead use NEXTVAL('client_alerts_seq') which will return the next > > valid value and increment the sequence. > > > > i.e: > > insert into clients_alerts (id, client_id, alert_message, > > alert_complete, date, staff_id) > > values(NEXTVAL('client_alerts_seq'),'39','This is some alert > > text','0','2007-05-04 12:42:05','14'); > > > > Or, better, he doesn't have to put a value for it as it is already the > default value for this column : > > insert into clients_alerts (client_id, alert_message, alert_complete, > date, staff_id) > values('39','This is some alert text','0','2007-05-04 12:42:05','14'); > > Regards. > > > -- > Guillaume. >