Insert columns from two tables to a new table in PostgreSQL

Question

I am building an application to manage an inventory and I have a problem when creating my tables for the database (I am using PostgreSQL). My problem is the following:

I have two tables, one called 'products' and one called 'users'. Each one with its columns (See image). I want to create a third table called 'product_act_register' , which will keep a record of activity of the products and has with it the columns id, activity_type, quantity, date. But, I want to add other columns which are taken from the table 'users' and 'products'.

It should look like this (Image)

Where product_id, product_name, product_category, product_unit are taken from the table 'products' and the column 'user_id' is taken from the table 'users'.

How can I do this with PostgreSQL ?

Answer 1

Your description and your precise goals are very unclear. You didn't tell us what should happen in the different cases (only a product exists or only a user exists or none exist or both exist). You also didn't tell us how the other columns not coming from these tables should be filled. You furthermore didn't tell us how the tables users and products depend on each other. Basically you con do something like this if you only want to do an insert if both tables have an entry:

INSERT INTO product_acts_register 
SELECT 1,'ActivityType1',p.id, p.name, p.category, 
p.unit, u.id, 100, CURRENT_DATE
FROM products p JOIN users u ON p.id = u.id;

(Since you didn't tell us how or if to join them, I assumed to join on their id column)

If you don't care about this, but want to insert an entry for any possible combination of users and products, you can just select both tables without joining:

INSERT INTO product_acts_register 
SELECT 1,'ActivityType1',p.id, p.name, p.category, 
p.unit, u.id, 100, CURRENT_DATE
FROM products p, users u;

You can replicate this here and try out other commands: db<>fiddle

Please be more precise and give us more information when asking the next question.