How to set date column value to Null

Moderator: NorbertKrupa

Post Reply
clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

How to set date column value to Null

Post by clv100 » Wed Apr 03, 2013 8:38 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to set date column value to Null

Post by JimKnicely » Thu Apr 04, 2013 1:18 pm

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Re: How to set date column value to Null

Post by clv100 » Thu Apr 04, 2013 2:46 pm

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.

clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Re: How to set date column value to Null

Post by clv100 » Fri Apr 05, 2013 9:46 pm

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.

Post Reply

Return to “Python”