Insert values with select Postgres

Question

I need to make an insert, but before I need to compare the values of the insert with other tables, so that I can also insert the values of other tables, in the current table that I want to insert:

I don't know how to write the insert syntax

Insert into TABLE_3 (code, value, description)
values 123, 20000, teste

GMB · Accepted Answer · 2020-03-08 20:46:53Z

2

Consider the insert ... select ... syntax. The values() list comes handy for this.

insert into table_3(code, value, desription, subscription, value_percent, total_value)
select
    v.code,
    v.value,
    v.description,
    t1.subscription,
    t2.value_percent,
    v.value * t2.value_percent / 100.0 total_value
from (values (123, 20000, 'teste')) v(code, value, description)
inner join table_1 t1 on t1.code = v.code
inner join table_2 t2 on t2.subscription = t1.subscription

answered Mar 8, 2020 at 20:46

GMB

224k25 gold badges103 silver badges151 bronze badges

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

5 Comments

rbrt Over a year ago

I still need to do one last test to run the last inner join only if there is t1.code, if there is not, then you don't need to run it. t1.code IS NOT NULL

rbrt Over a year ago

can help me please?

GMB Over a year ago

@rbrt: that's what the query does. If there is no match on t1.code, the row is not inserted.

rbrt Over a year ago

I needed to return null on the missing columns. The columns that can be null are t1.subscription. This is posible?

GMB Over a year ago

@rbrt: ok, then you want left joins instead of inner joins. First change the last inner join to a left join, see if it does what you want. If that's not enough then change both.

Collectives™ on Stack Overflow

Insert values with select Postgres

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related