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
– MrRobot, Commented Aug 24, 2018 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. — user330315
– user330315, Commented Aug 24, 2018 at 11:00
There are also multiple duplicates here on SO: here and here and here — user330315
– user330315, Commented Aug 24, 2018 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
– MrRobot, Commented Aug 24, 2018 at 12:09

MrRobot · Accepted Answer · 2018-08-24 12:08:50Z

0

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.

answered Aug 24, 2018 at 12:08

MrRobot

415 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert multiple values with foreign key Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related