By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

pg7.3.4: pg_atoi: zero-length string

P: n/a


Hi,

We're currently experiencing a problem where SQL statements are failing
when entring a '' for not not-null integer columns:

ERROR: pg_atoi: zero-length string

This was discovered just after a database migration from 7.2 to 7.3.4.

Example:

insert into renewal_cache
(dom, expiry, issued, aid) values
('data','2004-03-05','19980305','')

The above example in just one case where 'aid' can accept a null value.
The use of quotes around all values was established as IIRC pg7.2
wouldn't accept statements without them. The use of this convention is
extensive.

Cheers,

Rob Fielding
Development
Designer Servers Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote:


Hi,

We're currently experiencing a problem where SQL statements are failing
when entring a '' for not not-null integer columns:
Yes. This behaviour was made more compliant in 7.3. It's in the
release notes.
The above example in just one case where 'aid' can accept a null value.
That's not a null. It's a zero-length string.
The use of quotes around all values was established as IIRC pg7.2
wouldn't accept statements without them. The use of this convention is
extensive.


You could probably put in a rewrite rule to convert '' to NULL and
allow nulls on the column. It's the only suggestion I can think of,
short of going back to 7.2.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<an****@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a


Andrew Sullivan wrote:
On Tue, Nov 04, 2003 at 11:21:35AM +0000, Rob Fielding wrote:

Hi,

We're currently experiencing a problem where SQL statements are failing
when entring a '' for not not-null integer columns:

Yes. This behaviour was made more compliant in 7.3. It's in the
release notes.

The above example in just one case where 'aid' can accept a null value.


I've found this is a feature of 7.3 to not treat a empty string as a
NULL integer type. Silly lazy me ;)

As it turned out it relatively trivial to fix the offending statements
on the few occasions where it has been valid to do this.

Consider this a non-issue.
Cheers,

--

Rob Fielding
Development
Designer Servers Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

P: n/a
On Tue, Nov 04, 2003 at 12:19:58 +0000,
Rob Fielding <ro*@dsvr.net> wrote:

I've found this is a feature of 7.3 to not treat a empty string as a
NULL integer type. Silly lazy me ;)


It didn't even then. It was treated as 0. Oracle is the DB that treats
empty strings as null values.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4

P: n/a

The above example in just one case where 'aid' can accept a null value.


That's not a null. It's a zero-length string.


I've found this is a feature of 7.3 to not treat a empty string as a
NULL integer type. Silly lazy me.

As it turned out it relatively trivial to fix the offending statements
on the few occasions where it has been valid to do this.

Consider this a non-issue.
Cheers,

--

Rob Fielding
Development
Designer Servers Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #5

P: n/a
On Tue, Nov 04, 2003 at 12:19:58PM +0000, Rob Fielding wrote:
I've found this is a feature of 7.3 to not treat a empty string as a
NULL integer type. Silly lazy me ;)


For the record, it _never_ treated it as NULL. It treated it as
"empty string". '' != NULL. In fact, !(NULL=NULL) & !(NULL!=NULL).
SQL uses three-valued logic.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<an****@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #6

P: n/a
For the record, it _never_ treated it as NULL. It treated it as
"empty string". '' != NULL. In fact, !(NULL=NULL) & !(NULL!=NULL).
SQL uses three-valued logic.

You're absolutely right. That explains why, when I quickly looked, some
are zero's and some are NULLs - the NULLs where NULLs and the zeros
where empty strings.

Two different bad-programming examples. If I actually used these
columns for anything whenever they didn't have non zero or null data in
them then I'd have probably been alot more careful about what went in them.

I presume that an 32bit integer of zero and a NULL are represented
differently in the database ? I suppose internally you aren't
representing a NULL within the context of a 32bit integer field and it
would just probably be magic pointer to the next field - some sort of
exercise in space squashing? I don't know anything about the internal
stucture of the tuples.

Dependant on the above, it would probably make sense to clean up the
database, especially considering these columns are also indexed.
Cheers

--

Rob Fielding
Development
Designer Servers Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.