Thread: Elegant way to insert of some value in some tables in shortinstruction

Hi all,

i'm very novice in SQL and try to find an elegant way to insert some 
values to some tables (that i'll later query with jointure for have a 
view of only desired informations) but in short way.

All manuals i saw until now told me like:

insert into table1 values('value1','value2','value3');

insert into table2 values('value1','value2','value3');


Well, it's nice but doesn't it exists a way to make same in 1 operation?

Thanks for your answer

Short version is when using insert with values you can put a bunch of new records into the SAME table like this...

insert into table1 values (1, 'One'), (2, 'Two'), (3, 'Three');

...but inserts into different tables need to be done in different statements.



-----Original Message-----
From: philolilou [mailto:philolilou@free.fr] 
Sent: Wednesday, March 13, 2019 3:04 PM
To: pgsql-novice@postgresql.org
Subject: Elegant way to insert of some value in some tables in short instruction

Hi all,

i'm very novice in SQL and try to find an elegant way to insert some 
values to some tables (that i'll later query with jointure for have a 
view of only desired informations) but in short way.

All manuals i saw until now told me like:

insert into table1 values('value1','value2','value3');

insert into table2 values('value1','value2','value3');


Well, it's nice but doesn't it exists a way to make same in 1 operation?

Thanks for your answer

Am 13.03.19 um 20:25 schrieb David Raymond:
>
> ...but inserts into different tables need to be done in different statements.

you can insert into 2 tables within 1 statement using writeable common 
table expressions (wCTE):


test=# create table table1(a int, b int, c int);
CREATE TABLE
test=*# create table table2(a int, b int, c int);
CREATE TABLE
test=*# with x as (insert into table1 values (1,2,3) returning *) insert 
into table2 select * from x;
INSERT 0 1
test=*# select * from table1;
  a | b | c
---+---+---
  1 | 2 | 3
(1 row)

test=*# select * from table2;
  a | b | c
---+---+---
  1 | 2 | 3
(1 row)

test=*# with x as (insert into table1 values (4,5,6) returning *) insert 
into table2 select * from x;
INSERT 0 1
test=*# select * from table1;
  a | b | c
---+---+---
  1 | 2 | 3
  4 | 5 | 6
(2 rows)

test=*# select * from table2;
  a | b | c
---+---+---
  1 | 2 | 3
  4 | 5 | 6
(2 rows)

test=*#



Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com