psycopg2.IntegrityError: null value in column "uid" violates not-null constraint

[djdevin]

Strange issue where an integer 0 is being converted to a NULL and breaking the copy as the resulting table does not allow nulls.

The row in question here has an int(11) 0 in the uid column.

If I just delete the row with the 0, it will copy fine.

Coming from tap-mysql it looks like it is providing the correct value for the row:

{
  "type": "RECORD",
  "stream": "user",
  "record": {
    "name": "",
    "mail": "",
    "access": "1970-01-01T00:00:00+00:00",
    "uid": 0,
    "field_participant_category_value": null,
    "roles": null,
    "login": "1970-01-01T00:00:00+00:00",
    "theme": "",
    "created": "1970-01-01T00:00:00+00:00",
    "status": 0,
    "uuid": "dde83629-ca10-42d6-b376-e4c26009c384"
  },
  "version": 1539107389565,
  "time_extracted": "2018-10-09T17:49:49.591099Z"
}

INFO COPY user_temp ("access", "created", "field_participant_category_value", "login", "mail", "name", "roles", "status", "theme", "uid", "uuid") FROM STDIN WITH (FORMAT CSV, ESCAPE '\')
Traceback (most recent call last):
  File "/usr/bin/target-postgres", line 11, in <module>
    sys.exit(main())
  File "/usr/lib/python3.6/site-packages/target_postgres/__init__.py", line 138, in main
    state = persist_lines(config, input)
  File "/usr/lib/python3.6/site-packages/target_postgres/__init__.py", line 113, in persist_lines
    stream_to_sync[stream_name].load_csv(csv_files_to_load[stream_name], count)
  File "/usr/lib/python3.6/site-packages/target_postgres/db_sync.py", line 172, in load_csv
    file
psycopg2.IntegrityError: null value in column "uid" violates not-null constraint
DETAIL:  Failing row contains (1970-01-01 00:00:00, 1970-01-01 00:00:00, null, 1970-01-01 00:00:00, null, null, null, null, null, null, dde83629-ca10-42d6-b376-e4c26009c384).
CONTEXT:  COPY user_temp, line 1: ""1970-01-01T00:00:00+00:00","1970-01-01T00:00:00+00:00",,"1970-01-01T00:00:00+00:00",,,,,,,"dde83629..."

[koszti]

try it with PR #9 . Interesting that we find the same issues independently :)

[djdevin]

I confirmed this fixes the issue, thanks!

[robmoore]

I'm running into this same issue. Any chance this could be merged in?

[ifokeev]

@robmoore try this, maybe it will for you https://github.com/datamill-co/target-redshift

[djdevin]

That project doesn't support STATE which is a no-go for us. No way we could possibly do full table reloads.

@robmoore I made a fork with this and #6 that seems to be working well for us

pip install git+https://github.com/djdevin/target-postgres.git

[robmoore]

@djdevin You might want to take a look at the 'other' version of target-postgres: https://github.com/datamill-co/target-postgres

I've been using it and found it handles things like null values better. Also, it seems to be more actively developed.