Alter Data Type of a Column to Serial

Alter data type of a column to serial

A quick glance at the docs tells you that

The data types smallserial, serial and bigserial are not true types
but merely a notational convenience for creating unique identifier columns

If you want to make an existing (integer) column to work as a "serial", just create the sequence by hand (the name is arbitrary), set its current value to the maximum (or bigger) of your current address.new_id value, at set it as default value for your address.new_id column.

To set the value of your sequence see here.

SELECT setval('address_new_id_seq', 10000);

This is just an example, use your own sequence name (arbitrary, you create it), and a number greater than the maximum current value of your column.

---

Update: as pointed out by Lucas' answer (which should be the acccepted one) you should also specify to which column the sequence "belongs to" by using CREATE/ALTER SEQUENCE ... OWNED BY ...

Adding 'serial' to existing column in Postgres

Look at the following commands (especially the commented block).

DROP TABLE foo;
DROP TABLE bar;

CREATE TABLE foo (a int, b text);
CREATE TABLE bar (a serial, b text);

INSERT INTO foo (a, b) SELECT i, 'foo ' || i::text FROM generate_series(1, 5) i;
INSERT INTO bar (b) SELECT 'bar ' || i::text FROM generate_series(1, 5) i;

-- blocks of commands to turn foo into bar
CREATE SEQUENCE foo_a_seq;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
ALTER TABLE foo ALTER COLUMN a SET NOT NULL;
ALTER SEQUENCE foo_a_seq OWNED BY foo.a;    -- 8.2 or later

SELECT MAX(a) FROM foo;
SELECT setval('foo_a_seq', 5);  -- replace 5 by SELECT MAX result

INSERT INTO foo (b) VALUES('teste');
INSERT INTO bar (b) VALUES('teste');

SELECT * FROM foo;
SELECT * FROM bar;

How to change existing COLUMN type to SERIAL in postgres?

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 convert primary key from integer to serial?

serial is a pseudo data type, not an actual data type. It's an integer underneath with some additional DDL commands executed automatically:

1. Create a SEQUENCE (with matching name by default).
2. Set the column NOT NULL and the default to draw from that sequence.
3. Make the column "own" the sequence.

Details:

• Safely rename tables using serial primary key columns

A bigserial is the same, built around a bigint column. You want bigint, but you already achieved that. To transform an existing serial column into a bigserial (or smallserial), all you need to do is to ALTER the data type of the column. Sequences are generally based on bigint, so the same sequence can be used for any integer type.

To "change" a bigint into a bigserial or an integer into a serial, you just have to do the rest by hand:

• Creating a PostgreSQL sequence to a field (which is not the ID of the record)

The actual data type is still integer / bigint. Some clients like pgAdmin will display the data type serial in the reverse engineered CREATE TABLE script, if all criteria for a serial are met.

Changing primary key int type to serial

Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial;

• Pick a starting value for the serial, greater than any existing value in the table
  
  SELECT MAX(id)+1 FROM mytable

• Create a sequence for the serial (tablename_columnname_seq is a good name)
  
  CREATE SEQUENCE test_id_seq MINVALUE 3 (assuming you want to start at 3)

• Alter the default of the column to use the sequence
  
  ALTER TABLE test ALTER id SET DEFAULT nextval('test_id_seq')

• Alter the sequence to be owned by the table/column;
  
  ALTER SEQUENCE test_id_seq OWNED BY test.id

A very simple SQLfiddle demo.

And as always, make a habit of running a full backup before running altering SQL queries from random people on the Internet ;-)

Change datatype of column in Postgresql

I added another migration file with the following query

ALTER TABLE enqueries ALTER COLUMN id
ADD GENERATED BY DEFAULT AS IDENTITY;

It solved the problem.

postgresql alter table add column serial with sort values on created_at

Why add it to the table? You can just do:

select u.*, row_number() over (order by u.createdAt) as seqnum
from user u;

If you have an index on user(createdAt), then this should take advantage of the index.

If you have a unique column on the table, you can do an update:

update user u
set cid = uu.seqnum
from (select u.*, row_number() over (order by u.createdAt) as seqnum
from user u
) uu
on u.uuid = uu.uuid

How to change data type of a column in an SQL table from integer to decimal

Easy - just run this SQL statement

ALTER TABLE dbo.Budget
ALTER COLUMN ROE DECIMAL(20,2)   -- or whatever precision and scale you need.....

See the freely available MSDN documentation on what exactly the precision, scale and length in decimal numbers are and what ranges of values are possible

---

Related Topics