What is the easiest way to set a field NULL in a strategy file.

[Dansong00]

Hi, i'm trying to set an integer foreign key field to NULL with a column update.

i've tried the following changes to my strategy file, both of which produce exceptions.

  - table_name: sample_table
    type: update_columns
    columns:
      - column_name: foo_fk_column
        type: literal
        value: NULL

ERROR: column "none" does not exist

..."foo_fk_column" = None;

The other option

  - table_name: sample_table
    type: update_columns
    columns:
      - column_name: foo_fk_column
        type: empty

setting the column update type to empty sets the integer foreign key field to a string literal.

'foo_fk_column' = (' ')

What is the proper way to set a nullable integer field NULL?

[rwnx]

Ha! Here I think you're getting burned by YAML's parsing of NULL into Python's native null type None

If you want to pass the value through unchanged you need to send it as a string:

  - table_name: sample_table
    type: update_columns
    columns:
      - column_name: foo_fk_column
        type: literal
        value: "NULL"

[Dansong00]

Thanks! Another option i tried that worked was using a before and update script.

scripts:
  before:
    - UPDATE sample_table SET foo_fk_column = NULL WHERE foo_fk_column IS NOT NULL;