Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.klaver@xxxxxxxxxxx> 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.g.johnston@xxxxxxxxx <mailto:david.g.johnston@gmail.com>> wrote:

    On Sunday, May 6, 2018, tango ward <tangoward15@xxxxxxxxx
    <mailto:tangoward15@xxxxxxxxx>> 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@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux