CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Lukasz Szozda
Lukasz Szozda

Reputation: 175606

Snowflake - date constant/literal for ALTER TABLE ADD COLUMN

How to define constant/date literal that will work with ALTER TABLE ADD COLUMN?

According to documentation:

Date and Time Constants

Constants (also known as literals) refers to fixed data values. Snowflake supports using string constants to specify fixed date, time, or timestamp values. String constants must always be enclosed between delimiter characters. Snowflake supports using single quotes to delimit string constants.

For example:

date '2010-09-14'

and

Table Column Actions

  ADD COLUMN col_name col_data_type [ DEFAULT | AUTOINCREMENT ... ] [ inlineConstraint ] [, ...]

Adds a new column, including optionally adding a default and/or inline constraint for the column, to the table: For additional details about column defaults, see CREATE TABLE

...

DEFAULT <expr>

Column default value is defined by the specified expression which can be any of the following:

  • Constant value.

  • Simple expression

  • Sequence reference (seq_name.NEXTVAL)

For an existing table t:

CREATE TABLE t(d DATE);
INSERT INTO t(d) VALUES ('2000-01-01');

SELECT * FROM t;
-- D
-- 2000-01-01

-- addding new columns with default value works fine for these data types
ALTER TABLE t ADD COLUMN d2 INT DEFAULT 1;
ALTER TABLE t ADD COLUMN d3 STRING DEFAULT 'some string';

Let's try to add new date column with default value attached:

ALTER TABLE t ADD COLUMN d1 DATE DEFAULT '2000-01-01';
-- SQL compilation error: Default value data type does not match data type for column D1

ALTER TABLE t ADD COLUMN d1 DATE DEFAULT '2000-01-01'::DATE;
-- SQL compilation error: Invalid column default expression [CAST('2000-01-01' AS DATE)]

ALTER TABLE t ADD COLUMN d1 DATE DEFAULT date '2000-01-01';
-- SQL compilation error: Invalid column default expression [CAST('2000-01-01' AS DATE)]

-- default value during creation is possible
CREATE OR REPLACE TABLE t(d DATE DEFAULT date '2000-01-01');

It seems that date '2000-01-01',which should be date literal, is treated as expression [CAST('2000-01-01' AS DATE)] that is not a simple one. It works in context of CREATE TABLE but not in context of ALTER TABLE.

Is there is a way to provide contstant/simple expression that will work with ALTER TABLE for DATE column?

Upvotes: 2

Views: 4027

Answers (2)

narasimha
narasimha

Reputation: 1

The below statement will work to set default low and high dates. create or replace temporary table test ( id number );

insert into test values(1);
insert into test values(2);
insert into test values(3);

alter table test add column start_date date  default -313744;
alter table test add column end_date date  default 2525286;
select * from test;

Upvotes: 0

hgr
hgr

Reputation: 326

Here is a workaround using update statement:

Create the column_name with timestamp datatype

ALTER TABLE table_name ADD column_name DATE;

Since the newly create column will be null, you could update it:

UPDATE table_name SET column_name = cast('2022-01-01' as DATE) WHERE column_name IS NULL;

Upvotes: 0

Related Questions