From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Alexander Farber'" <alexander(dot)farber(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Adding a column with constraint |
Date: | 2011-02-24 19:02:00 |
Message-ID: | 009501cbd455$516ce1f0$f446a5d0$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
A column constraint can only reference its own column. Since you are
referencing "completed" in the CHECK it implicitly converts the Column
constraint into a Table constraint - and table constraints do not reference
the name of a column like a column constraint does during name
auto-generation.
David J.
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Thursday, February 24, 2011 1:31 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Adding a column with constraint
Hello,
I have a paranoic question.
In PostgreSQL 8.4.7 I had a table to store started, completed and
interrupted games :
# \d pref_match
Table "public.pref_match"
Column | Type | Modifiers
-----------+-----------------------+------------------------------------
-----------+-----------------------+-----
id | character varying(32) |
started | integer | default 0
completed | integer | default 0
quit | integer | default 0
yw | character(7) | default to_char(now(), 'IYYY-IW'::text)
Check constraints:
"pref_match_completed_check" CHECK (completed >= 0)
"pref_match_quit_check" CHECK (quit >= 0)
"pref_match_started_check" CHECK (started >= 0) Foreign-key constraints:
"pref_match_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
And have tried to add a column "win" with a check (to document the won
games):
# alter table pref_match add column win integer default 0 check (completed
>= win and win >= 0);
Now I have:
# \d pref_match
Table "public.pref_match"
Column | Type | Modifiers
-----------+-----------------------+------------------------------------
-----------+-----------------------+-----
id | character varying(32) |
started | integer | default 0
completed | integer | default 0
quit | integer | default 0
yw | character(7) | default to_char(now(), 'IYYY-IW'::text)
win | integer | default 0
Check constraints:
"pref_match_check" CHECK (completed >= win AND win >= 0)
"pref_match_completed_check" CHECK (completed >= 0)
"pref_match_quit_check" CHECK (quit >= 0)
"pref_match_started_check" CHECK (started >= 0) Foreign-key constraints:
"pref_match_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)
Shouldn't the line
"pref_match_check" CHECK (completed >= win AND win >= 0)
above actually be:
"pref_match_win_check" CHECK (completed >= win AND win >= 0)
? Does it indicate something went wrong or is it just cosmetic issue?
Thank you
Alex
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksey Tsalolikhin | 2011-02-24 19:02:33 | Re: database is bigger after dump/restore - why? (60 GB to 109 GB) |
Previous Message | Andrew Sullivan | 2011-02-24 18:50:08 | Re: Adding a column with constraint |