title: Add identity column to existing table in Snowflake?
body: <p>I have a table &quot;MY_TABLE&quot; in Snowflake that I would like to add an identity column to. I tried</p>
  <pre><code>ALTER TABLE &quot;MY_TABLE&quot; 
      ADD COLUMN primary_key int IDENTITY(1,1);
  </code></pre>
  <p>But this returns</p>
  <pre><code>SQL compilation error: Cannot add column 'PRIMARY_KEY' with non-constant default to non-empty table 'MY_TABLE'.
  </code></pre>
  <p>Is this just not possible in snowflake?</p>
  <p>To try to get around this limitation, I tried to create a temp version of the table</p>
  <pre><code>CREATE OR REPLACE TABLE &quot;MY_TABLE_TEMP&quot; LIKE &quot;MY_TABLE&quot;
  ALTER TABLE &quot;MY_TABLE_TEMP&quot; ADD COLUMN primary_key int IDENTITY(1,1)
  
  INSERT INTO &quot;MY_TABLE_TEMP&quot;
      SELECT * FROM &quot;MY_TABLE&quot;;
  </code></pre>
  <p>But now I get the error</p>
  <pre><code>SQL compilation error: Insert value list does not match column list expecting &lt;x+1&gt; but got &lt;x&gt;
  </code></pre>
  <p>Which sort of makes sense, since I am not passing the primary key. At this point it seems like I may have to manually enter the list of x (which is a very high number) of column names into the sql query, so I am wondering if I am just doing this all wrong. Has anyone else run into a similar issue?</p>