How to change existing COLUMN type to SERIAL in postgres?

Question

The following statement doesn't work:

ALTER TABLE my_table
ALTER COLUMN column_id set default nextval('my_table_column_id_seq');

So how to change id column to auto increment?

Error:

null value in column \"column_id"\ violates non null constraint.

This happens when I insert without column_id. I need it to be auto increment. Postgres Version is 12.

My insert:

INSERT INTO my_table (column2, column3)
VALUES ('rndmstring', 5);

@a_horse_with_no_name yet it produces that error when im trying to insert without id. — Sampo Kaarenmaa, Jan 18 '20 at 10:35
Then show us the code that does the INSERT and causes that error, not something unrelated. The `alter table` will **not** cause that error — , Jan 18 '20 at 10:37
Works for me: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=c8036ec1fd00ca7977fef7b868ad4635 — , Jan 18 '20 at 10:45

score 2 · Answer 1 · edited Jan 19 '20 at 19:36

You can create the sequence starting with the max value +1 of existing values for column_id :

CREATE SEQUENCE my_table_column_id_seq START WITH <MAX VALUE OF column_id+1>;

after checking out if this query

SELECT 1
  FROM my_table
 HAVING COUNT(distinct column_id)=COUNT(column_id)
    AND SUM(CASE WHEN column_id IS NULL THEN 0 ELSE 1 END)=
        SUM(CASE WHEN column_id IS NULL THEN 1 ELSE 1 END);

returns 1 for non-null and uniqueness of the concerned column's values, and then use your existing command

ALTER TABLE my_table
ALTER COLUMN column_id SET DEFAULT nextval('my_table_column_id_seq');

command and make sure all the existing values for column_id is unique, then issue :

ALTER TABLE my_table ADD PRIMARY KEY(column_id);

Demo

No. Insert statement wont go trough without entering is aswell. — Sampo Kaarenmaa, Jan 18 '20 at 10:38

score 0 · Accepted Answer · answered Jan 18 '20 at 10:51

Solved this. I had the id set as parameter in my endpoint for creating a new table.

So instead of this nodejs endpoint:

exports.createMy_table = async (column_id, column2, column3) => {
  try {
    const result = await client.query(
      "INSERT INTO my_table (column_id, column2, column3) VALUES ($1, $2, $3
      [column_id, column2, column3]
    );
    return result.rows;
  } catch (err) {
    throw new Error(err);
  }
};

I create without id.

 exports.createMy_table = async (column2, column3) => {
      try {
        const result = await client.query(
          "INSERT INTO my_table (column2, column3) VALUES ($1, $2,
          [column2, column3]
        );
        return result.rows;
      } catch (err) {
        throw new Error(err);
      }
    };

How to change existing COLUMN type to SERIAL in postgres?

2 Answers2