0

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?

2
  • 1
    How would org be derived?
    – Philᵀᴹ
    Feb 25 '20 at 20:44
  • 1
    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)?
    – jjanes
    Feb 25 '20 at 21:03
0

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 @Philᵀᴹ '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!

-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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.