Re: Using CHECK while CREATE'ing table

On Tue, Apr 14, 2009 at 11:56 AM, vj <harikris(at)gmail(dot)com> wrote:

>  I am new to postgreSQL and have a working experience with SQLite.
>  While i am creating a table, i want to use constraints or more so -
> use checks to see that

I think that you will find that PostgreSQL has more than enough
features to solve this problem in a number of ways.

> 1. the email address is valid [a basic check with no bells and
> whistles is good enuf for me].
> 2. On a separate column entry, i would also want to check that the
> string is a valid hex string i.e no characters other than [0-9 and (A-
> F or a-f)] are present.

Probably the most portable way is to use a table check constraint.
Notice the CHECK() constraint section:
http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html#AEN53993

Another option that is SQL-92 compliant is to use a custom DOMAIN data
type. It is a custom data type to you define that validates user
input similar to a check constraint. The value to it is that you only
need to maintain your validation logic in one place if you use this
data type in multiple places.

http://www.postgresql.org/docs/8.3/interactive/sql-createdomain.html

Finally you can check out PostgreSQL's pattern patching. LIKE and
SIMILAR TO are SQL compliant but PostgreSQL also supports POSIX
Regular Expressions which is extremely powerful.

> What is the simplest and most efficient way to achieve this without
> having to re-invent the wheel.

The easiest is probably a table check constraint using LIKE pattern matching.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug