CODEWITHSUNDEEP
sqlnetezzaaltertemp
Kellan Baker
Kellan Baker

Reputation: 375

Add a date column to a temporary table in SQL

I am working with a temporary table in Netezza that contains the columns id, gender, start_date, and end_date. I am trying to add a new column to this table that contains a default date of 2019-01-01 for all rows. The table to which I want to add this column is a local temp table, so ALTER TABLE does not work ("Error: Operation not allowed on a temp table"). To get around this, I created a new temp table with an appropriately-named empty column, but now I find I cannot use UPDATE/SET to populate it with my date ("Error: Value too large for column"). What is the best way to alter my temp table to include a new column with my date in it? If possible, I would like to just directly modify the old temp table rather than creating a new one. Note: I do not have the necessary administrator privileges in the database I'm working in to just create a permanent table to ALTER instead.

The code that successfully created a new temp table with an empty column is:

DROP TABLE new_temp_table IF EXISTS;
GO
SELECT id, gender, start_date, end_date, NULL default_date
INTO TEMP TABLE new_temp_table
FROM old_temp_table;
GO

The code that does not work to replace the NULL values in the new column is:

UPDATE new_temp_table
SET default_date = '2019-01-01';
GO

Upvotes: 2

Views: 2680

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Cast NULL as the type you want:

SELECT id, gender, start_date, end_date,
       CAST(NULL as DATE) as default_date
INTO TEMP TABLE new_temp_table
FROM old_temp_table;

Netezza doesn't know what the type is, so it makes a guess. And that guess is unlikely to be a date (I think it is an int).

Upvotes: 2

Related Questions