Thread: Using CHECK while CREATE'ing table
Hi All, 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 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. What is the simplest and most efficient way to achieve this without having to re-invent the wheel. Should i trigger actions during "insert" process or should i use constraints/checks during "create" process?? TIA
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