How do I write a query that inserts values while AND gets values from another table?

Question

I have a table (Tbl2) that needs to be populated with values from another table (Tbl1).

Tbl1 has columns: id (uuid), name, address, gender.

Tbl2 has columns: id (uuid), org, name, address, gender, createdAt, updatedAt.

I want to copy id, name, address, gender from tbl1 into tbl2. How Do I write a query that inserts those values into Tbl2 while also providing new ids, org, createdAt, and updatedAt values?

• Philᵀᴹ: How would org be derived?
• jjanes: You say you want both copy "id", and generate it. You can't do both. Do you need two different id columns in tbl2 (id and tbl1_id)?

Answer 1

You need to look at the DEFAULT clause. I think you want something like the following as an example:

CREATE TABLE tbl2
(
 id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
 org TEXT NOT NULL DEFAULT SUBSTRING(MD5(RANDOM()::TEXT) FROM 1 FOR 4),
 name TEXT NOT NULL,
 address TEXT NOT NULL,
 gender CHAR(1) NOT NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 update_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 CONSTRAINT tbl2_pk PRIMARY KEY (id)
);

Note that I am using

 id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT))

as the PRIMARY KEY. This is close enough to a UUID for the purposes of this answer - I'm using dbfiddle (see this link) and can't install the UUID extension. I'm also using a SUBSTRING of the same function to generate the org field values, since I don't know exactly what that is - I would have thought that a reference table to things like the UN, EU, NATO, ASEAN... &c. would be more appropriate for this, but then, only you know that - see @PhilTM 's com

Then populate it with some values:

INSERT INTO tbl2 (name, address, gender)
VALUES
('Name_23', 'Address_23', 'M'),
('Name_24', 'Address_24', 'F'),
('Name_25', 'Address_25', 'M');

Notice how I don't have to specify the id or the org fields since there's a DEFAULT in place. Now, we do the same for tbl1.

CREATE TABLE tbl1
(
 id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
 name TEXT NOT NULL,
 address TEXT NOT NULL,
 gender CHAR(1) NOT NULL,
 CONSTRAINT tbl1_pk PRIMARY KEY (id)
);

and:

INSERT INTO tbl1 (name, address, gender)
VALUES
('Name1', 'Address_1', 'M'),
('Name2', 'Address_2', 'F'),
('Name3', 'Address_3', 'M');

And then the INSERT is performed:

INSERT INTO tbl2 (name, address, gender)
SELECT name, address, gender
FROM tbl1 
-- WHERE tbl.id NOT IN (SELECT id FROM tbl1) -- MD5 should ensure uniqueness
RETURNING name, address, gender; -- this is optional - check it out.

And then we check our tbl2:

SELECT * FROM tbl2;

Result:

id org name address gender created_at update_at
1feaceed5c2d8e37f5565d222b5bd31c 28a1 Name_23 Address_23 M 2020年02月26日 05:43:28.366588+00 2020年02月26日 05:43:28.366588+00
508c0eecc16e305687b355c684cd5d56 d057 Name_24 Address_24 F 2020年02月26日 05:43:28.366588+00 2020年02月26日 05:43:28.366588+00
247d6d7bdcef078c4b4f7bef33e91d01 78a5 Name_25 Address_25 M 2020年02月26日 05:43:28.366588+00 2020年02月26日 05:43:28.366588+00
07bcc6cf6a3a3b79fcc05bafe9ebc8c2 05f8 Name1 Address_1 M 2020年02月26日 05:43:28.395899+00 2020年02月26日 05:43:28.395899+00
029ef1b1c21df2a0fe25da352114aa1f 48c2 Name2 Address_2 F 2020年02月26日 05:43:28.395899+00 2020年02月26日 05:43:28.395899+00
f4f51eccf9d4bfa7e011bdc4b2c135b8 8723 Name3 Address_3 M 2020年02月26日 05:43:28.395899+00 2020年02月26日 05:43:28.395899+00

A simple inspection shows that our INSERT has worked. Finally, notice that our created_at and updated_at fields have also been included - as the default is NOW(). In order for the updated_at field to be modified automatically, you'll need to use a TRIGGER - if you do a search, there are millions on the all-singing, all-dancing web. If this doesn't answer your question, let me know and I'll see what I can do. p.s. welcome to the forum!

Answer 2

Create a table mapping tbl1.id to the new values, then just insert into tbl2 a SELECT that joins those tables. Here's an example:

https://www.db-fiddle.com/f/eSRqTAdH9b2cwfuJfwrJ1B/0