Thread: query on smallint array column

query on smallint array column

From
Zhihong Yu
Date:
Hi,
I was experimenting with the following query.

create table sint1(k int primary key, arr smallint[]);
create index s1 on sint1(arr);
insert into sint1 select s, array[s*s, s] FROM generate_series(1, 10) AS s;
select * from sint1 where arr @> array[4];
ERROR:  operator does not exist: smallint[] @> integer[]
LINE 1: select * from sint1 where arr @> array[4];
                                      ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
-------

I wonder if someone can enlighten me on the correct way to perform the type cast.

Thanks

Re: query on smallint array column

From
Pavel Stehule
Date:
Hi

ne 13. 12. 2020 v 18:42 odesílatel Zhihong Yu <zyu@yugabyte.com> napsal:
Hi,
I was experimenting with the following query.

create table sint1(k int primary key, arr smallint[]);
create index s1 on sint1(arr);
insert into sint1 select s, array[s*s, s] FROM generate_series(1, 10) AS s;
select * from sint1 where arr @> array[4];
ERROR:  operator does not exist: smallint[] @> integer[]
LINE 1: select * from sint1 where arr @> array[4];
                                      ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
-------

I wonder if someone can enlighten me on the correct way to perform the type cast.

 
postgres=# select * from sint1 where arr @> array[4::smallint];
┌───┬────────┐
│ k │  arr   │
╞═══╪════════╡
│ 2 │ {4,2}  │
│ 4 │ {16,4} │
└───┴────────┘
(2 rows)

postgres=# select * from sint1 where arr @> array[4]::smallint[];
┌───┬────────┐
│ k │  arr   │
╞═══╪════════╡
│ 2 │ {4,2}  │
│ 4 │ {16,4} │
└───┴────────┘
(2 rows)


postgres=#
 

Thanks

Re: query on smallint array column

From
Zhihong Yu
Date:
Thanks Pavel for fast response.

On Sun, Dec 13, 2020 at 9:51 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

ne 13. 12. 2020 v 18:42 odesílatel Zhihong Yu <zyu@yugabyte.com> napsal:
Hi,
I was experimenting with the following query.

create table sint1(k int primary key, arr smallint[]);
create index s1 on sint1(arr);
insert into sint1 select s, array[s*s, s] FROM generate_series(1, 10) AS s;
select * from sint1 where arr @> array[4];
ERROR:  operator does not exist: smallint[] @> integer[]
LINE 1: select * from sint1 where arr @> array[4];
                                      ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
-------

I wonder if someone can enlighten me on the correct way to perform the type cast.

 
postgres=# select * from sint1 where arr @> array[4::smallint];
┌───┬────────┐
│ k │  arr   │
╞═══╪════════╡
│ 2 │ {4,2}  │
│ 4 │ {16,4} │
└───┴────────┘
(2 rows)

postgres=# select * from sint1 where arr @> array[4]::smallint[];
┌───┬────────┐
│ k │  arr   │
╞═══╪════════╡
│ 2 │ {4,2}  │
│ 4 │ {16,4} │
└───┴────────┘
(2 rows)


postgres=#
 

Thanks