Thread: Need help understanding error message

Need help understanding error message

From
Rich Shepard
Date:
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




Re: Need help understanding error message

From
Rob Sargent
Date:

> 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;? 






Re: Need help understanding error message

From
Ron
Date:
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.



Re: Need help understanding error message

From
Rob Sargent
Date:

> 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”.




Re: Need help understanding error message

From
Rich Shepard
Date:
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



Re: Need help understanding error message

From
Rich Shepard
Date:
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'),
...
);



Re: Need help understanding error message

From
Rob Sargent
Date:

> 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?

>




Re: Need help understanding error message

From
Rob Sargent
Date:

> 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?

>




Re: Need help understanding error message

From
Rich Shepard
Date:
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



Re: Need help understanding error message [RESOLVED]

From
Rich Shepard
Date:
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



Re: Need help understanding error message [RESOLVED]

From
Ron
Date:
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.