Skip site navigation (1) Skip section navigation (2)

Re: SQLite to Postgres, II

From: wes <pdxpug(at)the-wes(dot)com>
To: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
Cc: pdxpug(at)postgresql(dot)org
Subject: Re: SQLite to Postgres, II
Date: 2011-02-17 15:49:33
Message-ID: AANLkTikASi+3ns_1dqJPqtiYSWkRHjJyuSoO_4Lj5C4C@mail.gmail.com (view raw or flat)
Thread:
Lists: pdxpug
On Thu, Feb 17, 2011 at 6:24 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>wrote:

> On Wed, 16 Feb 2011, Rich Shepard wrote:
>
>   I have a problem that's probably a datatype mis-match and I'm just not
>> seeing it. The error reported is:
>>
>
>  I wonder if this bug report might be the reason:
>
>      <http://archives.postgresql.org/pgsql-bugs/2010-03/msg00239.php>
>
> I've no idea if emacs and joe are saving the file as UTF-8 and I've
> absolutely no idea whether there's a Bite Order Marker (BOM) anywhere.
>
>
>
I don't think it's this. I copied and pasted the create table statement and
the insert statement into my own testing DB and got the same error. I fixed
it by putting single-quotes around the data for that column, and then got
the same error for a different column. it seems it's interpreting data types
based on the first character. so for a column whose first character is a
numeral, PG believes it's a number, and throws an error on the alphabetical
component.

Pretty much, you have to quote all strings, and NOT quote all integers. Once
I fixed all those, then it complained about your date column:

ERROR:  column "date_log_rcvd" is of type timestamp without time zone but
expression is of type bigint
HINT:  You will need to rewrite or cast the expression.

The value of this column being inserted is 710179200000. I don't recognize
this format, so PG probably doesn't either.

-wes

In response to

Responses

pdxpug by date

Next:From: Rich ShepardDate: 2011-02-17 16:03:49
Subject: Re: SQLite to Postgres, II
Previous:From: Rich ShepardDate: 2011-02-17 14:24:27
Subject: Re: SQLite to Postgres, II

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group