Re: SQLite to Postgres, II

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