Recreate table from a select and add an extra datetime default column (Snowflake)-CodePudding

I'm having problems creating a table that should be pretty straightforward. The SQL code (Snowflake) is:

create or replace table bank_raw as 
select 
    *,
    created_at datetime default current_timestamp()
from bank_raw;

My error is: Syntax error: unexpected 'DEFAULT'. (line 12).

I don't know how I can recreate this table and add this default timestamp column. By the way, I have already created multiple tables from scratch with created_at DateTime default current_timestamp().

Any ideas?

CodePudding user response：

It is possible to define column list definition when using CTAS:

Sample data:

CREATE TABLE bank_raw(id INT, col TEXT);
INSERT INTO bank_raw(id, col) VALUES (1, 'a'), (2,'b');

Query:

CREATE OR REPLACE TABLE bank_raw(id INT,
                                col TEXT,
                                created_at datetime default current_timestamp())
AS
SELECT 
  id, col, CURRENT_TIMESTAMP()
FROM bank_raw;

Output:

SELECT * FROM bank_raw;

DESCRIBE TABLE bank_raw;

CodePudding user response：

Since this is a DML operation not a DDL operation, the default keyword does not apply. You can simply remove it and instead project the column and name it:

create or replace table bank_raw as 
select 
    *,
    current_timestamp() as created_at
from bank_raw;

Edit: To enforce a default, you cannot alter a table to add a column with a default value except for sequences. So you'd need to do something like this:

select get_ddl('table','BLANK_RAW');

-- Copy and paste the DDL. Rename the new table,
-- and add the default timestamp:
create or replace table A 
(
    -- Existing columns here then:
    created_at timestamp default current_timestamp
);

You can then do an insert from a select on the table BLANK_RAW. You'll need to specify a column list and omit the CREATED_AT column.