Insert null to integer type column in Redshift

0
votes

i am trying to modify an insert statement in Redshift. I added a column at the end of table. Previous last column is one of type integer, which I do not need to handle in my insert (has remained null so far). Can I insert null values to it somehow? The 'NULL AS[...]' expression obviously fails, with error message:

Amazon Invalid operation: column "search_result_size" is of type integer but expression is of type text;

Alternatively, I will try to skip this column during insert statement, but I prefer to declare its value as null each time. I have been searching this for quite some time now, no solution found so far. Any ideas would be greatly welcome.

Thank you!

2
sqlamazon-redshift
Simply use null, eg INSERT INTO t(a, b) SELECT 1, null;. Adding quotes around it makes it a text value. – Marth

2 Answers

2
votes

I actually managed to solve this by using CAST. So, I did:

[...], CAST (NULL AS INTEGER) AS column_name, [...]

It works fine now. I believe it could be considered a valid approach.

1
votes

Use the null keyword:

insert into the_table (col_1, col_2, col_3)
values (1, 2, null);

If you want to change the value for existing rows, you need UPDATE:

update the_table
    set the_column = null
where ...