Что за магия с PostgreSQL?

Question

[lightseeker]

есть такой функционал

create type t_best_asin_ranking_2 as
(
    asin    varchar,
    price   numeric,
    reviews integer,
    rating  numeric
);


DO $$ DECLARE test t_best_asin_ranking_2[];
BEGIN
    test = (SELECT array_agg(r) FROM (SELECT DISTINCT ON (asin) asin, price, rating, reviews FROM (
                        SELECT asin, price, rating, reviews
                        FROM UNNEST((SELECT array_agg(r) FROM jsonb_populate_recordset(NULL::t_best_asin_ranking_2, '[{
"asin":"B06XH9FNH1",
"price": 1.5,
"rating": 1.5,
"reviews": 1
}]') r))
) as rankings
ORDER BY asin) r);
END$$;

при выполнении он выдает ошибку

invalid input syntax for type integer: "1.5"

причем ошибка связана с полем rating

помогите пожалуйста разобраться. спасибо заранее

Comments

[Александр]

Эта ошибка связана с тем, что значение "1.5" передается в поле "rating", которое ожидает целое число, а не десятичное.
Нужно либо исправить тип данных в таблице, указав вместо "integer" тип "numeric", либо исправить данные в массиве, указав целое число вместо "1.5".

[lightseeker]

Александр, сверху указал ddl типа переменного. rating определен как numeric, не integer

Answer 1

[Melkij]

приведение массива record к массиву композитного типа позиционное, а не символьное. Поменяйте в

SELECT DISTINCT ON (asin) asin, price, reviews, raing

порядок полей на совпадающий с таковым в таблице.

убрали бы лишние подзапросы, что ли.

(SELECT array_agg(j) FROM (SELECT DISTINCT ON (j.asin) j FROM jsonb_populate_recordset(NULL::t_best_asin_ranking_2, '[{
"asin":"B06XH9FNH1",                                                                                                  
"price": 1.5,                                                                                                   
"rating": 1.5,
"reviews": 1
}]') j ORDER BY j.asin) r);

вот так хотя бы.

Comments

[lightseeker]

немного не понял
если приведение позиционное, то почему так работает?

SELECT asin, rating, price, reviews
                FROM (SELECT * FROM UNNEST((SELECT array_agg(r) FROM jsonb_populate_recordset(NULL::t_best_asin_ranking_2, '[{
"price": 1,
"asin":"B06XH9FNH1",
"reviews": 1,
"rating": 1
}]') r))

с точки зрения позиции ASIN совпадает с полем rating

[Melkij]

с точки зрения позиции ASIN совпадает с полем rating

где?
SELECT asin, rating, price, reviews
ну а то что 1 можно привести к целочисленному значению - логично.

[lightseeker]

Melkij, я изменил порядок в json файле. или же позиции должны совпать с типом ?

[Melkij]

Я специально процитировал часть запроса, формулирующую структуру record, которую и будем пытаться привести к композитному типу.
jsonb_populate_recordset по именам заполняет по образцу t_best_asin_ranking_2, потом вы переформатируете в произвольный record, упаковываете в массив и пытаетесь этот массив record привести к массиву t_best_asin_ranking_2.