Thread: Adding a column with constraint

Adding a column with constraint

From

Alexander Farber

Date:

24 February 2011, 17:30:42

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

Re: Adding a column with constraint

From

Andrew Sullivan

Date:

24 February 2011, 17:50:20

On Thu, Feb 24, 2011 at 07:30:32PM +0100, Alexander Farber wrote:
> 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?

That's its name.  It could be "fredsredwagon".  If you want it to have
a specific name, then name it when you create the constraint.  This is
a little hard to understand from the manual, because of all the square
brackets and such, but I encourage you to experiment with the manual
open to see exactly what everything does.

I haven't checked just this instant, but I think you can rename the
constraint if you don't like its name.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: Adding a column with constraint

From

"David Johnston"

Date:

24 February 2011, 18:02:17

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

Re: Adding a column with constraint

From

Alexander Farber

Date:

24 February 2011, 18:10:38

On Thu, Feb 24, 2011 at 8:02 PM, David Johnston <polobo@yahoo.com> wrote:
> 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.

Oh ok, that's a good explanation, thank you


>
> # 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)
>