Hi Tom and Stephan,
Thanks for your help guys.
I'm using varchar constraint definitions now that are "CHECK ((foobar
ISNULL) OR (length(foobar) < 17))" The short-circuiting of OR's in
7.0.3 allow this to work without error, thereby avoiding the "Bad (null)
varchar() external representation" error that I was getting before due
to inserting NULL's in length(varchar) constraint checked fields.
I've also extended the varchar columns to be the same size as the length
checking I'm doing as Tom suggested, to ensure the constraints do work.
Tom has also suggested using COALESCE instead, so I'll check this out
too.
Regards and best wishes,
Justin Clift
Database Administrator
Tom Lane wrote:
>
> > The reason I'm using constraints in the table is to allow the database
> > to recognise when oversize data is being fed to it and generate an
> > error, instead of silently accepting the data and truncating it.
>
> OK, but have you actually stopped to check whether the combination gives
> the results you expect? I believe the data will be coerced to the
> destination column type --- including any implicit truncation or padding
> --- before the constraint expressions are checked. (I further believe
> that that's the right order of events.)
>
> You might need to make the declared column widths one larger than what
> the constraints check for, if you want to raise an error for this.
>
> > I'm just about to try the same constraints with the ISNULL first, incase
> > the OR's in postgreSQL are short-circuited like you mention they might
> > be.
>
> I'd suggest
>
> CHECK (length(COALESCE(column,'')) < n)
>
> as a workaround for 7.0.*, if you don't want to hack up the source
> code as I mentioned.
>
> regards, tom lane