postgresql Postgres从用户定义类型内的数组中选择

icomxhvb 于 2023-04-11 发布在 PostgreSQL

关注(0)|答案(1)|浏览(84)

I have made a type and a table:

CREATE TYPE public.sample AS
(
    ts timestamp with time zone,
    temperature real[]
);

and a table using this type:

CREATE TABLE IF NOT EXISTS public.tc_sampling
(
    equip_id integer,
    tc_sample sample
)   
TABLESPACE pg_default;

and I can put data in like this:

INSERT INTO 
   tc_sampling (equip_id, tc_sample) 
VALUES 
   (10,('2022-12-30 11:45:56',
ARRAY[109.4,140.98,144.17,62.25,93.12,61.47,65.1,59.42,79.61]::real[]))

but I don't know how to select, for instance, just the timestamp or one of the items in the temperature real [] array.
when I do a full, select, I do get data:
SELECT * FROM tc_sampling limit 3;
10   "(""2022-12-30 11:45:46-05"",""{109.3,140.88,144.17,62.25,93.12,61.47,65.1,59.42,79.61}"")"
10   "(""2022-12-30 11:45:56-05"",""{110.47,141.89,146.2,62.34,94.01,63.17,65.5,60.03,85.69}"")"
10   "(""2022-12-30 11:46:06-05"",""{113.24,144.13,149.42,62.5,95.5,66.6,65.99,61.79,98.3}"")"
I have tried, for instance,

SELECT equip_id,tc_sample[1]FROM tc_sampling  limit 10;

and got

ERROR:  cannot subscript type sample because it does not support subscripting
LINE 1: SELECT equip_id,tc_sample[1]FROM tc_sampling  limit 10;
                         ^
SQL state: 42804
Character: 17

and

SELECT (ARRAY[tc_sample])[1:1]
FROM tc_sampling
WHERE equip_id=10
LIMIT 3

and got:
"{""(""2022-12-30 11:45:46-05"",""{109.3,140.88,144.17,62.25,93.12,61.47,65.1,59.42,79.61}"")""}" "{""(""2022-12-30 11:45:56-05"",""{110.47,141.89,146.2,62.34,94.01,63.17,65.5,60.03,85.69}"")""}" "{""(""2022-12-30 11:46:06-05"",""{113.24,144.13,149.42,62.5,95.5,66.6,65.99,61.79,98.3}"")""}"

postgresql

来源：https://stackoverflow.com/questions/75901030/postgres-selection-from-array-inside-a-user-defined-type

1条答案

按热度按时间

uqxowvwt1#

CREATE TYPE public.sample AS
(
    ts timestamp with time zone,
    temperature real[]
);
CREATE TABLE IF NOT EXISTS public.tc_sampling
(
    equip_id integer,
    tc_sample sample
)   ;
INSERT INTO 
   tc_sampling (equip_id, tc_sample) 
VALUES 
   (10,('2022-12-30 11:45:56',
ARRAY[109.4,140.98,144.17,62.25,93.12,61.47,65.1,59.42,79.61]::real[]));

select (tc_sampling.tc_sample).ts from tc_sampling ;
           ts            
-------------------------
 12/30/2022 11:45:56 PST

select (tc_sampling.tc_sample).temperature from tc_sampling ;
                       temperature                        
----------------------------------------------------------
 {109.4,140.98,144.17,62.25,93.12,61.47,65.1,59.42,79.61}

根据文档字段选择
4.2.4.字段选择
...
(Thus，限定列引用实际上只是字段选择语法的一种特殊情况。）一种重要的特殊情况是从复合类型的表列中提取字段：
（compositecol）.somefield（mytable.compositecol）.somefield
这里需要括号来表明compositecol是列名而不是表名，或者在第二种情况下mytable是表名而不是模式名。

赞(0）回复(0）举报 2023-04-11

我来回答

postgresql Postgres从用户定义类型内的数组中选择

1条答案

相关问题

热门标签

最新问答