Re: Duplicate primary key when primary key is varchar

2010/10/6 Tính Trương Xuân <tinh(dot)truong(at)evolus(dot)vn>:
>
> 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

Tinh,

I looked at the data, did not import it, but both those instances the
copy command AL and 1006 are the first lines and they both have a
space in front of them.

Dave