The following bug has been logged online:
Bug reference: 5784
Logged by: Martin Atukunda
Email address: matlads@gmail.com
PostgreSQL version: 8.4
Operating system: Linux
Description: CREATE INDEX USING GIN complains about array containing
null values yet none exist
Details:
CREATE INDEX USING GIN complains about array containing null values yet none
exist.
Take the following SQL as an example.
-- >8 --
BEGIN;
CREATE TABLE t (id serial primary key, apps bigint[]);
INSERT INTO t (apps) VALUES ('{1,2,3,4}');
INSERT INTO t (apps) VALUES ('{1,2,3,4}');
-- insert an array with one element as a null value
INSERT INTO t (apps) VALUES ('{1,2,3,NULL}');
-- remove the null value
UPDATE t SET apps[4] = -1 WHERE apps[4] IS NULL;
-- the create index fails
CREATE INDEX t_apps_idx ON t USING GIN(apps);
COMMIT;
-- 8< --
note that index creatiion succeeds if I first create the index without
inserting a row containing an array with a null element.
- Martin -