1

Problem

I can add an auto-incrementing primary key to a pre-existing column in an empty table in postgres, but I wonder if it can be done more efficiently.

What I've Done

Before the table gets populated, I need to alter a column to add an auto-incrementing primary key. Similar to the answer to this question, the following will work (assuming the table is named test and the column in question is named col1):

ALTER TABLE test ADD PRIMARY KEY (col1);
CREATE SEQUENCE seq OWNED BY test.col1;
ALTER TABLE test ALTER COLUMN col1 SET DEFAULT nextval('seq');
UPDATE test SET col1 = nextval('seq');

Four lines is far from the end of the world. However, as per that answer, this can be done in one line if we're adding a column rather than altering a pre-existing one:

ALTER TABLE test ADD COLUMN col1 SERIAL PRIMARY KEY;

Question

Is there a way to do that in one line, but for a pre-existing column? It seems like SERIAL is limited to when one adds a new column, but I figured it can't hurt to ask. My naive attempts included things like:

  • ALTER TABLE test ALTER COLUMN col1 SERIAL PRIMARY KEY;
  • ALTER TABLE test ADD SERIAL PRIMARY KEY (col1);

Thanks!

EDIT: This got marked as a duplicate right off the bat, though I read both of those questions coming into this. I feel like they both use the same methodology that I'm already using (unless I misunderstood what was at play), and my question is about seeing if there's a more efficient way to do it, especially since there is in new column creation.

erekalper
  • 857
  • 9
  • 22
  • @a_horse_with_no_name I feel like this isn't a duplicate of those two questions from a few years ago - they basically use the same technique that I'm currently using, which isn't what my question is about. – erekalper Mar 14 '19 at 15:03
  • There is no way you can change an existing column to a "serial" with just a single line. –  Mar 14 '19 at 15:11
  • Okay then! If you want to put that as an answer I'll happily accept. That's all I was looking for, as I couldn't find it anywhere else. – erekalper Mar 14 '19 at 15:14

0 Answers0