Re: Insert fails when it shouldn't - Mailing list pgsql-general

From Jim C. Nasby
Subject Re: Insert fails when it shouldn't
Date
Msg-id 20060304033021.GO82012@pervasive.com
Whole thread Raw
In response to Insert fails when it shouldn't  (Anakreon Mendis <anakreonmejdi@yahoo.gr>)
List pgsql-general
What encoding did you initdb with?

On Thu, Mar 02, 2006 at 04:59:39PM +0200, Anakreon Mendis wrote:
> The table:
> CREATE TABLE DILOSIS (
>     DL_AEM INT NOT NULL,
>     DL_CODE VARCHAR(6) NOT NULL,
>     DL_YEAR INT NOT NULL,
>     DL_GRADE FLOAT(3) NOT NULL,
>     DL_LESTYPE INT NOT NULL,
>     DL_SEMESTER INT NOT NULL,
>     DL_DM INT NOT NULL,
>     DL_COEF FLOAT(3), --Syntelestis ptyxiou
>     DL_CAT_NORM BOOLEAN NOT NULL, --TRUE NORMAL, FALSE APALLAGI
>     DL_TEI INT NOT NULL,
>     DL_THERINO BOOLEAN NOT NULL
> );
> ALTER TABLE DILOSIS ADD CONSTRAINT pk_dilosis PRIMARY KEY (DL_AEM, DL_CODE, DL_YEAR, DL_TEI, DL_SEMESTER);
>
> The table is empty and statements
> stored in a file are executed with psql.
> ===== Statements ===
> \encoding iso_8859_7
> delete from dilosis;
> INSERT INTO DILOSIS VALUES(1, '??0100', 2000, 0.00, 1, 1, 5, 5.00, true, 1, true);
> INSERT INTO DILOSIS VALUES(1, '??0100', 2000, 0.00, 1, 1, 5, 5.00, true, 1, true);
>
> The second insert fails with an error message:
> psql:a.sql:4: ERROR:  duplicate key violates unique constraint "pk_dilosis"
>
> The second insert is identical to the first one except
> the DL_CODE value.
> 1:??0100 (Gamma Epsilon ..)
> 2:??0100 (Heta Ypsilon ..)
>
> I think what is hapening is that the two first
> letters are ignored and the two values are found
> identical since the postfix is 0100 for bouth of them.
>
> What can be done so the second instert does not fail?
>
> Anakreon
> --
> Three words describe our society:homo homini lupus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-general by date:

Previous
From: Michael Glaesemann
Date:
Subject: Accessing composite type columns in indexes
Next
From: "Jim C. Nasby"
Date:
Subject: Re: Solaris 10 ZFS Postgresql request for comments