Insert into table from another using custom data

Question

As I read here If I want to insert into a table from another in PostgreSQL database I have to use this:

This query inserts some rows from another table

INSERT INTO books (id, title, author_id, subject_id)
       SELECT nextval('book_ids'), title, author_id, subject_id
              FROM book_queue WHERE approved;

But how can I insert a row with more columns with my custom data(default or non default)?

E.g., books (id, title, author_id, <a cell with my data1>,subject_id,<a cell with my data2>)

And how can I update a selected data before inserting into a table?

I.e., something like:

INSERT INTO books (id, title, author_id, subject_id)
       SELECT nextval('book_ids'), title, author_id+1, subject_id/2
              FROM book_queue WHERE approved;

What exactly is <a cell with my dataX>? Is that a row from some other table? Some other well-defined data? The way you phrase your question you will not get a proper answer; please provide more details. — Patrick
– Patrick, Commented Sep 13, 2015 at 7:52

wwkudu · Accepted Answer · 2015-09-13 07:55:11Z

1

Insert inserts rows, and select generates/forms rows, so if you can build the data you want to insert with any select statement, then you can insert it, even if there are new fields or updated fields. For example:

INSERT INTO books (title, author_id, subject_id, combined)
  SELECT title, author_id+1, subject_id/2, author_id || ' ' || title
    FROM book_queue WHERE approved;

Here is a fiddle to play with.

answered Sep 13, 2015 at 7:55

wwkudu

2,7963 gold badges31 silver badges42 bronze badges

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

1 Comment

Vyacheslav Over a year ago

Thanks. That's what I want. This is very difficult to find good examples. Congratulate with 1k scores:)

Collectives™ on Stack Overflow

Insert into table from another using custom data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related