Re: Insert in table with UNIQUE index

[Samed YILDIRIM <samed@xxxxxxxxxx>]

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@xxxxxxxxxx>:

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin