Thread: Unique constaint violated without being violated

Hi all,

Here's an odd one:

 itidb=> \d joblist;
                   Table "public.joblist"
       Column       |           Type           | Modifiers
 -------------------+--------------------------+-----------
  full_name         | character varying(64)    | not null
  email_address     | character varying(64)    | not null
  username          | character varying(12)    |
  password          | character varying(12)    |
  recruiter         | boolean                  | not null
  subscribed        | boolean                  | not null
  verified          | boolean                  | not null
  created_at        | timestamp with time zone | not null
  updated_at        | timestamp with time zone | not null
  verification_code | character varying(24)    |
  alumni            | boolean                  |
 Indexes:
     "joblist_pkey" PRIMARY KEY, btree (email_address)
     "joblist_username_key" UNIQUE, btree (username)

 itidb=> update joblist set (full_name, email_address, recruiter,
 itidb(> subscribed, verified, created_at, updated_at) =
 itidb-> ('[name hidden]', '[email address hidden]', false, true
 itidb(> true, current_timestamp(0), current_timestamp(0));
 ERROR:  duplicate key value violates unique constraint "joblist_pkey"

 itidb=> select * from joblist where
 itidb-> email_address='[email address hidden]';
 (No rows)

email_address is the primary key of this table (because the manual says
every table should have one :-) and the unique aspect of this primary
key is being violated when I try to enter the (hidden) email address
above.

But the email address hasn't already been entered into this table, as
shown by the output of the select command...  so why the error?

Is my database corrupted somehow, or am I just losing my mind?

What course of action do you suggest I follow?

Sebastian

P.S. I've checked three times now, and I'm definitely using the same
     email address in the update command and the select command, i.e., a
     typo is not what's causing this.

On Sat, Dec 6, 2008 at 10:27 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:
>  itidb=> update joblist set (full_name, email_address, recruiter,
>  itidb(> subscribed, verified, created_at, updated_at) =
>  itidb-> ('[name hidden]', '[email address hidden]', false, true
>  itidb(> true, current_timestamp(0), current_timestamp(0));
>  ERROR:  duplicate key value violates unique constraint "joblist_pkey"
>
>  itidb=> select * from joblist where
>  itidb-> email_address='[email address hidden]';
>  (No rows)
>
> email_address is the primary key of this table (because the manual says
> every table should have one :-) and the unique aspect of this primary
> key is being violated when I try to enter the (hidden) email address
> above.
>
> But the email address hasn't already been entered into this table, as
> shown by the output of the select command...  so why the error?

It looks to me like you are setting the whole table to the same
address in the update statement (no where clause)...so of course you'd
get the error.  Maybe you want to do an insert statement?

merlin

Quoth "Merlin Moncure" <mmoncure@gmail.com>:
> It looks to me like you are setting the whole table to the same
> address in the update statement (no where clause)...so of course you'd
> get the error.  Maybe you want to do an insert statement?
>
> merlin

Doh!  Thanks Merlin.  I'm so glad it's just my mind that's going! :-)

Sebastian

On Sat, Dec 6, 2008 at 7:36 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

>>  itidb=> update joblist set (full_name, email_address, recruiter,
>>  itidb(> subscribed, verified, created_at, updated_at) =
>>  itidb-> ('[name hidden]', '[email address hidden]', false, true
>>  itidb(> true, current_timestamp(0), current_timestamp(0));

> It looks to me like you are setting the whole table to the same
> address in the update statement (no where clause)...so of course you'd
> get the error.  Maybe you want to do an insert statement?

Does this mean the PostgreSQL supports row-wise updates?  When did this happen?


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

"Richard Broersma" <richard.broersma@gmail.com> writes:
> Does this mean the PostgreSQL supports row-wise updates?  When did this happen?

Only sort of ...
http://archives.postgresql.org/pgsql-committers/2006-09/msg00021.php

I didn't want this applied
http://archives.postgresql.org/pgsql-patches/2006-07/msg00246.php
but I got outvoted.

            regards, tom lane