Re: Field Constraints

Поиск
Список
Период
Сортировка
Искать

Re: Field Constraints

От:
"PGMailList" <pgmail@pgexplorer.com>
Дата:
CHECK ("Prior_Rx"  ~ '[Yy][Ee][Ss]$|[Nn][Oo]$');

http://www.pgexplorer.com
PostgreSQL GUI



----- Original Message -----
From: "Joseph Maxwell" 
To: 
Sent: Wednesday, March 06, 2002 9:47 PM
Subject: [GENERAL] Field Constraints


> Hello,
> I have created a DB and TABLE but would like to constrain one field to
> accept either "YES" or "NO" only. How can I set this up?
> I have tried various syntactic constructs of
>
>      Prior_Rx              varchar(3) NULL CHECK ('Yes', 'No'),
>      Prior_Rx              varchar(3) NULL CHECK (VALUE 'Yes', 'No'),
>      Prior_Rx              varchar(3) NULL CHECK ('Yes', 'No'),
>      Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (VALUE
> 'Yes', 'No')),
>      Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK ('Yes',
> 'No')),
>      Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (VALUE Yes,
> No)),
>      Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (Yes, No)),
>
>      Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (Yes OR
> No)),
>      Prior_Rx              varchar(3) NULL CHECK (Yes OR No),
>      Prior_Rx              varchar(3) NULL CHECK ('Yes' OR 'No'),
>
> Obviously they were all wrong, could someone advise?
> Thanks
>
> --  Joe  --
>
PS--------------------------------------------------------------------------
-------------
>
> Just learned that the datatype should be 'bool' in this case
>
>      haven't tested it yet
>
> But had I wanted to restrict it to say either mike or john, or even one
> of a list say, 'tea', 'cofee', 'chocolate' or 'espresso', how should
> that be constructed?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: Field Constraints

От:
"Dan Langille" <dan@langille.org>
Дата:
On 6 Mar 2002 at 11:47, Joseph Maxwell wrote:

> Hello,
> I have created a DB and TABLE but would like to constrain one field to
> accept either "YES" or "NO" only. How can I set this up? I have tried
> various syntactic constructs of
> 

[snip]


> --  Joe  --
> PS-------------------------------------------------------------------------
> --------------
> 
> Just learned that the datatype should be 'bool' in this case
> 
>      haven't tested it yet
> 
> But had I wanted to restrict it to say either mike or john, or even one of
> a list say, 'tea', 'cofee', 'chocolate' or 'espresso', how should that be
> constructed?

Try:

found_in_ports         boolean               not null
        default 'N'
        check (
            found_in_ports in ('Y','N'))

The same way as the above AFAIK>
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

Field Constraints

От:
Joseph Maxwell <jemaxwell@jaymax.com>
Дата:
Hello,
I have created a DB and TABLE but would like to constrain one field to
accept either "YES" or "NO" only. How can I set this up?
I have tried various syntactic constructs of

     Prior_Rx              varchar(3) NULL CHECK ('Yes', 'No'),
     Prior_Rx              varchar(3) NULL CHECK (VALUE 'Yes', 'No'),
     Prior_Rx              varchar(3) NULL CHECK ('Yes', 'No'),
     Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (VALUE
'Yes', 'No')),
     Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK ('Yes',
'No')),
     Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (VALUE Yes,
No)),
     Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (Yes, No)),

     Prior_Rx              varchar(3) NULL CONSTRAINT (CHECK (Yes OR
No)),
     Prior_Rx              varchar(3) NULL CHECK (Yes OR No),
     Prior_Rx              varchar(3) NULL CHECK ('Yes' OR 'No'),

Obviously they were all wrong, could someone advise?
Thanks

--  Joe  --
PS---------------------------------------------------------------------------------------

Just learned that the datatype should be 'bool' in this case

     haven't tested it yet

But had I wanted to restrict it to say either mike or john, or even one
of a list say, 'tea', 'cofee', 'chocolate' or 'espresso', how should
that be constructed?
FAQ