Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #14121: Constraint UNIQUE

BUG #14121: Constraint UNIQUE

From
m.giraldo@stt-telefonia.it
Date:
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

Re: BUG #14121: Constraint UNIQUE

From
John McKown
Date:
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



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

Maranatha! <><
John McKown

Re: BUG #14121: Constraint UNIQUE

From
"David G. Johnston"
Date:
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?
>

=E2=80=8BYou 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.

=E2=80=8BThere is no difference between specifying a single-column constrai=
nt 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.

Re: BUG #14121: Constraint UNIQUE

From
John McKown
Date:
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

R: BUG #14121: Constraint UNIQUE

From
Marco Giraldo
Date:
SGkNCkV4Y3VzZSBtZSBmb3IgdGhlIGxhdGUgYnV0IEkgaGF2ZSBtYW55IHdvcmsgcHJvYmxlbS4N
CkkgaG9wZSBJIHVuZGVyc3RhbmQgd2hhdCB5b3UgbWVhbi4NCkJ1dCBJIGRvIHRoaXMgY29uc3Ry
YWludCBDUkVBVEUgVEFCTEUgKHBpcHBvIGRhdGUgVU5JUVVFLCBwbHV0byBkYXRlKSBldGPigKYN
CkFuZCBJIGRvbuKAmXQgY2FuIHJlbW92ZSB0aGlzIHJlc3RyaWN0aW9uLg0KDQoNCg0KRGE6IERh
dmlkIEcuIEpvaG5zdG9uIFttYWlsdG86ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb21dDQpJbnZp
YXRvOiB2ZW5lcmTDrCAyOSBhcHJpbGUgMjAxNiAxNzo1OQ0KQTogTWFyY28gR2lyYWxkbyA8bS5n
aXJhbGRvQHN0dC10ZWxlZm9uaWEuaXQ+DQpDYzogcGdzcWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZw0K
T2dnZXR0bzogUmU6IFtCVUdTXSBCVUcgIzE0MTIxOiBDb25zdHJhaW50IFVOSVFVRQ0KDQpPbiBG
cmksIEFwciAyOSwgMjAxNiBhdCAzOjM1IEFNLCA8bS5naXJhbGRvQHN0dC10ZWxlZm9uaWEuaXQ8
bWFpbHRvOm0uZ2lyYWxkb0BzdHQtdGVsZWZvbmlhLml0Pj4gd3JvdGU6DQpUaGUgZm9sbG93aW5n
IGJ1ZyBoYXMgYmVlbiBsb2dnZWQgb24gdGhlIHdlYnNpdGU6DQoNCkJ1ZyByZWZlcmVuY2U6ICAg
ICAgMTQxMjENCkxvZ2dlZCBieTogICAgICAgICAgTWFyY28NCkVtYWlsIGFkZHJlc3M6ICAgICAg
bS5naXJhbGRvQHN0dC10ZWxlZm9uaWEuaXQ8bWFpbHRvOm0uZ2lyYWxkb0BzdHQtdGVsZWZvbmlh
Lml0Pg0KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuMA0KT3BlcmF0aW5nIHN5c3RlbTogICBXaW5k
b3dzIDcNCkRlc2NyaXB0aW9uOg0KDQpIaQ0KSSBoYXZlIGEgcHJvYmxlbSB3aXRoIFVOSVFVRSBj
b25zdHJhaW50Lg0KSSBkb24ndCB1bmRlcnN0YW5kIHRoZSBkaWZmZXJlbmNlIGZyb20gVFlQRSB0
eXBlIFVOSVFVRSh3aGVuIEkgZGVjbGFyZSBhDQpjb2x1bW4gb2YgdGhlIHRhYmxlKSBhbmQgQUxU
RVIgVEFCTEUgdGFibGUgQUREIENPTlNUUkFJTlQgY29sdW1uIFVOSVFVRQ0KKGNvbHVtbikuDQpI
b3cgY2FuIEkgcmVtb3ZlIHRoZSBVTklRVUUgb3B0aW9uIGZyb20gVFlQRSBkZWNsYXJhdGlvbj8N
Cg0K4oCLWW91IG1pZ2h0IHdhbnQgdG8gdHJ5IHJlLXBocmFzaW5nIHlvdXIgcXVlc3Rpb24uDQoN
ClNwZWNpZmljYXRpb24gb2YgYSBjb25zdHJhaW50IHdpdGhpbiB0aGUgY29sdW1uIHNwZWNpZmlj
YXRpb24gb2YgQ1JFQVRFIFRBQkxFIGlzIG9wdGlvbmFsLiAgSWYgeW91IGRvbid0IHdhbnQgdG8g
ZG8gaXQgdGhlcmUsIGRvbid0Lg0KDQrigItUaGVyZSBpcyBubyBkaWZmZXJlbmNlIGJldHdlZW4g
c3BlY2lmeWluZyBhIHNpbmdsZS1jb2x1bW4gY29uc3RyYWludCBvbiB0aGUgY29sdW1uIGRlZmlu
aXRpb24gb3IgYXMgYSB0YWJsZSBjb25zdHJhaW50Lg0KDQpJJ2Qgc3VnZ2VzdCB5b3UgdXBncmFk
ZSB0byA5LjUuMiAtIGFuZCA5LjUuMyBpbiBhIGNvdXBsZSBvZiB3ZWVrcy4NCg0KRGF2aWQgSi4N
Cg0KX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX18NCg0KRElDSElBUkFaSU9ORSBESSBF
U09ORVJPIERJIFJFU1BPTlNBQklMSVRBJw0KTGUgaW5mb3JtYXppb25pIGNvbnRlbnV0ZSBuZWxs
YSBwcmVzZW50ZSBjb211bmljYXppb25lIGUgaSByZWxhdGl2aSBhbGxlZ2F0aSBwb3Nzb25vIGVz
c2VyZSByaXNlcnZhdGUgZSBzb25vLCBjb211bnF1ZSwgZGVzdGluYXRlIGVzY2x1c2l2YW1lbnRl
IGFsbGUgcGVyc29uZSBvIGFsbGEgU29jaWV0YScgc29wcmFpbmRpY2F0aS4NCkxhIGRpZmZ1c2lv
bmUsIGRpc3RyaWJ1emlvbmUgZS9vIGNvcGlhdHVyYSBkZWwgZG9jdW1lbnRvIHRyYXNtZXNzbyBk
YSBwYXJ0ZSBkaSBxdWFsc2lhc2kgc29nZ2V0dG8gZGl2ZXJzbyBkYWwgZGVzdGluYXRhcmlvIGUn
IHByb2liaXRhLCBzaWEgYWkgc2Vuc2kgZGVsbCdhcnQuIDYxNiBjLnAuLCBjaGUgYWkgc2Vuc2kg
ZGVsIEQuTGdzLiBuLiAxOTYvMjAwMy4NCg0KU2UgYXZldGUgcmljZXZ1dG8gcXVlc3RvIG1lc3Nh
Z2dpbyBwZXIgZXJyb3JlLCB2aSBwcmVnaGlhbW8gZGkgZGlzdHJ1Z2dlcmxvIGUgZGkgaW5mb3Jt
YXJjaSBpbW1lZGlhdGFtZW50ZSBwZXIgdGVsZWZvbm8gYWwgKzM5IDAzNjIyNjk0MQ0K