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, Aug 09 '13 at 17:25

score 0 · Accepted Answer · edited May 23 '17 at 11:50

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 '17 at 11:50

Community

1
1

answered Aug 09 '13 at 17:31

xanatos

109,618
12
197
280

With 9.1+ as is the case just do `create extension`. – Clodoaldo Neto Aug 10 '13 at 12:38
@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. – xanatos Aug 10 '13 at 12:55

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

1 Answers1