CODEWITHSUNDEEP
snowflake-cloud-data-platform
Siddaram H
Siddaram H

Reputation: 1176

Create serial numbers in a new column

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?

Upvotes: 1

Views: 937

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10039

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 |
+----+-------+

https://community.snowflake.com/s/question/0D50Z00008xAsQ0/how-to-set-auto-identity-column-from-alter-table-statement

PS: It's not possible to add such a column after creating the table.

Upvotes: 5

Related Questions