0

I have a question about Union all

I have a simple SQL to insert into table

INSERT INTO tbl_a (id,userid,itemcd,amount)
SELECT
  (nextval('seq_tbl_id')::text, 20, '0')
  ,'010'
  ,(left(serialid,3))
  ,amount
 FROM
 (SELECT 'AAA00000006999' AS serialid, 5 AS amount
 UNION ALL SELECT 'AAA00000007000', 1
 UNION ALL SELECT 'AAA00000007001', 1
 ...6k rows
 UNION ALL SELECT 'ZZZ00000009999', 1
 )t

It's only 6k rows so I didn't think there gonna any problem but my server went out of memory after executing it and some other services also went down.(My server is not that bad, without Union All, inserting 10k, 50k rows is fine all the time)

Is this some kind of wrong way to use Union All or some server side problem? When a process when out of memory like this, is there any way, any setting make only that process goes down without affecting other services?

2
  • 1
    (nextval('seq_tbl_id')::text, 20, '0') doesn't look valid to begin with.
    – user1822
    Aug 12, 2021 at 11:08
  • My bad, it's actually LPAD(nextval('seq_tbl_id')::text, 20, '0')
    – duynguyen
    Aug 13, 2021 at 2:46

1 Answer 1

1

A values clause might be more efficient than a UNION all:

INSERT INTO tbl_a (id,userid,itemcd,amount)
SELECT (nextval('seq_tbl_id')::text, 20, '0')
       ,'010'
       ,(left(serialid,3))
       ,amount
FROM (
 values 
   ('AAA00000006999', 5), 
   ('AAA00000007000', 1),
   ('AAA00000007001', 1),
 ...6k rows
   ('ZZZ00000009999', 1)
) as (serialid, amount)

Or maybe using generate_series(1,6000) instead of hardcoding 6000 values.

1
  • Thanks for your answer, your SQL is perfectly fine but what I wanted to ask is why UNION all causing all these problem but "values" or just "insert" is fine.
    – duynguyen
    Aug 13, 2021 at 2:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.