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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general