Re: BUG #14121: Constraint UNIQUE
От | John McKown |
---|---|
Тема | Re: BUG #14121: Constraint UNIQUE |
Дата | |
Msg-id | CAAJSdjh0SZBKcmscW6tUozSo4vF3We_osj0nmmsfEDQO7MJr=Q@mail.gmail.com обсуждение исходный текст |
Ответ на | BUG #14121: Constraint UNIQUE (m.giraldo@stt-telefonia.it) |
Список | pgsql-bugs |
On Thu, May 5, 2016 at 5:46 AM, Marco Giraldo <m.giraldo@stt-telefonia.it> wrote: > Hi John > > Excuse me for the late but I have many work problem. > =E2=80=8BI understand. > I hope I understand what you mean. > > But I do this constraint CREATE TABLE (pippo date UNIQUE, pluto date) etc= =E2=80=A6 > > And I don=E2=80=99t can remove this restriction. > =E2=80=8BCan you show me (and the entire pg=E2=80=8Bsql-bugs group also) wh= at happens when you try? That is, in psql, enter something like: CREATE TABLE name (pippo date UNIQUE, pluto date); \d+ name What does the \d+ output show? This is what happens when I do this: [tsh009@it-johnmckown-linux syslog]$ psql psql (9.4.7) Type "help" for help. tsh009@tsh009=3D# CREATE TABLE name (pippo date UNIQUE, pluto date); CREATE TABLE tsh009@tsh009=3D# \d+ name Table "public.name" Column | Type | Modifiers | Storage | Stats target | Description --------+------+-----------+---------+--------------+------------- pippo | date | | plain | | pluto | date | | plain | | Indexes: "name_pippo_key" UNIQUE CONSTRAINT, btree (pippo) tsh009@tsh009=3D# insert into name(pippo,pluto) values('2016-01-01'::date,'2016-01-02'::date); INSERT 0 1 type=3DSTREAM tsh009@tsh009=3D# select * from name; pippo | pluto ------------+------------ 2016-01-01 | 2016-01-02 (1 row) tsh009@tsh009=3D# insert into name(pippo,pluto) values('2016-01-01'::date,'2016-01-03'::date); ERROR: duplicate key value violates unique constraint "name_pippo_key" DETAIL: Key (pippo)=3D(2016-01-01) already exists. tsh009@tsh009=3D# ALTER TABLE name DROP CONSTRAINT name_pippo_key; ALTER TABLE tsh009@tsh009=3D# \d+ name Table "public.name" Column | Type | Modifiers | Storage | Stats target | Description --------+------+-----------+---------+--------------+------------- pippo | date | | plain | | pluto | date | | plain | | tsh009@tsh009=3D# insert into name(pippo,pluto) values('2016-01-01'::date,'2016-01-03'::date); INSERT 0 1 tsh009@tsh009=3D# =E2=80=8BThe first INSERT works because the table is empty. The second time= , the INSERT fails because of the duplicate key. But after the ALTER TABLE ... DROP CONSTRAINT, I can do the second INSERT again and it succeeds.=E2=80=8B > > > > > *Da:* John McKown [mailto:john.archie.mckown@gmail.com] > *Inviato:* venerd=C3=AC 29 aprile 2016 16:47 > *A:* Marco Giraldo <m.giraldo@stt-telefonia.it> > *Cc:* pgsql-bugs@postgresql.org > *Oggetto:* Re: [BUGS] BUG #14121: Constraint UNIQUE > > > > On Fri, Apr 29, 2016 at 5:35 AM, <m.giraldo@stt-telefonia.it> wrote: > > The following bug has been logged on the website: > > Bug reference: 14121 > Logged by: Marco > Email address: m.giraldo@stt-telefonia.it > PostgreSQL version: 9.5.0 > Operating system: Windows 7 > Description: > > Hi > I have a problem with UNIQUE constraint. > I don't understand the difference from TYPE type UNIQUE(when I declare a > column of the table) and ALTER TABLE table ADD CONSTRAINT column UNIQUE > (column). > How can I remove the UNIQUE option from TYPE declaration? > > Best reguards > > > > =E2=80=8BALTER TABLE table DROP CONSTRAINT constraint_name; > > > > Example: > > > > # create table c(c text unique); > > > > CREATE TABLE > > > > # \d+ c > > Table "public.c" > > > > Column | Type | Modifiers | Storage | Stats target | Description > > > > --------+------+-----------+----------+--------------+------------- > > > > c | text | | extended | | > > > > Indexes: > > > > "c_c_key" UNIQUE CONSTRAINT, btree (c) > > > > > > > > > > # alter table c drop constraint c_c_key; > > ALTER TABLE > > # \d+ c > > Table "public.c" > > Column | Type | Modifiers | Storage | Stats target | Description > > --------+------+-----------+----------+--------------+------------- > > c | text | | extended | | > > =E2=80=8B > > > > > > > -- > > The unfacts, did we have them, are too imprecisely few to warrant our > certitude. > > > > Maranatha! <>< > John McKown > > ------------------------------ > > DICHIARAZIONE DI ESONERO DI RESPONSABILITA' > Le informazioni contenute nella presente comunicazione e i relativi > allegati possono essere riservate e sono, comunque, destinate > esclusivamente alle persone o alla Societa' sopraindicati. > La diffusione, distribuzione e/o copiatura del documento trasmesso da > parte di qualsiasi soggetto diverso dal destinatario e' proibita, sia ai > sensi dell'art. 616 c.p., che ai sensi del D.Lgs. n. 196/2003. > > Se avete ricevuto questo messaggio per errore, vi preghiamo di > distruggerlo e di informarci immediatamente per telefono al +39 036226941 > --=20 The unfacts, did we have them, are too imprecisely few to warrant our certitude. Maranatha! <>< John McKown
В списке pgsql-bugs по дате отправления: