Search code examples
sqlpostgresqlsql-insert

PostgreSQL - How to INSERT with shared values in a specific column

I am creating an Exam Management System database.

I have a table called Questions:

q_id (pk) q_text
1 What is the name of our galaxy?
2 What planet do we live on?

And a second table called Choices:

c_id (pk) q_id (fk) c_text
1 1 Milky Way
2 1 Galactus
3 1 Omicron Persei 8
4 2 Mars
5 2 Earth
6 2 Saturn

When I am doing an INSERT into the choices table, how can I reference the same q_id for multiple insert statements without having to write it out each time?

Similar to this:

INSERT INTO choices (q_id = 1, c_text)
VALUES ('Milky Way'),
       ('Galactus'),
       ('Omicron Persei 8');

I get this error when I try to set the column with a shared value for multiple INSERTs:

ERROR: syntax error at or near "="
LINE 1: INSERT INTO choices (q_id = 1, c_text)

Solution

  • You can use an insert-select statement where you unnest an array of the values you have and have the "fixed" value as another column:

    INSERT INTO choices
SELECT 1, UNNEST(ARRAY['Milky Way', 'Galactus', 'Omicron Persei 8'])