Обсуждение: check constraint bug?
I specified: ALTER TABLE h ADD CONSTRAINT val_h_stats CHECK (NOT (sex = 'f') AND (stats IS NOT NULL)); which was translated to: ALTER TABLE h ADD CONSTRAINT val_h_stats CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL); Can the expression not be bracketed? I changed this to: ALTER TABLE horse ADD CONSTRAINT val_horse_stats CHECK (sex != 'f') OR (stats IS NULL)); -- Best Regards, Tarlika Elisabeth Schmitz
On Thu, May 5, 2011 at 11:06 PM, Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de> wrote: > I specified: > > ALTER TABLE h ADD CONSTRAINT val_h_stats > CHECK (NOT (sex = 'f') AND (stats IS NOT NULL)); > > which was translated to: > > ALTER TABLE h ADD CONSTRAINT val_h_stats > CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL); You need another level of parens: CHECK (NOT ((sex = 'f') AND (stats IS NOT NULL)));
Scott Marlowe wrote: > Tarlika Elisabeth Schmitz wrote: >> I specified: >> >> ALTER TABLE h ADD CONSTRAINT val_h_stats >> CHECK (NOT (sex = 'f') AND (stats IS NOT NULL)); >> >> which was translated to: >> >> ALTER TABLE h ADD CONSTRAINT val_h_stats >> CHECK (NOT sex = 'f'::bpchar AND stats IS NOT NULL); > You need another level of parens: > > CHECK (NOT ((sex = 'f') AND (stats IS NOT NULL))); Because NOT has higher precedence than AND. <http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-PRECEDENCE> Note that equals (=), IS and NOTNULL have higher precedence than NOT. So the CHECK expression Scott indicated is equivalent to the parenthesis-minimal CHECK ( NOT ( sex = 'f' AND stats IS NOT NULL ) ) or CHECK ( sex != 'f' OR stats IS NULL ) -- Lew Honi soit qui mal y pense. http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg