Why doesn't knex create serial column in postgres?

Question

I use knex to create a postgres table as following:

knex.schema.createTable('users', table => {
    table.bigIncrements('user_id');
    ....
})

But after the table was created, the column user_id is a integer not the serial as expected.

The sql get by the pgAdmin is as following:

CREATE TABLE public.users
(
    user_id bigint NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
    ....
)

And the consequence is that when I do insert statement, the user_id won't auto increment as expected.

Any gives?

====================

Currently I just changed to mysql connection, and the inserting works well. But if I changed the database back to postgresql, then inserting would fail due to the duplication of user_id. The code can be found here: https://github.com/buzz-buzz/buzz-service

Eelke · Accepted Answer · 2018-01-21 09:00:18Z

3

serial and bigserial are not real types they are just shorthand for what pgAdmin is showing.

You will also find that a sequence has been created with the name users_user_id_seq when you look under sequences in pgAdmin.

answered Jan 21, 2018 at 9:00

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeff Tian Over a year ago

What should I do to let the postgresql auto increments the user_id when I do inserting? I changed the connection string to mysql the inserting would be fine, but if I changed back to postgresql then inserting would be fail due to the duplication of user_id. The code can be found here: github.com/buzz-buzz/buzz-service

Eelke Over a year ago

Please post relevant code in the question, I and many other will not be going to dig through your repository to find the problem. Also I'm not familiar with knex.js only with postgresql. However make sure not to try to insert an actual value for user_id. Either the column shouldn't be included in the INSERT statement or DEFAULT should be passed as it's value.

Eelke Over a year ago

Note that when you have inserted actual values directly into the user_id column the sequence will not know this and might generate values you have inserted previously resulting in the same error. Check in pgAdmin that the current value of the sequence is higher then the highest is in your table.

Collectives™ on Stack Overflow

Why doesn't knex create serial column in postgres?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related