Thread: (check) constraints on composite type
Hi! As of PostgreSQL 9.1 it is not possible to declare check constraints on composite types, according to http://www.postgresql.org/docs/9.1/interactive/rowtypes.html.I cannot use domains in that particular context, because I wantto have the check constraint check for a combination of fields in that composite type. Are there any plans to have this feature implemented in the near future? Regards, Ralph
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Graulich Sent: Tuesday, January 10, 2012 2:30 PM To: pgsql-general@postgresql.org Subject: [GENERAL] (check) constraints on composite type Are there any plans to have this feature implemented in the near future? ------------------------------------ Sorry for not answering the main question but you really need to avoid phrases like "near future". That said, new features are only released during major releases and so at best you would have to wait for 9.2 which is probably 9+ months out since 9.1 was just recently released. The better question is whether 9.2 is likely to have what you need or whether it has a chance to be during 9.3 (1.5+ years out). More generically the question is whether anyone is currently working on the feature. I have be over-complicating since "near future" may simply mean "the next release" but being avoids the back-and-forth required to make sure expectations are matched. You do have other options depending on how critical such a feature is to you. There are possibly workarounds that can be implemented or you can always commission the work and request a back-port to 9.1 with future inclusion into 9.2 (or 9.3 is feature freeze has taken effect). Just saying - though I am not involved with development efforts. David J.
On Tue, Jan 10, 2012 at 1:44 PM, David Johnston <polobo@yahoo.com> wrote: > Sorry for not answering the main question but you really need to avoid > phrases like "near future". That said, new features are only released > during major releases and so at best you would have to wait for 9.2 which is > probably 9+ months out since 9.1 was just recently released. The better > question is whether 9.2 is likely to have what you need or whether it has a > chance to be during 9.3 (1.5+ years out). More generically the question is > whether anyone is currently working on the feature. yeah -- I've seen a bit of interest in this feature, but AFAIK nobody's working on it, and no plausible design has been submitted (which is a giant red flag in terms of 9.2 acceptance). fair warning: this is one of those items that sounds neat but is a lot more complicated than it looks. in the meantime, just do the workaround I do: make an immutable function that takes your type as input, and manually apply the constraint in various places like (like as a table constraint). merlin
Hi David! > Sorry for not answering the main question but you really need to avoid > phrases like "near future". That said, new features are only released > during major releases and so at best you would have to wait for 9.2 which is > probably 9+ months out since 9.1 was just recently released. The better > question is whether 9.2 is likely to have what you need or whether it has a > chance to be during 9.3 (1.5+ years out). More generically the question is > whether anyone is currently working on the feature. Thanks alot for your input on that issue. I really appreciate it. - However I posed my question using that wording intentionally, so it is open to any input, may it that someone states he orshe is already working on that part of PostgreSQL, someone already made a plan and checked whether the request is feasibleto add or any other decisions on that topic, that were probably made and I missed or were not published. "Near future"for me means getting all possible input, not referring to a particular time line. If one tells me "it is planned,no deadline given" that's as fine for me as "no plans, no idea" or "March, 12th, this year". Understanding your point, I found posing the question using "whether anyone is currently working on the feature" far toolimiting, probably guiding the question into the wrong direction, like pushing someone to look into it between the lines- in the sense of "why the heck is PostgreSQL lacking that feature of utmost importance?" - which I didn't intent. Now back to the point of question: > You do have other options depending on how critical such a feature is to > you. There are possibly workarounds that can be implemented or you can > always commission the work and request a back-port to 9.1 with future > inclusion into 9.2 (or 9.3 is feature freeze has taken effect). Let's assume I have three fields in my custom type: field1 varchar(4) // or text field2 smallint field3 char(1) // or text Now I want to add a complex check constraint involving the combination of those three fields, written in pseudo code: field1 IN ('A') && field2 BETWEEN 1 AND 120 && field3 IS NULL field1 IN ('B') && field2 BETWEEN 1 AND 40 && field3 IS NULL field1 IN ('TFnr') && field3 BETWEEN 1600 AND 2200 && field3 IN ('a', 'b', 'c') field1 IS NULL & field2 = 1 & field3 IN ('a'..'z') OR field3 IS NULL ... & field2 = 2 & field3 IN ('a'..'h') OR field3 IS NULL ... & field2 = 3 & field3 IN ('a'..'o', 'Z') OR field3 IS NULL and so on (the problem is actually far more complex, just to give a hint of…). My point is putting all the validation ofdata into the database itself, so just having to care for changes at one place and making sure data and data domains ofvalid data are tightly integrated, which I favor. Regards, Ralph
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Graulich Sent: Tuesday, January 10, 2012 3:47 PM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] (check) constraints on composite type Let's assume I have three fields in my custom type: field1 varchar(4) // or text field2 smallint field3 char(1) // or text Now I want to add a complex check constraint involving the combination of those three fields, written in pseudo code: field1 IN ('A') && field2 BETWEEN 1 AND 120 && field3 IS NULL field1 IN ('B') && field2 BETWEEN 1 AND 40 && field3 IS NULL field1 IN ('TFnr') && field3 BETWEEN 1600 AND 2200 && field3 IN ('a', 'b', 'c') field1 IS NULL & field2 = 1 & field3 IN ('a'..'z') OR field3 IS NULL ... & field2 = 2 & field3 IN ('a'..'h') OR field3 IS NULL ... & field2 = 3 & field3 IN ('a'..'o', 'Z') OR field3 IS NULL and so on (the problem is actually far more complex, just to give a hint of.). My point is putting all the validation of data into the database itself, so just having to care for changes at one place and making sure data and data domains of valid data are tightly integrated, which I favor. Regards, Ralph ---------------------------------------------------------------------------- --- I would suggest trying to make your constraints as "template" as possible and then create a table that provides inputs for each of the constraint "parameters". You include a field which flags the constraint as either "require" or "disallow" and then you write a function that iterates over the table and compares each of the constraints against the value and ensure that at least of the "require" constraints pass and that all the "disallow" constraints fail. RegularExpressions will likely come in handy and you will probably need to create "field_n_can_be_null" booleans to allow them explicitly. The implementation I've used is to "CROSS JOIN" the value and the constraint table and then use ARRAY_AGG to create multiple arrays for the various test results (the specific set of arrays varies depending on your needs). I've done this with a single text field check using reqularexpressions and the results work fairly well (ignoring performance concerns which have not yet been a factor due to the lite load I experience). For an exclusion constraint I add "Exclude" if the constraint matches otherwise I add "Ignore"; for inclusion constraints I add "Include" for matches or "Ignore"; I then exclude if "Exclude" = ANY(match_results) OR "Ignore" = ALL(match_results); otherwise there has to be at least one "Include" and thus the check passes. For example: SELECT val, CASE WHEN 'Exclude' = ANY(results) THEN 'Fail - Excluded' WHEN 'Ignore' = ALL(results) THEN 'Fail - No Includes Match' WHEN 'Include' = ANY(results) THEN 'Include' ELSE 'Fail - Not Possible As Coded But Just In Case' END AS test_outcome FROM ( SELECT val, ARRAY_AGG( CASE WHEN val ~ pattern THEN CASE WHEN include THEN 'Include' ELSE 'Exclude' END ELSE 'Ignore' END ) AS results FROM (VALUES ('value to check')) vals (val) CROSS JOIN (VALUES ('test pattern',true) [, ...] ) tests (pattern, include) GROUP BY val ) val_test David J.