From: | tango ward <tangoward15(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: psycopg2.DataError: invalid input syntax for integer: "" |
Date: | 2018-05-07 23:44:25 |
Message-ID: | CAA6wQLJuduvdEsLWo0yXD_9twg0FxMjMjohAcYKhUCabaHcmFQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi All,
Thanks for the suggestions.
I managed to fix this by running CASE on the column. I also fix the %s to
avoid SQLi as per discussed in the documentation of psycopg2.
My apologies for consuming your time, it's my first time to work with DB
and DB migration.
Thanks,
J
On Mon, May 7, 2018 at 9:49 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> 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 | tango ward | 2018-05-08 03:07:55 | KeyError: self._index[x] |
Previous Message | Adrian Klaver | 2018-05-07 22:37:13 | Re: Run external command as part of an sql statement ? |