2010/10/6 Tính Trương Xuân <tinh(dot)truong(at)evolus(dot)vn>:
> 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
> 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
> 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
> The same thing happened for a row in the Family with the code 1006 (it's
> There are a couple of other rows in other table as well, but not all of
> 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 220.127.116.11
> Any input will be appreciated
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.
In response to
pgsql-jdbc by date
|Next:||From: louis||Date: 2010-10-06 11:41:42|
|Subject: The logic behind the prepared statement in PostgreSQL|
|Previous:||From: Tính Trương Xuân||Date: 2010-10-06 10:16:54|
|Subject: Duplicate primary key when primary key is varchar|