Hi Artem,

You can see exact same record in your select results from _infogcngr6716_test table despite uniq index exists, if you create a table inherited from the table.

For example:

Create inherited table

CREATE TABLE public._inforgcngr6716_test_child
(
LIKE public._inforgchngr6716_test INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
)
INHERITS (_inforgchngr6716_test)
WITH (
OIDS = FALSE
);

Insert same records into these two table. (No error returns)

INSERT INTO _infogcngr6716_test_child(
            _nodetref, _noderref, _messageno)
    VALUES ('asd', 'asd',10);

INSERT INTO _infogcngr6716_test(
            _nodetref, _noderref, _messageno)
    VALUES ('asd', 'asd', 10);

Make select query to parent table

SELECT _nodetref, _noderref, _messageno
FROM _inforgchngr6716_test;

"asd";"asd";10
"asd";"asd";10

Make select query to only parent table

SELECT _nodetref, _noderref, _messageno
FROM ONLY _inforgchngr6716_test;

"asd";"asd";10

İyi çalışmalar.

Samed YILDIRIM

27.01.2016, 16:14, "Albe Laurenz" <laurenz.albe@wien.gv.at>:

Artem Tomyuk wrote:
I have a table with unique index with 2 exactly the same rows.
How it can be possible?

CREATE TABLE _inforgchngr6716_test
(
   _nodetref bytea NOT NULL,
   _noderref bytea NOT NULL,
   _messageno numeric(10,0)
)
WITH (
   OIDS=FALSE
);
ALTER TABLE _inforgchngr6716_test
   OWNER TO postgres;

-- Index: _inforg6716_bynodemsg_rn_test

-- DROP INDEX _inforg6716_bynodemsg_rn_test;

CREATE UNIQUE INDEX _inforg6716_bynodemsg_rn_test
   ON _inforgchngr6716_test
   USING btree
   (_nodetref, _noderref, _messageno);

Maybe index corruption.
Did you have any crashes?

Do you get an error when you
   REINDEX INDEX _inforg6716_bynodemsg_rn_test;

Yours,
Laurenz Albe

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin