CODEWITHSUNDEEP
sqlpostgresql
dros
dros

Reputation: 1567

UNNEST not working when trying to unwrap values from array in POSTGRES

[![enter image description here][1]][1]I am trying to insert some data into a table as such:

filterArr:= ARRAY(SELECT row(f_id, sub_id, item_id,item_code ) FROM 
ToList(p_f_id, p_c_id)); 

INSERT INTO my_table (id, d_id, dc, IE_code)
SELECT 
        p_id AS id,
        fr.d_id AS d_id,
        fr.dc AS dc,
        'I' AS IE_code
From UNNEST(filterArr) fr
ON CONFLICT (id, d_id) DO NOTHING;

however when I call this procedure,

I have an error that complains of:

NOTICE:  items {"(2981,1,64704240,5055125912306-66233804)","(2981,1,66008001,5033849029197-71818244)","(2981,1,64857780,9780952006237-67074218)","(2981,1,64102887,5032510500997-66410192)","(2981,1,67008012,5060031875409-76016747)","(2981,1,63934027,5032510500959-66410324)","(2981,1,64988254,9780952006220-67074201)","(2981,1,64011641,5032510500942-66410301)","(2981,1,67007174,5060031875393-76016764)","(2981,1,66796500,5053947835261-74769014)"})
INFO:  filterArr{"(,,,)"}

ERROR:  null value in column "d_id" violates not-null constraint


my ToList Method:

    items := array(
        select row(p_f_id, p_f_rec.m_sub_id, d.st_item_id, d.st_item_code)
        from mytable)

So the items are indeed coming through in the array, they just dont seem to UNNEST in 'filterArr'

probably being quite blind here but a second pair of eyes will be much appreciate.

as you can see there are no NULL values :

NOTICE:  items {"(2981,1,64704240,5055125912306-66233804)","(2981,1,66008001,5033849029197-71818244)","(2981,1,64857780,9780952006237-67074218)","(2981,1,64102887,5032510500997-66410192)","(2981,1,67008012,5060031875409-76016747)","(2981,1,63934027,5032510500959-66410324)","(2981,1,64988254,9780952006220-67074201)","(2981,1,64011641,5032510500942-66410301)","(2981,1,67007174,5060031875393-76016764)","(2981,1,66796500,5053947835261-74769014)"})

how ever they do not unnest

INFO:  filterArr{"(,,,)"}

Upvotes: 0

Views: 1399

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247625

There is no need to convert to a array of records and back. Keep it simple:

INSERT INTO my_table (id, d_id, dc, IE_code)
SELECT f_id, sub_id, item_id, item_code
FROM ToList(p_f_id, p_c_id);

Upvotes: 0

Suffer Surfer
Suffer Surfer

Reputation: 15

As it is directly related to constraint of a particular field, there are two options:

  1. Try changing the constraint of column d_id to NULL to accept null values, if at all it solves the problem fully.

OR

  1. Fix the NULL values in the UNNEST(filterArr) table.

Upvotes: 0

Related Questions