postgres insert + select + values - Pgsql 9.5

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt; 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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please have a look on this example Patrick: http://sqlfiddle.com/#!15/
1773d/4

Lucas

This helped a lot... it's working now :) Thanks guys!!!

Patrick