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

​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

On Fri, Apr 29, 2016 at 3: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?

​You might want to try re-phrasing your question.

Specification of a constraint within the column specification of CREATE
TABLE is optional. If you don't want to do it there, don't.

​There is no difference between specifying a single-column constraint on
the column definition or as a table constraint.

I'd suggest you upgrade to 9.5.2 - and 9.5.3 in a couple of weeks.

David J.

Hi
Excuse me for the late but I have many work problem.
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.

Da: David G. Johnston [mailto:david.g.johnston@gmail.com]
Inviato: venerdì 29 aprile 2016 17:59
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 3:35 AM, <m.giraldo@stt-telefonia.it<mailto: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<mailto: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?

​You might want to try re-phrasing your question.

Specification of a constraint within the column specification of CREATE TABLE is optional. If you don't want to do it there, don't.

​There is no difference between specifying a single-column constraint on the column definition or as a table constraint.

I'd suggest you upgrade to 9.5.2 - and 9.5.3 in a couple of weeks.

David J.

________________________________

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

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.

​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@it-johnmckown-linux syslog]$ psql
psql (9.4.7)
Type "help" for help.

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

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

tsh009@tsh009=# insert into name(pippo,pluto)
values('2016-01-01'::date,'2016-01-03'::date);
INSERT 0 1
tsh009@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.archie.mckown@gmail.com]
*Inviato:* venerdì 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

​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