Page 1 of 1
How to set date column value to Null
Posted: Wed Apr 03, 2013 8:38 pm
by clv100
We are converting from an Informix database to Vertica. All of our stored procedures have to be converted to python scripts (and we are completely newbies to python). The basic structure of all our stored procedures was to initialize column variables and use them when the row was inserted, re-setting the column values that were either an insert or an update based on the data coming in. I created a function to initialize the column variables and I then assign the values to the variables from the input staging table. Not all the values will be there so the columns will retain the initialized values. I am having a problem with a table that has a 'date' data type. I have set the variable for the date to 'None' because the data coming in will not necessarily have data for the date column. However, when I try to insert a row with the date column set to 'None' - I receive the following error:
Column "None" does not exist
I have tried everything I can think of and googled but can not find any way around this. Any help would be greatly appreciated!!!
Thanks,
Cece V.
Re: How to set date column value to Null
Posted: Thu Apr 04, 2013 1:18 pm
by JimKnicely
Hi!
I think you may be actually trying to insert the word none into a date field...
Here's an example from vsql:
Code: Select all
dbadmin=> create table d (date1 date);
CREATE TABLE
dbadmin=> insert into d values (none);
ERROR 2624: Column "none" does not exist
I think you want to use NULL:
Code: Select all
dbadmin=> insert into d values (NULL);
OUTPUT
--------
1
(1 row)
I hope this helps!
Re: How to set date column value to Null
Posted: Thu Apr 04, 2013 2:46 pm
by clv100
Thanks! I have a function that initializes the columns for the table row (these are listed as global variables). For example:
key_value = 0
code = None
start_date = None
description = None
quantity = 0
This is where I am having the problem. The data that comes in could have nothing in those columns. I pass the columns as a list to the function that inserts or updates the table row. If I have initialized the column to None and there are NO values in the input data for that column, it is actually setting the literal None as a column value. How can I avoid that??
Thanks,
Cece V.
Re: How to set date column value to Null
Posted: Fri Apr 05, 2013 9:46 pm
by clv100
I ended up figuring it out - although not very elegantly. I had to format my insert string column by column, checking if the value was there for each column and setting it to "NULL" if it wasn't with a format string of %s and if there was a value, to the value (in this case a date) with a format of '%s'. Very laborious and then it took HOURS to load 4000 records through the python script. Will end up having to do a Data Stage job and then a MERGE.