Migration - not null default '0' -> not null default 0 - confused

On Tuesday 30 March 2010 4:23:39 pm Wang, Mary Y wrote:

Hi,

I'm confused. I'm in the process of migrating to 8.3.8. I used pg_dump
and pg_restore command for migration. Here is my problem.
Here is my old table prior migration:
\d activity_log
Table "activity_log"
Attribute | Type | Modifier
-----------+----------------------+--------------------------
day | integer | not null default '0'
hour | integer | not null default '0'
group_id | integer | not null default '0'
browser | character varying(8) | not null default 'OTHER'
ver | double precision | not null default '0.00'
platform | character varying(8) | not null default 'OTHER'
time | integer | not null default '0'
page | text |
type | integer | not null default '0'
user_id | integer | not null default '0'

Here is my table after migration:
\d activity_log;
Table "public.activity_log"
Column | Type | Modifiers
----------+----------------------+-----------------------------------------
---- day | integer | not null default 0
hour | integer | not null default 0
group_id | integer | not null default 0
browser | character varying(8) | not null default 'OTHER'::character
varying ver | double precision | not null default 0::double
precision platform | character varying(8) | not null default
'OTHER'::character varying time | integer | not null
default 0
page | text |
type | integer | not null default 0
user_id | integer | not null default 0

Now, the source code doesn't work any more. Here is the SQL - INSERT INTO
activity_log
(day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES
(20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
and pgsql returned "ERROR: invalid input syntax for integer: """. My
understanding is that if the value is null, then it should set both the
group_id=0 and user_id=0. But it didn't do it. With the old table, this
SQL statement would work.

Any suggestions on what I need to do for the not null default values?

I'm running on Postgres 8.3.8 and RHEL 3.9.

Thanks
Mary Wang

8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an
integer i.e '0'::integer.

--
Adrian Klaver
adrian.klaver@gmail.com

Ok. Thanks. In that case, I'm going to have a lot of type casting issues. What's the best way to fix all tables? Write a script to alter those tables?
Any suggestions?

Mary Wang

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Tuesday, March 30, 2010 4:32 PM
To: pgsql-general@postgresql.org
Cc: Wang, Mary Y
Subject: Re: [GENERAL] Migration - not null default '0' -> not null default 0 - confused

On Tuesday 30 March 2010 4:23:39 pm Wang, Mary Y wrote:

Hi,

I'm confused. I'm in the process of migrating to 8.3.8. I used
pg_dump and pg_restore command for migration. Here is my problem.
Here is my old table prior migration:
\d activity_log
Table "activity_log"
Attribute | Type | Modifier
-----------+----------------------+--------------------------
day | integer | not null default '0'
hour | integer | not null default '0'
group_id | integer | not null default '0'
browser | character varying(8) | not null default 'OTHER'
ver | double precision | not null default '0.00'
platform | character varying(8) | not null default 'OTHER'
time | integer | not null default '0'
page | text |
type | integer | not null default '0'
user_id | integer | not null default '0'

Here is my table after migration:
\d activity_log;
Table "public.activity_log"
Column | Type | Modifiers
----------+----------------------+------------------------------------
----------+----------------------+-----
---- day | integer | not null default 0
hour | integer | not null default 0
group_id | integer | not null default 0
browser | character varying(8) | not null default 'OTHER'::character
varying ver | double precision | not null default 0::double
precision platform | character varying(8) | not null default
'OTHER'::character varying time | integer | not null
default 0
page | text |
type | integer | not null default 0
user_id | integer | not null default 0

Now, the source code doesn't work any more. Here is the SQL - INSERT
INTO activity_log
(day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES
(20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0',
''); and pgsql returned "ERROR: invalid input syntax for integer:
""". My understanding is that if the value is null, then it should
set both the
group_id=0 and user_id=0. But it didn't do it. With the old table, this
SQL statement would work.

Any suggestions on what I need to do for the not null default values?

I'm running on Postgres 8.3.8 and RHEL 3.9.

Thanks
Mary Wang

8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an integer i.e '0'::integer.

--
Adrian Klaver
adrian.klaver@gmail.com

On Tuesday 30 March 2010 4:49:42 pm Wang, Mary Y wrote:

Ok. Thanks. In that case, I'm going to have a lot of type casting issues.
What's the best way to fix all tables? Write a script to alter those
tables? Any suggestions?

Mary Wang

This might help:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

--
Adrian Klaver
adrian.klaver@gmail.com

On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote:

8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an
integer i.e '0'::integer.

I don't think that's accurate:

postgres=# select version();

version

--------------------------------------------------------------------------------------------------------------------
--
PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit
(1 row)

postgres=# create table ints(i int);
CREATE TABLE
postgres=# insert into ints values('0');
INSERT 0 1

Regards,
Jeff Davis

On Mar 30, 2010, at 4:23 PM, Wang, Mary Y wrote:

Hi,

I'm confused. I'm in the process of migrating to 8.3.8. I used pg_dump and pg_restore command for migration.
Here is my problem.
Here is my old table prior migration:
\d activity_log
Table "activity_log"
Attribute | Type | Modifier
-----------+----------------------+--------------------------
day | integer | not null default '0'
hour | integer | not null default '0'
group_id | integer | not null default '0'
browser | character varying(8) | not null default 'OTHER'
ver | double precision | not null default '0.00'
platform | character varying(8) | not null default 'OTHER'
time | integer | not null default '0'
page | text |
type | integer | not null default '0'
user_id | integer | not null default '0'

Here is my table after migration:
\d activity_log;
Table "public.activity_log"
Column | Type | Modifiers
----------+----------------------+---------------------------------------------
day | integer | not null default 0
hour | integer | not null default 0
group_id | integer | not null default 0
browser | character varying(8) | not null default 'OTHER'::character varying
ver | double precision | not null default 0::double precision
platform | character varying(8) | not null default 'OTHER'::character varying
time | integer | not null default 0
page | text |
type | integer | not null default 0
user_id | integer | not null default 0

Now, the source code doesn't work any more. Here is the SQL - INSERT INTO activity_log (day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
and pgsql returned "ERROR: invalid input syntax for integer: """.

You're trying to insert an empty string into group_id. An empty string is not a valid integer.

My understanding is that if the value is null, then it should set both the group_id=0 and user_id=0.

No, that's not the case. You can't insert a null into a not-null field. Also, you're not trying to insert a null unto group_id, you're trying to insert an empty string.

But it didn't do it. With the old table, this SQL statement would work.

I don't think it did. Maybe you changed something else at the same time?

abacus=> create table foo (bar integer not null default '0');
CREATE TABLE
abacus=> insert into foo (bar) values ('');
ERROR: invalid input syntax for integer: ""

Any suggestions on what I need to do for the not null default values?

It's nothing at all to do with them, I don't think - it's just that you're trying to insert bad data into the table.

You can either use the literal string "default" (with no quotes) to insert the default value into a field, or don't list the field in the list of fields to insert at all.

Cheers,
Steve

On Tuesday 30 March 2010 4:59:30 pm Jeff Davis wrote:

On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote:

8.3 tightened up type casting. You cannot INSERT a '0' without casting it
to an integer i.e '0'::integer.

I don't think that's accurate:

postgres=# select version();

version

---------------------------------------------------------------------------
----------------------------------------- --
PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit
(1 row)

postgres=# create table ints(i int);
CREATE TABLE
postgres=# insert into ints values('0');
INSERT 0 1

Regards,
Jeff Davis

My mistake. I could of swore I had problems with this when I first tried 8.3.
What happens when you rely on memory.

--
Adrian Klaver
adrian.klaver@gmail.com

Steve Atkins <steve@blighty.com> writes:

On Mar 30, 2010, at 4:23 PM, Wang, Mary Y wrote:

Now, the source code doesn't work any more. Here is the SQL - INSERT INTO activity_log (day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
and pgsql returned "ERROR: invalid input syntax for integer: """.

You're trying to insert an empty string into group_id. An empty string is not a valid integer.

My understanding is that if the value is null, then it should set both the group_id=0 and user_id=0.

No, that's not the case. You can't insert a null into a not-null field. Also, you're not trying to insert a null unto group_id, you're trying to insert an empty string.

But it didn't do it. With the old table, this SQL statement would work.

I don't think it did. Maybe you changed something else at the same time?

Mary's the one who's trying to port forward from some neolithic PG
version. A bit of experimentation shows that this did work (the integer
input routine would accept an empty string as meaning zero) up through
PG 7.2. Nothing to do with casting, just with the strictness of the
data type's input function.

regards, tom lane

On 31/03/2010 15:09, Tom Lane wrote:

Mary's the one who's trying to port forward from some neolithic PG
version.

Lots of pain, but hopefully lots of gain too! :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie