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