Thank you so much, I may discuss more on what I am doing so that it might have a clearer version for me and if you would be so kind to provide your opinions on this.
The target industry is insurance industry and the table is used to hold policy data for insurance company. The key reason for doing complex structure is sourced from the nature of policy information. 1, For a policy, we would have basic info about insurant, beneficiary, product name and so on. 2, But that's surely not enough, we also need the history of policy status, when it started, when it deactivated when it break into payment and so on. 3, Above that, we also need to have financial information about that, like receivable account and cash account. Adjustments and money in/out due to the change of policy statues (such as payment); 4, Beside that, I'm in actuary team, so we need to value the actual contribution each policy is doing for the company. Therefore, more information about cash flow projection, survival rate (regulator will require you to perform multiple scenarios so they would have multiple entries that conduct the result). 5, furthermore, for profit analysis reason, the expected cash flow and the actual cash flow would needed. 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. I will look up json and try on the efficiency when I have time. And again, thanks for answering my questions. ------------------ Original ------------------ From: "Charles Clavadetscher";<clavadetsc...@swisspug.org>; Date: May 23, 2018 To: "a"<372660...@qq.com>; "'pgsql-general'"<pgsql-gene...@postgresql.org>; Subject: RE: RE: How do I select composite array element that satisfy specific conditions. Hello From: a [mailto:372660...@qq.com] Sent: Mittwoch, 23. Mai 2018 14:23 To: Charles Clavadetscher <clavadetsc...@swisspug.org>; pgsql-general <pgsql-gene...@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"<clavadetsc...@swisspug.org> Date: Wed, May 23, 2018 19:29 PM To: "'pgsql-general'"<pgsql-gene...@postgresql.org>;"'a'"<372660...@qq.com>; Subject: RE: How do I select composite array element that satisfy specific conditions. Hi > -----Original Message----- > From: a [mailto:372660...@qq.com] > Sent: Mittwoch, 23. Mai 2018 11:43 > To: pgsql-general <pgsql-gene...@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
