Re: BUG #14121: Constraint UNIQUE

On Thu, May 5, 2016 at 5:46 AM, Marco Giraldo <m(dot)giraldo(at)stt-telefonia(dot)it>
wrote:

> Hi John
>
> Excuse me for the late but I have many work problem.
>

​I understand.

> I hope I understand what you mean.
>
> But I do this constraint CREATE TABLE (pippo date UNIQUE, pluto date) etc…
>
> And I don’t can remove this restriction.
>

​Can you show me (and the entire pg​sql-bugs group also) what 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(at)it-johnmckown-linux syslog]$ psql
psql (9.4.7)
Type "help" for help.

tsh009(at)tsh009=# CREATE TABLE name (pippo date UNIQUE, pluto date);
CREATE TABLE
tsh009(at)tsh009=# \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(at)tsh009=# insert into name(pippo,pluto)
values('2016-01-01'::date,'2016-01-02'::date);
INSERT 0 1
type=STREAM tsh009(at)tsh009=# select * from name;
pippo | pluto
------------+------------
2016-01-01 | 2016-01-02
(1 row)

tsh009(at)tsh009=# 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)=(2016-01-01) already exists.
tsh009(at)tsh009=# ALTER TABLE name DROP CONSTRAINT name_pippo_key;
ALTER TABLE
tsh009(at)tsh009=# \d+ name
Table "public.name"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+---------+--------------+-------------
pippo | date | | plain | |
pluto | date | | plain | |

tsh009(at)tsh009=# insert into name(pippo,pluto)
values('2016-01-01'::date,'2016-01-03'::date);
INSERT 0 1
tsh009(at)tsh009=#

​The 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.​

>
>
>
>
> *Da:* John McKown [mailto:john(dot)archie(dot)mckown(at)gmail(dot)com]
> *Inviato:* venerdì 29 aprile 2016 16:47
> *A:* Marco Giraldo <m(dot)giraldo(at)stt-telefonia(dot)it>
> *Cc:* pgsql-bugs(at)postgresql(dot)org
> *Oggetto:* Re: [BUGS] BUG #14121: Constraint UNIQUE
>
>
>
> On Fri, Apr 29, 2016 at 5:35 AM, <m(dot)giraldo(at)stt-telefonia(dot)it> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 14121
> Logged by: Marco
> Email address: m(dot)giraldo(at)stt-telefonia(dot)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
>
>
>
> ​ALTER 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 | |
>
> ​
>
>
>
>
>
>
> --
>
> 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
>

--
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown