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.kla...@aklaver.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.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote: >> >> On Sunday, May 6, 2018, tango ward <tangowar...@gmail.com >> <mailto:tangowar...@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.kla...@aklaver.com >
