Thread: Need help understanding error message
In a database table I have these rows:
# select * from contacts where person_nbr=6;
person_nbr | contact_date | contact_type | not
es | next_contact
------------+--------------+--------------+---------------------------------------------------------
---------------------------------------------------------+--------------
6 | 2018-04-05 | Phone | Left message @ 9:39.
|
6 | 2019-05-14 | Phone | He can call me if issues do come up. |
6 | 2021-08-17 | Email | Sent message 2.
| 2021-10-06
6 | 2021-07-23 | Email | Sent message 1.
| 2021-07-28
6 | 2021-07-28 | Email | Sent message 1.
| 2021-08-16
(5 rows)
When I try to update this table with:
(6,'2021-10-26','Email','message 3','2012-11-16'),
psql reports this error:
psql:insert_into_contacts.sql:31: ERROR: duplicate key value violates unique constraint "activities_pkey"
DETAIL: Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, Email) already exists.
The PK for this table is:
PRIMARY KEY, btree (person_nbr, contact_date, contact_type)
so there should be no conflict as the contact_date in the update is not
already in the table.
I don't understand the error as it's not occurred before when updating this
table.
Insight needed,
Rich
> On Oct 26, 2021, at 9:58 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote: > > In a database table I have these rows: > # select * from contacts where person_nbr=6; > person_nbr | contact_date | contact_type | not > es | next_contact ------------+--------------+--------------+--------------------------------------------------------- > ---------------------------------------------------------+-------------- > 6 | 2018-04-05 | Phone | Left message @ 9:39. > | > 6 | 2019-05-14 | Phone | He can call me if issues do come up. | > 6 | 2021-08-17 | Email | Sent message 2. > | 2021-10-06 > 6 | 2021-07-23 | Email | Sent message 1. > | 2021-07-28 > 6 | 2021-07-28 | Email | Sent message 1. > | 2021-08-16 > (5 rows) > > When I try to update this table with: > (6,'2021-10-26','Email','message 3','2012-11-16'), > psql reports this error: > psql:insert_into_contacts.sql:31: ERROR: duplicate key value violates unique constraint "activities_pkey" > DETAIL: Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, Email) already exists. > > The PK for this table is: > PRIMARY KEY, btree (person_nbr, contact_date, contact_type) > so there should be no conflict as the contact_date in the update is not > already in the table. > > I don't understand the error as it's not occurred before when updating this > table. > > Insight needed, > > Rich > > What do you get when you select * where person_nbr = 6 and contact_date = ‘2021-10-26’ and contact_type = ‘Email’ from activities;?
On 10/26/21 11:58 AM, Rich Shepard wrote: > In a database table I have these rows: > # select * from contacts where person_nbr=6; > person_nbr | contact_date | contact_type > | not > es | next_contact > ------------+--------------+--------------+---------------------------- > ---------------------------------------------------------+-------------- > 6 | 2018-04-05 | Phone | Left message @ 9:39. > | > 6 | 2019-05-14 | Phone | He can call me if issues do > come up. | > 6 | 2021-08-17 | Email | Sent message 2. > | 2021-10-06 > 6 | 2021-07-23 | Email | Sent message 1. > | 2021-07-28 > 6 | 2021-07-28 | Email | Sent message 1. > | 2021-08-16 > (5 rows) > > When I try to update this table with: > (6,'2021-10-26','Email','message 3','2012-11-16'), > psql reports this error: > psql:insert_into_contacts.sql:31: ERROR: duplicate key value violates > unique constraint "activities_pkey" > DETAIL: Key (person_nbr, contact_date, contact_type)=(6, 2021-10-26, > Email) already exists. > > The PK for this table is: > PRIMARY KEY, btree (person_nbr, contact_date, contact_type) > so there should be no conflict as the contact_date in the update is not > already in the table. > > I don't understand the error as it's not occurred before when updating this > table. > > Insight needed, Show us the actual UPDATE statement. -- Angular momentum makes the world go 'round.
> On Oct 26, 2021, at 10:07 AM, Rob Sargent <robjsargent@gmail.com> wrote: > > > >> > What do you get when you select * where person_nbr = 6 and contact_date = ‘2021-10-26’ and contact_type = ‘Email’ fromactivities; ? > > > Whoa. Not sure why I put the “from” last but I’m sure you know it goes before the “where”.
On Tue, 26 Oct 2021, Rob Sargent wrote: > What do you get when you select * where person_nbr = 6 and contact_date = > ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? Rob, <where person_nbr = 6 and contact_date = '2021-10-26' and contact_type = 'Email'; person_nbr | contact_date | contact_type | notes | next_contact ------------+--------------+--------------+-------+-------------- (0 rows) Rich
On Tue, 26 Oct 2021, Ron wrote: > Show us the actual UPDATE statement. Ron, insert into contacts (person_nbr,contact_date,contact_type,notes,next_contact) values (1,'2021-10-26','Email','message 3','2012-11-16'), (4,'2021-10-26','Email','message 3','2012-11-16'), (6,'2021-10-26','Email','message 3','2012-11-16'), (3,'2021-10-26','Email','message 3','2012-11-16'), (6,'2021-10-26','Email','message 3','2012-11-16'), ... );
> On Oct 26, 2021, at 11:05 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote: > > On Tue, 26 Oct 2021, Rob Sargent wrote: > >> What do you get when you select * where person_nbr = 6 and contact_date = >> ‘2021-10-26’ and contact_type = ‘Email’ from activities; ? > > Rob, > > <where person_nbr = 6 and contact_date = '2021-10-26' and contact_type = 'Email'; > person_nbr | contact_date | contact_type | notes | next_contact ------------+--------------+--------------+-------+-------------- > (0 rows) > > Rich > Are you doing a batch of updates, and including/generating a duplicate in there? >
> On Oct 26, 2021, at 11:06 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote: > > On Tue, 26 Oct 2021, Ron wrote: > >> Show us the actual UPDATE statement. > > Ron, > > insert into contacts (person_nbr,contact_date,contact_type,notes,next_contact) values > (1,'2021-10-26','Email','message 3','2012-11-16'), > (4,'2021-10-26','Email','message 3','2012-11-16'), > (6,'2021-10-26','Email','message 3','2012-11-16'), > (3,'2021-10-26','Email','message 3','2012-11-16'), > (6,'2021-10-26','Email','message 3','2012-11-16'), > ... > ); > Aren’t lines 3 and 6 duplicates? >
On Tue, 26 Oct 2021, Rob Sargent wrote: > Are you doing a batch of updates, Yes, > ... and including/generating a duplicate in there? No. Rich
On Tue, 26 Oct 2021, Rob Sargent wrote: > Aren’t lines 3 and 6 duplicates? Ah, shoot! The second was supposed to be 16 and that's how I saw it when I scanned the list. Thanks! Rich
On 10/26/21 1:17 PM, Rich Shepard wrote: > On Tue, 26 Oct 2021, Rob Sargent wrote: > >> Aren’t lines 3 and 6 duplicates? > > Ah, shoot! The second was supposed to be 16 and that's how I saw it when I > scanned the list. The second thing I deeply learned about computer programming: it's almost certainly my fault. -- Angular momentum makes the world go 'round.