Обсуждение: 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.
>