From: Justin Clift <aa2@bigpond.net.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@kick.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Possible bug? WAS :Bad (null) varchar() external representation.
Date: Fri, 12 Jan 2001 14:57:56 +1100
Message-ID: <3A5E80C4.54D654A0@bigpond.net.au> (raw)
References: <3A5CA5FA.CBDF0513@bigpond.net.au>
<3A5CB33B.A2D61F9B@bigpond.net.au>
<12256.979240778@sss.pgh.pa.us>
<3A5E6B9B.2E45D429@bigpond.net.au>
<13994.979267804@sss.pgh.pa.us>
<3A5E7305.485B36E8@bigpond.net.au>
<14121.979269213@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: aa2@bigpond.net.au, tgl@sss.pgh.pa.us, sszabo@kick.com
Subject: Re: Possible bug? WAS :Bad (null) varchar() external representation.
In-Reply-To: <3A5E80C4.54D654A0@bigpond.net.au>
References: <3A5CA5FA.CBDF0513@bigpond.net.au> <3A5CB33B.A2D61F9B@bigpond.net.au> <12256.979240778@sss.pgh.pa.us> <3A5E6B9B.2E45D429@bigpond.net.au> <13994.979267804@sss.pgh.pa.us> <3A5E7305.485B36E8@bigpond.net.au> <14121.979269213@sss.pgh.pa.us> <3A5E80C4.54D654A0@bigpond.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox