Re: Adding a column with constraint - Mailing list pgsql-general
From | David Johnston |
---|---|
Subject | Re: Adding a column with constraint |
Date | |
Msg-id | 009501cbd455$516ce1f0$f446a5d0$@yahoo.com Whole thread Raw |
In response to | Adding a column with constraint (Alexander Farber <alexander.farber@gmail.com>) |
Responses |
Re: Adding a column with constraint
|
List | 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber Sent: Thursday, February 24, 2011 1:31 PM To: pgsql-general@postgresql.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: