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

Re: SQLite to Postgres, II

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: pdxpug(at)postgresql(dot)org
Subject: Re: SQLite to Postgres, II
Date: 2011-02-17 16:03:49
Message-ID: alpine.LNX.2.00.1102170800300.13316@salmo.appl-ecosys.com (view raw or flat)
Thread:
Lists: pdxpug
On Thu, 17 Feb 2011, wes wrote:

> 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.

Wes,

   How interesting! That's the sqlite default: columns are not typecast but
assigned based on the content. The .sql file came from a sqlite dump. The
DATE columns were originally DATETIME, but I changed them since no times
were visible; I can change these back.

> 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:

   Since the file is ASCII (or the lower 127 chars for UTF-8), I wonder if I
can learn _why_ postgres is interpreting attribute type by content rather
than by the DDL.

Thanks,

Rich

In response to

pdxpug by date

Next:From: Rich ShepardDate: 2011-02-18 17:02:10
Subject: timestamp and input format
Previous:From: wesDate: 2011-02-17 15:49:33
Subject: Re: SQLite to Postgres, II

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