PostgreSQL randomize foreign key

Question

I have 2 tables in Postgres ( inventory table and event table ). In the inventory table my primary key consist of 8 strings. Example '03163161'.

Now I have to input them in the Event table randomly with the primary key that is on the inventory table.

In what way can I do it? I've tried googling it but no one seems to have a solution on it and I'm trying to experiment on how i can random input the PK of inventory table to the event table since the data is almost 3k.

Update#1

I've tried using the code below

INSERT INTO dashboard.event(terminal_id) VALUES (SELECT terminal_id FROM dashboard.inventory)

but i get the error:

ERROR:  syntax error at or near "SELECT"
LINE 1: INSERT INTO dashboard.event(terminal_id) VALUES (SELECT term...
                                                         ^
SQL state: 42601
Character: 50

Craeft · Accepted Answer · 2020-03-31 07:54:51Z

2

Don't use the keyword VALUES with a select in the insert. See this question:

Update or Insert (multiple rows and columns) from subquery in PostgreSQL

INSERT INTO dashboard.event(terminal_id) 
SELECT terminal_id 
FROM dashboard.inventory --should work

edited Mar 31, 2020 at 7:54

user330315

answered Mar 31, 2020 at 4:25

Craeft

2271 silver badge11 bronze badges

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

Comments

Craeft · Accepted Answer · 2020-03-31 07:55:23Z

0

I think you want the 'references' keyword. For example:

https://www.postgresql.org/docs/current/ddl-constraints.html

create table inventory
(
 id INTEGER NOT NULL UNIQUE,
 ... other fields
);

create table events
(
 eid INTEGER REFERENCES inventory(id),
 ... other fields
);

edited Mar 31, 2020 at 7:55

user330315

answered Mar 31, 2020 at 2:00

Craeft

2271 silver badge11 bronze badges

3 Comments

Elijah Leis Over a year ago

Hi! Thanks for answering. But i'm not trying to create a table. I'm trying to insert random foreign keys on the event table.

Craeft Over a year ago

I was hoping to see the SQL definition or \d the tables. It's not completely clear to me what you are trying to do. Do you want to select all the PKs from inventory, and then randomly put them in events?

Elijah Leis Over a year ago

Yes that's what i'm planning to do or just input them all in the event table

Collectives™ on Stack Overflow

PostgreSQL randomize foreign key

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related