Incrementing PK column from specific number(PostgreSQL)

Question

In my PostgreSQL database I have table users which has two columns, PK named uid(serial) and user_type(integer). Is it possible that for regular users (of user_type 1) uid starts from 0 (and increments regularly), and for non-regular users (of user_type 2), uid starts from (let's say) 5000, and to increments from that number (5000, 5001, 5002...) each time new non-regular user is added?

I won't have more than 2000 regular users, so overlap between uids of regular and non-regular users will never happen.

You are putting a meaning into a primary key - a very bad idea - especially because you are "duplicating" the information that is already available in the user_type column. — user330315
– user330315, Commented Nov 28, 2013 at 9:44
I am not doing this so I can make difference between regular and non-regular users. For that I have user_type column. I am doing this because I will have to fetch users from different database, and keep their uid, and write them in mine. So, if I have(in mine db) non-regular user with uid 1500, and I am fetching regular user from different database who also have uid 1500 there will be error and I won't be able to insert that regular user... — anagarD
– anagarD, Commented Nov 28, 2013 at 14:05
@a_horse_with_no_name good idea, I'll put some useful links for that in my answer — mabi
– mabi, Commented Nov 28, 2013 at 14:16

mabi · Accepted Answer · 2013-11-28 14:15:31Z

4

A serial data type will create a sequence and pull the default value for your column out of that sequence. For what you're trying to do you'd need two sequences, pull from these sequences and insert the uid explicitly. This is not something the serial can nor should do.

To echo @a_horse_with_no_name, you shouldn't put information into a serial data type. A generated primary key is only acceptable if it is completely opaque to the table users. Please consider just letting serial do its work and update your application code to react properly to user_type. Since your concern seems to be id collision with external entities, I'd suggest storing the user ids generated by an external system in a separate field, say extern_uid.

Or have the external system generate UUID strings you can safely use in your uid column. If the external system is a PostgresSQL database as well, you might use the uuid-ossp module to generate the UUID/GUID.

If you absolutely have to use sequences, you'd need to:

CREATE SEQUENCE uid_one START 1;
CREATE SEQUENCE uid_two START 5000;

INSERT INTO user (uid, user_type) VALUES (nextval('uid_one'::regclass), 1);
INSERT INTO user (uid, user_type) VALUES (nextval('uid_two'::regclass), 2);

Selecting the appropiate statement is left to the application.

edited Nov 28, 2013 at 14:15

answered Nov 28, 2013 at 10:00

mabi

5,3042 gold badges45 silver badges82 bronze badges

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

6 Comments

anagarD Over a year ago

So, I can have two sequences for one PK column(uid)? Can you please show me a piece of code or something...

mabi Over a year ago

@anagarD I've updated the answer, but please consider alternatives to your table design.

user330315 Over a year ago

@anagarD: well you could use two sequences to populate the column from inside a trigger. But again: that is a very bad idea.

anagarD Over a year ago

Problem is that table 'user' is main table for my app, and I can't add any other column, and every user must be stored in that table. External users will be of user_type 3. External system is MySQL db.

mabi Over a year ago

@anagarD Well, then using two sequences seems to be your only option. You can use a trigger to ease the selection burden, but be warned that maintaining this setup will be a continued nightmare.

|

Collectives™ on Stack Overflow

Incrementing PK column from specific number(PostgreSQL)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related