Question 195643 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql how to use set of inserted ids for inserting to another table?

What I want: if record with the same unique(doctor_id, year, month) exists, we need to increase sum via input sum value.

I’m trying this SQL query:

with rows as (
    insert into mis_doctor_stats (doctor_id, year, month, sum)
        values (1, 2025, 3, 100),
               (1, 2021, 4, 6)
        on conflict (doctor_id, year, month) do update set sum = mis_doctor_stats.sum + excluded.sum
        returning id)
insert
into mis_doctor_stats_files(file_id, stats_id)
select 4, rows.id
from rows;

But still got the error: FROM expected, got ‘;’

screenshot

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As @Bruno strictly said the problem was in the alias_name for which I tried to use reserved keyword rows.

    The right solution in my case:

    with cte as (
    insert into mis_doctor_stats (doctor_id, year, month, sum)
        values (1, 2020, 3, 100),
               (1, 2023, 4, 6)
        on conflict (doctor_id, year, month) do update set sum = mis_doctor_stats.sum + excluded.sum
        returning id)
insert
into mis_doctor_stats_files(file_id, stats_id)
select 4, cte.id
from cte
on conflict do nothing;

  2. 0 
    with rows as (
    insert into mis_doctor_stats (doctor_id, year, month, sum)
        values (1, 2025, 3, 100),
               (1, 2021, 4, 6)
        on conflict (doctor_id, year, month) do update set sum = mis_doctor_stats.sum + excluded.sum
        returning id)
insert
into mis_doctor_stats_files(file_id, stats_id)
select 4, rows.id
from rows

    can you remove semicolon at the end?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search