Обсуждение: Insert in table with UNIQUE index
Hi All!
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);
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
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
);
(
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);
_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;
FROM _inforgchngr6716_test;
"asd";"asd";10
"asd";"asd";10
"asd";"asd";10
Make select query to only parent table
SELECT _nodetref, _noderref, _messagenoFROM 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
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.
So what? That is not the problem presented.
David J.
On Wed, Jan 27, 2016 at 5:30 AM, Artem Tomyuk <admin@leboutique.com> 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))CREATE UNIQUE INDEX _inforg6716_bynodemsg_rn_testON _inforgchngr6716_testUSING btree(_nodetref, _noderref, _messageno);
In the duplicated entries, are any of the "_messageno" values NULL? If so, that would explain it. NULL is not bound by the constraint, you have a few options: make it NOT NULL, COALESCE() it to a known value, or create a couple partial indexes to enforce the uniqueness.
El mié, 27-01-2016 a las 14:12 +0000, Albe Laurenz escribió: > 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? To discard a index corruption problem, try to select duplicates row setting enable_indexscan and enable_bitmapscan to false and check if you get 1 or 2 rows, if you get 2 -> I'll follow Albe advide and reindex > > Do you get an error when you > REINDEX INDEX _inforg6716_bynodemsg_rn_test; > > Yours, > Laurenz Albe >