On Tue, Apr 14, 2009 at 11:56 AM, vj <harikris@gmail.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