Menu
The query below:
SELECT i_adgroup_id, i_category_id FROM adgroupcategories_br WHERE i_adgroup_id IN ( SELECT i_adgroup_id FROM adgroupusers_br WHERE i_user_id = 103713 ) GROUP BY i_adgroup_id, i_category_id; Gives me results like this:
i_adgroup_id integer | i_category_id smallint ---------------------|----------------------- 15938 | 2 15938 | 3 15938 | 4 15942 | 1 15942 | 2 What I want is results like this:
i_adgroup_id integer | i_category_id smallint[] ---------------------|----------------------- 15938 | { 2, 3, 4 } 15942 | { 1, 2 } How can I change the original SQL query to give me the result above?
253
Syntax. In the above syntax, we can declare a String Array data type at the time of table creation. Where table name is the specified table name that we need to create and column 1, column 2, and column n declared with the data type of array and it separated by using a comma.
Define arrays as SQL variables. Use the ARRAY_AGG built-in function in a cursor declaration, to assign the rows of a single-column result table to elements of an array. Use the cursor to retrieve the array into an SQL out parameter. Use an array constructor to initialize an array.
PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same.
PostgreSQL Array type PL/pgSQL in PostgreSQL allows us to declare a variable as an ARRAY type. This ARRAY can be either a base or a custom type. For example, if we want to store a list of PINCODE values, then, we can declare the variable as v_pincode INT[].
You want to use array_agg, this should work:
SELECT i_adgroup_id, array_agg(i_category_id) FROM adgroupcategories_br WHERE i_adgroup_id IN ( SELECT i_adgroup_id FROM adgroupusers_br WHERE i_user_id = 103713 ) GROUP BY i_adgroup_id; Note that i_category_id is no longer in the GROUP BY as it is now being aggregated.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
