Insert multiple values with foreign key Postgresql

Question

I am having trouble figuring out how to insert multiple values to a table, which checks if another table has the needed values stored. I am currently doing this in a PostgreSQL server, but will be implementing it in PreparedStatements for my java program.

user_id is a foreign key which references the primary in mock2. I have been trying to check if mock2 has values ('foo1', 'bar1') and ('foo2', 'bar2').

After this I am trying to insert new values into mock1 which would have a date and integer value and reference the primary key of the row in mock2 to the foreign key in mock1.

mock1 table looks like this:

===============================
| date  |  time    |  user_id |
| date  |  integer |  integer | 
|       |          |          |

And the table mock2 is:

==================================
| Id      |  name   |  program   |
| integer |  text   |  test      |

Id is a primary key for the table and the name is UNIQUE.

I've been playing around with this solution https://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key

However, I haven't been able to make it work. Could someone please point out what the correct syntax is for this, I would be really appreciative.

EDIT:

The create table statements are:

CREATE TABLE mock2(
   id SERIAL PRIMARY KEY UNIQUE,
   name text NOT NULL,
    program text NOT NULL UNIQUE
);

and

CREATE TABLE mock1(
   date date,
   time_spent INTEGER,
   user_id integer REFERENCES mock2(Id) NOT NULL);

Hey, apologies for the bad explanation. I'm not adding anything I was just trying to show the layout of the database. I've changed the + with | and added the create statements. Thank you, — MrRobot, Aug 24 '18 at 10:58
Please [edit] your question and also add the code that doesn't work. The linked question seems to perfectly answer your question. — , Aug 24 '18 at 11:00
There are also multiple duplicates here on SO: [here](https://stackoverflow.com/questions/33414552) and [here](https://stackoverflow.com/questions/7391090) and [here](https://stackoverflow.com/questions/42941006) — , Aug 24 '18 at 11:03
I just noticed the answer was staring me in my face, I wasn't using the primary/foreign keys to my advantage. I've posted my own answer to the question. Thanks for the feedback. — MrRobot, Aug 24 '18 at 12:09

score 0 · Answer 1 · answered Aug 24 '18 at 12:08

Ok so I found an answer to my own question.

WITH ins (date,time_spent, id) AS
( VALUES
    ( '22/08/2012', 170, (SELECT id FROM mock3 WHERE program ='bar'))
)  
INSERT INTO mock4
   (date, time_spent, user_id) 
SELECT 
    ins.date, ins.time_spent, mock3.id
FROM 
  mock3 JOIN ins
    ON ins.id = mock3.id ;

I was trying to take the 2 values from the first table, match these and then insert 2 new values to the next table, but I realised that I should be using the Primary and Foreign keys to my advantage.

I instead now JOIN on the ID and then just select the key I need by searching it from the values with (SELECT id FROM mock3 WHERE program ='bar') in the third row.

Insert multiple values with foreign key Postgresql

1 Answers1