Обсуждение: postgres insert + select + values - Pgsql 9.5
Hi guys,
I got the test1 table with three columns:
id(BIGINT) - j_id(BIGINT) - comments(CHARACTER VARYING)
This needs to be done 180 times:
INSERT INTO test1 (id,j_id,comments) VALUES (default,123321,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default,123322,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default,123323,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default,123324,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default,123325,'test-1 - comments');
However, I have to get j_id from another table:
select j_id from test2 WHERE customer_id = 88897
17012651
17012601
16623374
16808986
16843879
16808794
16870199
16870488
16870443
16870403
Example of what I need:
INSERT INTO test1 (id,j_id,comments) VALUES (default, 17012651,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default, 17012601,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default, 16623374,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default, 16808986,'test-1 - comments');
INSERT INTO test1 (id,j_id,comments) VALUES (default, 16843879,'test-1 - comments');
etc...
How can I do that? If the customer_id is the same for all those 180 rows?
Cheers
Patrick
On 16/09/16 07:45, Patrick B wrote: > Hi guys, > > > I got the test1 table with three columns: > > id(BIGINT) - j_id(BIGINT) - comments(CHARACTER VARYING) > > > *This needs to be done 180 times:* > > INSERT INTO test1 (id,j_id,comments) VALUES (default,123321,'test-1 > - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES (default,123322,'test-1 > - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES (default,123323,'test-1 > - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES (default,123324,'test-1 > - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES (default,123325,'test-1 > - comments'); > > > *However, I have to get j_id from another table:* > > select j_id from test2 WHERE customer_id = 88897 > > > 17012651 > 17012601 > 16623374 > 16808986 > 16843879 > 16808794 > 16870199 > 16870488 > 16870443 > 16870403 > > > > *Example of what I need:* > > INSERT INTO test1 (id,j_id,comments) VALUES > (default, 17012651,'test-1 - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES > (default, 17012601,'test-1 - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES > (default, 16623374,'test-1 - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES > (default, 16808986,'test-1 - comments'); > INSERT INTO test1 (id,j_id,comments) VALUES > (default, 16843879,'test-1 - comments'); > > > etc... > > How can I do that? If the customer_id is the same for all those 180 rows? A select can make up columns too, not just what you've got in a table, so you can: select j_id, 'test-1 - comments' as comment from test2 where customer_id=88897; and then you can simply insert that into your other table (you don't need to specify the columns that are getting a default value): insert into test1 (j_id, comments) select j_id, 'test-1 - comments' as comment from test2 where customer_id=88897; https://www.postgresql.org/docs/current/static/sql-insert.html has more info. -- Postgresql & php tutorials http://www.designmagick.com/
A select can make up columns too, not just what you've got in a table,
so you can:
select j_id, 'test-1 - comments' as comment from test2 where
customer_id=88897;
and then you can simply insert that into your other table (you don't
need to specify the columns that are getting a default value):
insert into test1 (j_id, comments)
select j_id, 'test-1 - comments' as comment from test2 where
customer_id=88897;
https://www.postgresql.org/docs/current/static/sql- insert.html has more
info.
Thanks Chris!
But the problem is that test2 table has 180 rows with different j_id and I need to insert each one of them into test1 table.
How can I do that?
select j_id FROM test2 - will return 180 rows
2016-09-16 10:07 GMT+12:00 Patrick B <patrickbakerbr@gmail.com>:
A select can make up columns too, not just what you've got in a table,
so you can:
select j_id, 'test-1 - comments' as comment from test2 where
customer_id=88897;
and then you can simply insert that into your other table (you don't
need to specify the columns that are getting a default value):
insert into test1 (j_id, comments)
select j_id, 'test-1 - comments' as comment from test2 where
customer_id=88897;
https://www.postgresql.org/docs/current/static/sql-insert. html has more
info.Thanks Chris!But the problem is that test2 table has 180 rows with different j_id and I need to insert each one of them into test1 table.How can I do that?select j_id FROM test2 - will return 180 rows
Please have a look on this example Patrick: http://sqlfiddle.com/#!15/1773d/4
Lucas
On 16/09/16 08:07, Patrick B wrote: > > > A select can make up columns too, not just what you've got in a table, > so you can: > > select j_id, 'test-1 - comments' as comment from test2 where > customer_id=88897; > > and then you can simply insert that into your other table (you don't > need to specify the columns that are getting a default value): > > insert into test1 (j_id, comments) > select j_id, 'test-1 - comments' as comment from test2 where > customer_id=88897; > > https://www.postgresql.org/docs/current/static/sql-insert.html > <https://www.postgresql.org/docs/current/static/sql-insert.html> has > more > info. > > > Thanks Chris! > > But the problem is that test2 table has 180 rows with different j_id and > I need to insert each one of them into test1 table. > > How can I do that? > select j_id FROM test2 - will return 180 rows > If your select returns 180 rows, then an `insert into select` query would insert 180 rows (assuming other constraints like primary / unique keys are met). -- Postgresql & php tutorials http://www.designmagick.com/
This helped a lot... it's working now :) Thanks guys!!!
Patrick