pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Possible bug? WAS :Bad (null) varchar() external representation.
6+ messages / 3 participants
[nested] [flat]
* Re: Possible bug? WAS :Bad (null) varchar() external representation.
@ 2001-01-10 18:30 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Justin Clift <aa2@bigpond.net.au>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2001-01-10 18:30 UTC (permalink / raw)
To: Justin Clift <aa2@bigpond.net.au>; +Cc: pgsql-sql
On Thu, 11 Jan 2001, Justin Clift wrote:
> I haven't seen a mention of a maximum number of constraints of similar
> applying to a table. If so, then could someone please point me to it...
>
> The reason I mention this is because I've found what seems to be causing
> this problem I'm experiencing with Postgres 7.03 :
>
> CREATE TABLE "staff_details" (
> "userid" character varying(24) NOT NULL,
> "password" character(13) NOT NULL,
> "name" character varying(96) NOT NULL,
> "role" int2 NOT NULL,
> "dob" date NOT NULL,
> "phone_one" character varying(14) NOT NULL,
> "phone_two" character varying(14),
> "phone_three" character varying(14),
> "address" character varying(280),
> "status" int2,
> "managers_notes" character varying(600),
> CONSTRAINT "staff_details_uesrid" CHECK ((length(userid) < 25)),
> CONSTRAINT "staff_details_password" CHECK ((length("password") <
> 14)),
> CONSTRAINT "staff_details_name" CHECK ((length(name) < 97)),
> CONSTRAINT "staff_details_dob" CHECK
> (date_ge(date(("timestamp"('2001-01-08'::date) - '18 years
> 00:00'::"interval")), dob)),
> CONSTRAINT "staff_details_phone_one" CHECK ((length(phone_one) <
> 17)),
> CONSTRAINT "staff_details_phone_two" CHECK ((length(phone_two) <
> 17)),
> CONSTRAINT "staff_details_phone_three" CHECK
> ((length(phone_three) < 17)),
> CONSTRAINT "staff_details_address" CHECK ((length(address) <
> 281)),
> CONSTRAINT "staff_details_managers_notes" CHECK
> ((length(managers_notes) < 601)),
> PRIMARY KEY ("userid")
> );
>
> When I attempt to insert data into this table, I get the following error
> :
>
> foobar=# insert into staff_details values ('0000111122223333',
> encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
> 2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
>
Current source seem to insert fine (don't have a 7.0 system to test on
anymore). Also, aren't alot of these length checks meaningless? I think
the values are converted to the correct type first, so the phone number
really shouldn't possibly be longer than 16 since it's a varchar(14).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Possible bug? WAS :Bad (null) varchar() external representation.
@ 2001-01-10 19:08 Justin Clift <aa2@bigpond.net.au>
0 siblings, 2 replies; 6+ messages in thread
From: Justin Clift @ 2001-01-10 19:08 UTC (permalink / raw)
To: pgsql-sql
Hi,
I haven't seen a mention of a maximum number of constraints of similar
applying to a table. If so, then could someone please point me to it...
The reason I mention this is because I've found what seems to be causing
this problem I'm experiencing with Postgres 7.03 :
CREATE TABLE "staff_details" (
"userid" character varying(24) NOT NULL,
"password" character(13) NOT NULL,
"name" character varying(96) NOT NULL,
"role" int2 NOT NULL,
"dob" date NOT NULL,
"phone_one" character varying(14) NOT NULL,
"phone_two" character varying(14),
"phone_three" character varying(14),
"address" character varying(280),
"status" int2,
"managers_notes" character varying(600),
CONSTRAINT "staff_details_uesrid" CHECK ((length(userid) < 25)),
CONSTRAINT "staff_details_password" CHECK ((length("password") <
14)),
CONSTRAINT "staff_details_name" CHECK ((length(name) < 97)),
CONSTRAINT "staff_details_dob" CHECK
(date_ge(date(("timestamp"('2001-01-08'::date) - '18 years
00:00'::"interval")), dob)),
CONSTRAINT "staff_details_phone_one" CHECK ((length(phone_one) <
17)),
CONSTRAINT "staff_details_phone_two" CHECK ((length(phone_two) <
17)),
CONSTRAINT "staff_details_phone_three" CHECK
((length(phone_three) < 17)),
CONSTRAINT "staff_details_address" CHECK ((length(address) <
281)),
CONSTRAINT "staff_details_managers_notes" CHECK
((length(managers_notes) < 601)),
PRIMARY KEY ("userid")
);
When I attempt to insert data into this table, I get the following error
:
foobar=# insert into staff_details values ('0000111122223333',
encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
Yet if I remove BOTH the "staff_details_phone_three" &
"staff_details_managers_notes" constraints it works :
foobar=# insert into staff_details values ('0000111122223333',
encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
INSERT 27605472 1
Removing EITHER of these constraints doesn't work, and neither does
removing any of the other constraints on this table. Just these two
TOGETHER. AND they're not even defined one-after-another possibly
indicating some formatting error.
Does anyone have an idea why this is occuring?
Regards and best wishes,
+ Justin Clift
Database Administrator
Justin Clift wrote:
>
> Hi all,
>
> I'm getting the following problem when trying to do a simple insert
> statement...
>
> "Bad (null) varchar() external representation"
>
> WHY?
>
> I'm running PostgreSQL 7.03 on Linux Mandrake 7.2 (using a specially
> compiled version, not an RPM).
>
> Here's the table :
>
> foobar=# \d staff_details
> Table "staff_details"
> Attribute | Type | Modifier
> ----------------+--------------+----------
> userid | varchar(24) | not null
> password | char(13) |
> name | varchar(96) |
> role | smallint |
> dob | date |
> phone_one | varchar(14) |
> phone_two | varchar(14) |
> phone_three | varchar(14) |
> address | varchar(280) |
> status | smallint |
> managers_notes | varchar(600) |
> Index: staff_details_pkey
> Constraints: (length(userid) < 25)
> (length("password") < 14)
> (length(name) < 97)
> (length(phone_one) < 17)
> (length(phone_two) < 17)
> (length(phone_three) < 17)
> (length(address) < 281)
> (length(managers_notes) < 601)
>
> foobar=# insert into staff_details values ('A', NULL, NULL, NULL, NULL,
> NULL, NULL, NULL, NULL, NULL, NULL);
> ERROR: Bad (null) varchar() external representation
> foobar=# insert into staff_details (userid, password, name, role, dob,
> phone_one) values ('0000111122223333', 'foobarbaz1234', 'Joshua', 1,
> '1970-07-01', '(03) 9867 5432');
> ERROR: Bad (null) varchar() external representation
> foobar=# insert into staff_details values ('0000111122223333',
> encrypt('foo'), 'Joshua', 1, '1970-07-01', '(03) 9867 5432', '(041) 309
> 2819', NULL, '1 Blankety-Blank Way\nBazzville', NULL, NULL);
> ERROR: Bad (null) varchar() external representation
>
> etc...
>
> I've tried everything I can think of, also exported and reloaded the
> database, etc. This is a new table with nothing in it.
>
> This is driving me nuts. :-(
>
> + Justin Clift
> Database Administrator
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug? WAS :Bad (null) varchar() external representation.
@ 2001-01-11 19:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Clift <aa2@bigpond.net.au>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-01-11 19:19 UTC (permalink / raw)
To: Justin Clift <aa2@bigpond.net.au>; +Cc: pgsql-sql
Justin Clift <aa2@bigpond.net.au> writes:
> I haven't seen a mention of a maximum number of constraints of similar
> applying to a table. If so, then could someone please point me to it...
There is no such limit that I know of.
> Yet if I remove BOTH the "staff_details_phone_three" &
> "staff_details_managers_notes" constraints it works :
Are you absolutely certain that that's how it went? I think the most
likely story is just that you were hitting the length(varchar)-barfs-
on-NULL bug, and got confused about which combinations you'd tried.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug? WAS :Bad (null) varchar() external representation.
@ 2001-01-12 02:27 Justin Clift <aa2@bigpond.net.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Justin Clift @ 2001-01-12 02:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi Tom,
I think you are right. It does seem to barf on NULLS and
length(varchar), regardless.
Thanks for your assistance.
BTW - How do things normally get added to the FAQ? I would like to add
something about length(varchar) and NULLS not working in PostgreSQL
7.0.x
Regards and best wishes,
Justin Clift
Database Administrator
Tom Lane wrote:
>
> Justin Clift <aa2@bigpond.net.au> writes:
> > I haven't seen a mention of a maximum number of constraints of similar
> > applying to a table. If so, then could someone please point me to it...
>
> There is no such limit that I know of.
>
> > Yet if I remove BOTH the "staff_details_phone_three" &
> > "staff_details_managers_notes" constraints it works :
>
> Are you absolutely certain that that's how it went? I think the most
> likely story is just that you were hitting the length(varchar)-barfs-
> on-NULL bug, and got confused about which combinations you'd tried.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug? WAS :Bad (null) varchar() external representation.
@ 2001-01-12 02:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Clift <aa2@bigpond.net.au>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-01-12 02:50 UTC (permalink / raw)
To: Justin Clift <aa2@bigpond.net.au>; +Cc: pgsql-sql
Justin Clift <aa2@bigpond.net.au> writes:
> BTW - How do things normally get added to the FAQ?
It's not a FAQ until it's been asked, um, frequently. But you can
try to talk Bruce Momjian into adding it, if you like.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug? WAS :Bad (null) varchar() external representation.
@ 2001-01-12 03:57 Justin Clift <aa2@bigpond.net.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Justin Clift @ 2001-01-12 03:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@kick.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-01-12 03:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-10 19:08 Possible bug? WAS :Bad (null) varchar() external representation. Justin Clift <aa2@bigpond.net.au>
2001-01-10 18:30 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-01-11 19:19 ` Re: Possible bug? WAS :Bad (null) varchar() external representation. Tom Lane <tgl@sss.pgh.pa.us>
2001-01-12 02:27 ` Re: Possible bug? WAS :Bad (null) varchar() external representation. Justin Clift <aa2@bigpond.net.au>
2001-01-12 02:50 ` Re: Possible bug? WAS :Bad (null) varchar() external representation. Tom Lane <tgl@sss.pgh.pa.us>
2001-01-12 03:57 ` Re: Possible bug? WAS :Bad (null) varchar() external representation. Justin Clift <aa2@bigpond.net.au>
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