From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgres insert + select + values - Pgsql 9.5 |
Date: | 2016-09-15 22:00:05 |
Message-ID: | 083ca09c-ec67-c2a2-60dc-0b76e29f6ff5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick B | 2016-09-15 22:07:42 | Re: postgres insert + select + values - Pgsql 9.5 |
Previous Message | Scott Mead | 2016-09-15 21:58:04 | Re: Unable to create oracle_fdw (foreign data wrapper) extension |