How can I execute an `INSERT INTO` on a table with a string primary key?

Question

I have two tables that are initialized with something like this:

create table foo ( 
     "id" varchar(254) not null primary key, 
     "first_name" varchar(254) not null);

create table my_user (
     "id" serial not null primary key, 
     "role" varchar(254) not null,
     "first_name" varchar(254) not null);

The reason why the id column of foo is a varchar(254) instead of a serial is because in normal operations I'm inserting in an id provided by Google OAuth2 instead of generating my own id values.

I now have a set of records in a third table I call temp with the first_name column. I'm trying to emulate this post, but I'm not sure how to do so for string primary keys.

select * from (insert into my_user(id, role) 
    ('some id value I want to generate, like historical || incrementing number',
    [a fixed number],
    select first_name from temp) returning id);

As it says in the official Postgres documentation, I know I need to get the arguments following the insert statement into the format of a table that matches the declaration of my_user. I guess I'm just lost as to how to generate a column of the ids I want here, or even a column of one number repeating.

Thanks for reading

Stongly advise to make the PK a plain integer (possibly serial) and use that as a FK. Treat the OAUTH2 string as an alternate key (just add a UNIQUE constraint on it) — wildplasser
– wildplasser, Commented Aug 9, 2013 at 17:25

Community · Accepted Answer · 2017-05-23 11:50:13Z

0

You could insert a UUID (it's like a GUID) in your ID... It's guaranteed to be unique.

Sadly it's a little complex to load the module: Generating a UUID in Postgres for Insert statement?

Ah... and what wildplasser said, +1! :-)

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Aug 9, 2013 at 17:31

xanatos

112k13 gold badges209 silver badges296 bronze badges

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

2 Comments

Clodoaldo Neto Over a year ago

With 9.1+ as is the case just do create extension.

xanatos Over a year ago

@ClodoaldoNeto You still need administrative privileges, and must remember to do it on each server you use. If you work in a big enterprise, just thinking of doing it could be a nightmare. Compare it with SQL Server: NEWID(), no permissions to ask, no bureaucracy to fulfill.

Collectives™ on Stack Overflow

How can I execute an `INSERT INTO` on a table with a string primary key?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related