How do you insert rows into Postgresql 8.4 when one column is a variable value?

Question

I want to insert records into the "books" table for every author in the "author" table. In other words, if this represented the author table:

> 1 - "Herman Melville" 
> 2 - "Stephen King" 
> 3 - "Truman Capote"

...then, the insert would yield these results in the "book" table:

> 'Hello World' - 1
> 'Hello World' - 2
> 'Hello World' - 3

I have the following INSERT statement (it's actually a crude example, but it demonstrates the objective):

INSERT INTO books (title, author_id) VALUES ('Hello World', SELECT id FROM author);

Unfortunately, this fails and I'm uncertain how to get each of the "id" values from the "author" table.

DNS · Accepted Answer · 2011-06-14 16:57:33Z

9

It's not clear why you'd want to insert the same entry for each author, but you could use a SELECT as the source for your INSERT, like this:

INSERT INTO books (title, author_id)
    SELECT 'Hello World', id
    FROM author

Edit: for some more info, check the docs for INSERT; as you can see, the source can be either DEFAULT VALUES, VALUES or a query.

answered Jun 14, 2011 at 16:57

DNS

38.4k20 gold badges97 silver badges134 bronze badges

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

Comments

Frank Heikens · Accepted Answer · 2011-06-14 16:58:11Z

3

You can use this one:

INSERT INTO books (title, author_id) SELECT 'Hello World', id FROM author;

answered Jun 14, 2011 at 16:58

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Comments

leonbloy · Accepted Answer · 2011-06-14 17:22:04Z

2

You can also surround your subselect with parentheses:

 INSERT INTO books (title, author_id) 
  VALUES ('Hello World',  (SELECT id FROM author WHERE...) );

This syntax might be easiest to understand, but the other syntax pointed by other commenters (without VALUES) is probably more clean : you just build out the fly the record you want to insert (with a SELECT) and then you INSERT it; it could also be more efficient if you had many subselects for distict fields.

edited Jun 14, 2011 at 17:22

answered Jun 14, 2011 at 17:16

leonbloy

76.4k22 gold badges149 silver badges197 bronze badges

Comments

phihag · Accepted Answer · 2011-06-14 18:12:07Z

1

INSERT INTO books (title, author_id) SELECT 'Hello World', id FROM author

edited Jun 14, 2011 at 18:12

phihag

289k75 gold badges475 silver badges489 bronze badges

answered Jun 14, 2011 at 16:58

Optillect Team

1,7672 gold badges11 silver badges12 bronze badges

4 Comments

Huuuze Over a year ago

Exact dupe of the one above it. Please consider removing the answer.

Lasse V. Karlsen Over a year ago

@Huuuze To be fair, the "one above" was posted afterwards, some 18 minutes afterwards. Let the community decide which one they like.

Huuuze Over a year ago

@Lasse Um, nope. After the down vote, it's two up -- they're posted at the exact same time. Look more closely next time.

Lasse V. Karlsen Over a year ago

Yeah, I missed which one you were talking about, the ordering here is random when things have the same amount of votes, so "above" and "below" is easy to get wrong. I see what you mean now. Sorry.

Collectives™ on Stack Overflow

How do you insert rows into Postgresql 8.4 when one column is a variable value?

4 Answers 4

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related