Thread: BUG #5372: insert into fails with integer expression type char error

The following bug has been logged online:

Bug reference:      5372
Logged by:          Gray Watson
Email address:      gray.pg@mailnull.com
PostgreSQL version: 8.3.9
Operating system:   debian linux
Description:        insert into fails with integer expression type char
error
Details:

I have 2 tables which have the same schema (as far as I can see) but when I
try to do the following in psql:

INSERT INTO old_log SELECT * FROM log WHERE time < '2010-02-01';

I get the error:

ERROR: column "port" is of type integer but expression is of type character

old_log is:

 Column  |            Type             |                     Modifiers

---------+-----------------------------+------------------------------------
----------------
 port    | integer                     |
 session | character(32)               |
 account | text                        |
 slot    | text                        |
 comment | text                        | not null
 time    | timestamp without time zone | default ('now'::text)::timestamp(6)
with time zone
 extra   | text                        |
 addr    | text                        |

log is:

 Column  |            Type             |                     Modifiers

---------+-----------------------------+------------------------------------
----------------
 port    | integer                     |
 session | character(32)               |
 account | text                        |
 slot    | text                        |
 comment | text                        | not null
 time    | timestamp without time zone | default ('now'::text)::timestamp(6)
with time zone
 extra   | text                        |
 addr    | text                        |
Indexes:
    "log_account" btree (account)
    "log_time" btree ("time")

I tried making port have a default 0 and converting the null port values in
log to be 0.  I also dumped the table and looked at the fields and could not
see a text one.

Various different web searches leave me baffled.  I see some array casting
bullets in the 8.4.X release notes.  If that is the fix, is there a 8.3.10
in the works?

Thanks.

"Gray Watson" <gray.pg@mailnull.com> writes:
> I have 2 tables which have the same schema (as far as I can see) but when I
> try to do the following in psql:

> INSERT INTO old_log SELECT * FROM log WHERE time < '2010-02-01';

> I get the error:

> ERROR: column "port" is of type integer but expression is of type character

That's just bizarre.  The only thought that comes to mind is that the
error is in a trigger or rule attached to old_log, but you aren't
showing any such thing in your psql \d output.

If you pg_dump the tables and restore into another database, can you
still replicate the misbehavior?  In any case it might be helpful to
look at "pg_dump -s -t table" output for these two tables.

Another thing that might be relevant is whether you've ever dropped
any columns in either table?

            regards, tom lane

Ok.  I've reproduced this but I now think that it isn't a bug.

My schemas initially weren't the symetric when I was using select *.  Alas, when I did align my schemas later, I had
alreadyswitched to specifying a column list which then did not match the column list in the table.  An improvement
mightbe to give the destination port name as well as the source.  I was concentrating on this being a export issue not
acolumn mismatch. 

  ERROR:  column "b" is of type integer but expression is of type text

This would be better:

  ERROR:  source column "b" is of type integer but destination column or expression "a" is of type text

What do I need to do to cancel the bug?  Sorry to waste people's time with this.

gray


----------
This message was sent from a MailNull anti-spam account.  You can get
your free account and take control over your email by visiting the
following URL.

   http://mailnull.com/