CODEWITHSUNDEEP
postgresql
chhavi rai
chhavi rai

Reputation: 21

not able to convert integer data type to bigserial in postgres

For converting integer datatype to bigserial in postgres I have run below command but it didn't change its datatype but it's changing the modifiers

CREATE SEQUENCE id;
ALTER TABLE user_event_logs ALTER COLUMN id SET NOT NULL;
ALTER TABLE user_event_logs ALTER COLUMN id SET DEFAULT nextval('id');
ALTER SEQUENCE id OWNED BY user_event_logs.id;

After running this it is showing output like this 

Column Type  Modifiers  this all my column heading and id  integer  not null

i want to change type to bigserial

Upvotes: 2

Views: 5611

Answers (2)

Mabu Kloesen
Mabu Kloesen

Reputation: 1358

I am not sure, can you try this?

CREATE SEQUENCE id_seq;
ALTER TABLE user_event_logs ALTER COLUMN id TYPE BIGINT;
ALTER TABLE user_event_logs ALTER COLUMN id SET NOT NULL;
ALTER TABLE user_event_logs ALTER COLUMN id SET DEFAULT nextval('id_seq'::regclass);
ALTER SEQUENCE id_seq OWNED BY user_event_logs.id;

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51406

bigserial is a shortcut for bigint + sequence + default value, so if you want user_event_logs.id to be bigint, instead of int, use:

ALTER TABLE user_event_logs ALTER COLUMN id type bigint;

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

also:

The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column.

so if you want bigserial, just alter volumn type to bigint

Upvotes: 4

Related Questions