In postgres, we can add a new incremental column like:
ALTER TABLE <table_name>
ADD IF NOT EXISTS <column_name> SERIAL;
Which creates new column in the table with serial numbers. In snowflake when I tried the same command, it's throwing an error
SQL compilation error: Unsupported data type 'SERIAL'.
How can the same behavior be achieved in Snowflake?
The SERIAL pseudo-type is an auto-incremented integer with a storage size of four bytes and a range of one to 2,147,483,647.
You can use AUTOINCREMENT or IDENTITY to have the same feature:
create or replace table test ( id number AUTOINCREMENT, v varchar ) ;
insert into test (v) values ('Test1'),('Test2'),('Test3');
select * from test;
+----+-------+
| ID | V |
+----+-------+
| 1 | Test1 |
| 2 | Test2 |
| 3 | Test3 |
+----+-------+
PS: It's not possible to add such a column after creating the table.