Thread: How do I select composite array element that satisfy specific conditions.
x float8,
y float8
);
create table B(
Ay A[]
);
insert into B
values(array[
(1,2)::A,
(3,4)::A]
);
RE: How do I select composite array element that satisfy specific conditions.
Hi > -----Original Message----- > From: a [mailto:372660931@qq.com] > Sent: Mittwoch, 23. Mai 2018 11:43 > To: pgsql-general <pgsql-general@postgresql.org> > Subject: How do I select composite array element that satisfy specific conditions. > > Hi, say if I have composite type and table > > create type A as( > x float8, > y float8 > ); > > create table B( > Ay A[] > ); > > insert into B > values(array[ > (1,2)::A, > (3,4)::A] > ); > > How could I select the element of Ay that satisfy x=3?? > > Thank you so much!! > > Shore I did not really follow this thread, so I am not in clear, why you want to complicate your life that much. You create a custom data type and then use it in an array in a column. A complex hierarchical structure. Why don't you simply use JSON or JSONB? Your example sounds terribly academic very much like a school assignment. Bye Charles
Hi
> -----Original Message-----
> From: a [mailto:372660931@qq.com]
> Sent: Mittwoch, 23. Mai 2018 11:43
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: How do I select composite array element that satisfy specific conditions.
>
> Hi, say if I have composite type and table
>
> create type A as(
> x float8,
> y float8
> );
>
> create table B(
> Ay A[]
> );
>
> insert into B
> values(array[
> (1,2)::A,
> (3,4)::A]
> );
>
> How could I select the element of Ay that satisfy x=3??
>
> Thank you so much!!
>
> Shore
I did not really follow this thread, so I am not in clear, why you want to complicate your life that much.
You create a custom data type and then use it in an array in a column. A complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic very much like a school assignment.
Bye
Charles
RE: RE: How do I select composite array element that satisfy specific conditions.
Hello
From: a [mailto:372660931@qq.com]
Sent: Mittwoch, 23. Mai 2018 14:23
To: Charles Clavadetscher <clavadetscher@swisspug.org>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: RE: How do I select composite array element that satisfy specific conditions.
Thanks for your reply.......
Honestly I do not use java and don't really know json. All I understand is that it is a text format that allow some customization.
Java and JSON are not really related. For a simple description of JSON: http://www.json.org
However, as long as it can solve my problem, I'm happy to learn it.
now I do have a complex structure of data to store. what I'm aiming at is to:
1, orgnize the data so that it has hierarchy and structrues for people to operate.
2, all updates, insertion, will be recorded (including who, when, for what reason and which element changed from what to what).
currently I wrote a C trigger to dynamically disassemble the complex structure and compare them one by one and generate a string that printing out every change along with the update user info.
since my amount of data are not that big and the trigger is written in C, the final efficient is considerablly accepted. Now my question would be if json would be helpful on creating a relative efficient mechanism on that......
I assume that it is possible and much easier, but this would require more knowledge on the data that you want to pack in the json structure. Besides that, a basic question would be if it even necessary at all to have such a complex structure. In many cases a simpler design is more efficient.
The best thing would be to have a look at how JSON works and decide for yourself, if it helps in your case. Creating new types and aggregating them in array sounds like an overkill, but I may be mistaken.
For example your original example in JSONB could look like this:
[
{
"x": 1,
"y": 2
},
{
"x": 3,
"y": 4
}
]
In the database:
CREATE t (a JSONB);
INSERT INTO t VALUES ('[{"x": 1,"y": 2},{"x": 3,"y": 4}]');
SELECT * FROM (SELECT jsonb_array_elements(a) e FROM t) x WHERE x.e->>'x' = '3';
e
------------------
{"x": 3, "y": 4}
(1 row)
For completeness. The answer to your original question is:
SELECT * FROM (SELECT unnest(ay) AS ay FROM b) u WHERE (u.ay).x = 3;
ay
-------
(3,4)
(1 row)
Regards
Charles
---Original---
From: "Charles Clavadetscher"<clavadetscher@swisspug.org>
Date: Wed, May 23, 2018 19:29 PM
To: "'pgsql-general'"<pgsql-general@postgresql.org>;"'a'"<372660931@qq.com>;
Subject: RE: How do I select composite array element that satisfy specific conditions.
Hi
> -----Original Message-----
> From: a [mailto:372660931@qq.com]
> Sent: Mittwoch, 23. Mai 2018 11:43
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: How do I select composite array element that satisfy specific conditions.
>
> Hi, say if I have composite type and table
>
> create type A as(
> x float8,
> y float8
> );
>
> create table B(
> Ay A[]
> );
>
> insert into B
> values(array[
> (1,2)::A,
> (3,4)::A]
> );
>
> How could I select the element of Ay that satisfy x=3??
>
> Thank you so much!!
>
> Shore
I did not really follow this thread, so I am not in clear, why you want to complicate your life that much.
You create a custom data type and then use it in an array in a column. A complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic very much like a school assignment.
Bye
Charles
Re: How do I select composite array element that satisfy specific conditions.
How could I select the element of Ay that satisfy x=3??
Hello
From: a [mailto:372660931@qq.com]
Sent: Mittwoch, 23. Mai 2018 14:23
To: Charles Clavadetscher <clavadetscher@swisspug.org>; pgsql-general <pgsql-general@postgresql.org>
Subject: Re: RE: How do I select composite array element that satisfy specific conditions.
Thanks for your reply.......
Honestly I do not use java and don't really know json. All I understand is that it is a text format that allow some customization.
Java and JSON are not really related. For a simple description of JSON: http://www.json.org
However, as long as it can solve my problem, I'm happy to learn it.
now I do have a complex structure of data to store. what I'm aiming at is to:
1, orgnize the data so that it has hierarchy and structrues for people to operate.
2, all updates, insertion, will be recorded (including who, when, for what reason and which element changed from what to what).
currently I wrote a C trigger to dynamically disassemble the complex structure and compare them one by one and generate a string that printing out every change along with the update user info.
since my amount of data are not that big and the trigger is written in C, the final efficient is considerablly accepted. Now my question would be if json would be helpful on creating a relative efficient mechanism on that......
I assume that it is possible and much easier, but this would require more knowledge on the data that you want to pack in the json structure. Besides that, a basic question would be if it even necessary at all to have such a complex structure. In many cases a simpler design is more efficient.
The best thing would be to have a look at how JSON works and decide for yourself, if it helps in your case. Creating new types and aggregating them in array sounds like an overkill, but I may be mistaken.
For example your original example in JSONB could look like this:
[
{
"x": 1,
"y": 2
},
{
"x": 3,
"y": 4
}
]
In the database:
CREATE t (a JSONB);
INSERT INTO t VALUES ('[{"x": 1,"y": 2},{"x": 3,"y": 4}]');
SELECT * FROM (SELECT jsonb_array_elements(a) e FROM t) x WHERE x.e->>'x' = '3';
e
------------------
{"x": 3, "y": 4}
(1 row)
For completeness. The answer to your original question is:
SELECT * FROM (SELECT unnest(ay) AS ay FROM b) u WHERE (u.ay).x = 3;
ay
-------
(3,4)
(1 row)
Regards
Charles
---Original---
From: "Charles Clavadetscher"<clavadetscher@swisspug.org>
Date: Wed, May 23, 2018 19:29 PM
To: "'pgsql-general'"<pgsql-general@postgresql.org>;"'a'"<372660931@qq.com>;
Subject: RE: How do I select composite array element that satisfy specific conditions.
Hi
> -----Original Message-----
> From: a [mailto:372660931@qq.com]
> Sent: Mittwoch, 23. Mai 2018 11:43
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: How do I select composite array element that satisfy specific conditions.
>
> Hi, say if I have composite type and table
>
> create type A as(
> x float8,
> y float8
> );
>
> create table B(
> Ay A[]
> );
>
> insert into B
> values(array[
> (1,2)::A,
> (3,4)::A]
> );
>
> How could I select the element of Ay that satisfy x=3??
>
> Thank you so much!!
>
> Shore
I did not really follow this thread, so I am not in clear, why you want to complicate your life that much.
You create a custom data type and then use it in an array in a column. A complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic very much like a school assignment.
Bye
Charles
Re: RE: RE: How do I select composite array element that satisfyspecific conditions.
That is only by saying, the actual information could be much more, and all of them are not in some way, "aligned".
The results would be millions(which means many) of tables lies in database and each query is hundreds of lines. It is hard to create new query that target your info and it is dangerous to modify any set query.
Now my think was to group data into structures so that I can significantly decrease the amount of table, and since it can hold array, I can actually put historical data into one table for one year, which stops query from multiple historical tables and shrink the size of database.
However, I am new to this and do not have experience, so if you could provide any suggestion, it would be extremely grateful from me.
That is only by saying, the actual information could be much more, and all of them are not in some way, "aligned".
The results would be millions(which means many) of tables lies in database and each query is hundreds of lines. It is hard to create new query that target your info and it is dangerous to modify any set query.
Now my think was to group data into structures so that I can significantly decrease the amount of table, and since it can hold array, I can actually put historical data into one table for one year, which stops query from multiple historical tables and shrink the size of database.
However, I am new to this and do not have experience, so if you could provide any suggestion, it would be extremely grateful from me.