From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | tango ward <tangoward15(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: psycopg2.DataError: invalid input syntax for integer: "" |
Date: | 2018-05-07 13:49:38 |
Message-ID: | e40dbb5a-199f-887b-15b6-9a94f031a813@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Sunday, May 6, 2018, tango ward <tangoward15(at)gmail(dot)com
> <mailto:tangoward15(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Philipp Kraus | 2018-05-07 13:52:29 | void function and view select |
Previous Message | David G. Johnston | 2018-05-07 13:40:39 | Re: psycopg2.DataError: invalid input syntax for integer: "" |