Re: psycopg2.DataError: invalid input syntax for integer: ""

On 05/07/2018 12:28 AM, tango ward wrote:
> I think I've found the culprit of the problem.
> 
> I have a field which is varchar from the source DB while on the 
> destination DB its integer.
> 
> Reading the documentation: 
> http://www.postgresqltutorial.com/postgresql-cast/ but it gives me error 
> `psycopg2.DataError: invalid input syntax for integer: ""`

Would need to see your code to be sure, but I am gong to guess you are 
trying to CAST the string to integer in the SQL e.g CAST(some_str_value 
AS INTEGER) or some_str_value::integer. The error you are getting is :

test=# select CAST('' AS INTEGER);
ERROR:  invalid input syntax for integer: ""
LINE 1: select CAST('' AS INTEGER);
                     ^
test=# select ''::integer;
ERROR:  invalid input syntax for integer: ""
LINE 1: select ''::integer;

Two options:

1) You will need the catch the '' on the Python side before they get to 
the database and turn them into None(if Nulls allowed in column) or 0 
otherwise.

2) If possible convert the integer column on the destination db to a 
varchar one. Though I would do some investigation before doing this as 
this may very well mess up other code.

> 
> On Mon, May 7, 2018 at 2:39 PM, David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Sunday, May 6, 2018, tango ward <tangoward15@gmail.com
>     <mailto:tangoward15@gmail.com>> wrote:
> 
>         Yes, my apologies.
> 
>         May I also ask if there's a limitation for the number of
>         timestamp with timezone fields in a table?
> 
> 
>     Not one that is likely to matter in practice.  There's a page
>     discussing limitations on the website/docs somewhere if you wish to
>     find out more.
> 
>     David J.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com