The following bug has been logged on the website:
Bug reference: 6535
Logged by: Aren Cambre
Email address: aren@arencambre.com
PostgreSQL version: 9.1.3
Operating system: Windows 7 x64
Description:=20=20=20=20=20=20=20=20
(Note that this issue is described fully at
http://stackoverflow.com/questions/9713537/postgres-left-join-is-creating-n=
ew-data.
A few knowledgeable people have weighed in, and there is no solution
identified.)
In a nutshell, I have a query LEFT JOINing table A with table B and
inserting the data into table A'. A' is a functional equivalent of table A.
Table B is actually built from a using a WITH at the top. I'm using it to
help add a rank() to the table.
Table A has just over 2 million rows.
There is a column named citation_id that has no null values. Not only does
citation_id have a NOT NULL constraint, I have verified with a query that it
has no null values.
The LEFT JOIN is pulling table A's citation_id. However, when the joined
table is inserted into table A', Postgres throws "null value in column
"citation_id" violates not-null constraint" errors.
Note that I am running Postgres 32 bit for PostGIS compatibility.