1

i am trying to add new column which contain default time in existing table but it wont allow any current timestamp or '2019-11-14 00:00:00.000' this time format in snowflake.

patel94
  • 85
  • 2
  • 11
  • How are you adding the column (SQL command) and what error are you getting when trying to load data? Have you seen the Snowflake docs and what have you tried so far? https://docs.snowflake.net/manuals/user-guide/date-time-examples.html – Suzy Lockwood Feb 05 '20 at 20:22
  • alter table abc add column CREATED_DATE timestamp default current_timestamp() – patel94 Feb 05 '20 at 20:28
  • second one is: alter table abc add column CREATED_DATE timestamp default 2020-02-04 00:00:00.000 – patel94 Feb 05 '20 at 20:29
  • and its getting error : SQL compilation error: syntax error line 3 at position 43 unexpected '00'. – patel94 Feb 05 '20 at 20:30
  • This information is really helpful, you can also always go back and edit your question if any other details come to mind after you first post, hope that is helpful. – Rachel McGuigan Feb 06 '20 at 21:56
  • I tried to fix this way and it works: I add one column called created time and then update that column and set with value. – patel94 Feb 07 '20 at 19:51

3 Answers3

5

The only type of column default that can be added after table creation is a default sequence. All other column defaults are not supported.

One workaround could be to:

  1. create the new table format
  2. insert the old table's data into the new table
  3. when synced, pause writing to the old table
  4. run the swap with command to replace the new with the old
Allen
  • 406
  • 2
  • 8
1

While you cannot change the default value in this case, you can create a new table with columns having the default you need.

For example, create a tmp_table with the same structure and for the created_date column, use this:

CREATED_DATE TIMESTAMP_NTZ(9) DEFAULT CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ(9)),

... finish the create table statement with:

... as select * from orig_table;

Grant appropriate permissions and then:

alter table orig_table swap with tmp_table;
Beege
  • 665
  • 4
  • 18
0

Here is a workaround using update statement:

Create the column_name with timestamp datatype

ALTER TABLE table_name ADD column_name TIMESTAMP_TZ(9);

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

UPDATE table_name SET column_name = cast(CURRENT_TIMESTAMP() as TIMESTAMP_TZ(9)) WHERE column_name IS NULL;
hgr
  • 178
  • 1
  • 1
  • 10