Syntax of: alter table ... add constraint ...

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Syntax of: alter table ... add constraint ...
Date: 2010-11-08 14:50:32
Message-ID: AANLkTi=EVn5J-dWMSUzC5oUsif4NcAbsUpfaiiWf_tR0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm having this table filled with data:

\d pref_users;
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
first_name | character varying(32) |
last_name | character varying(32) |
female | boolean |
avatar | character varying(128) |
city | character varying(32) |
lat | real |
lng | real |
last_login | timestamp without time zone | default now()
last_ip | inet |
medals | smallint |
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pref_users_lat_check" CHECK ((-90)::double precision <= lat AND
lat <= 90::double precision)
"pref_users_lng_check" CHECK ((-90)::double precision <= lng AND
lng <= 90::double precision)
"pref_users_medals_check" CHECK (medals > 0)
Referenced by:
TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_rate" CONSTRAINT "pref_rate_obj_fkey" FOREIGN KEY
(obj) REFERENCES pref_users(id)
TABLE "pref_rate" CONSTRAINT "pref_rate_subj_fkey" FOREIGN KEY
(subj) REFERENCES pref_users(id)

And then I realized that I actually want

medals smallint default 0 check (medals >= 0)

So I've dropped the old constraint with

alter table pref_users drop constraint "pref_users_medals_check";

but how can I add the new contraint please? I'm trying:

alter table pref_users add constraint pref_users_medals_check (medals >= 0);
ERROR: syntax error at or near "("
LINE 1: ...pref_users add constraint pref_users_medals_check (medals >=...
^
and many combinations of quotes and "check" inbetween,
but can't find the correct syntax

Regards
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Cédric Villemain 2010-11-08 15:08:49 Re: migrate from 8.1 to 9.0
Previous Message Vick Khera 2010-11-08 12:21:17 Re: migrate from 8.1 to 9.0