Skip site navigation (1) Skip section navigation (2)

Duplicate primary key when primary key is varchar

From: Tính Trương Xuân <tinh(dot)truong(at)evolus(dot)vn>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Duplicate primary key when primary key is varchar
Date: 2010-10-06 10:16:54
Message-ID: AANLkTin+Rj7HCigXx=3+s81N0=d7frGdbxrUY=_fZsU+@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hello,
I have a very interesting situation with my database schema (attached in
this message). But first you have to import this database script into your
database:
psql -h localhost -p 5432 -U postgres -f /path/to/the/file.sql your_database

Note: this schema is virus/malware/rootkit free, you can check it for
yourself.

In the State table, you will notice that there are two rows with the primary
key is AL (it's capital A and capital L). But when you select:
select * from state where code='AL'
There is only one row returned. You may guest that there are some spaces in
the primary key? Nope!
You can verify by this query:
select 'a'||code||'b' from state
You will see that there are two rows with the data as aALb. No space in
between!

The same thing happened for a row in the Family with the code 1006 (it's
one-zero-zero-six)
There are a couple of other rows in other table as well, but not all of
them.

You may ask, how did I come up with this schema?
Basically, I am developing an reporting web application, there is an
external system exporting data files (in UTF-16 LE). Then I convert those
files into UTF-8 and use Hibernate to insert the data.
I know that there is something wrong with the encoding but there is some
wrong with PostgreSQL as well.
Here is the script I used to create the database
CREATE DATABASE bug_demo
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.utf8'
       LC_CTYPE = 'en_US.utf8'
       CONNECTION LIMIT = -1;
I'm using PostgreSQL 8.4.4 on ArchLinux 32bit kernel 2.6.35.7

Any input will be appreciated

Tinh

Attachment: sample_schema.sql.zip
Description: application/zip (10.8 KB)

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2010-10-06 11:06:06
Subject: Re: Duplicate primary key when primary key is varchar
Previous:From: Oliver JowettDate: 2010-10-06 00:01:35
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group