Linked Questions
21 questions linked to/from Why is array_agg() slower than the non-aggregate ARRAY() constructor?
130
votes
8
answers
421k
views
How to turn JSON array into Postgres array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, ...
- 1,493
39
votes
3
answers
41k
views
Are regular VACUUM ANALYZE still recommended under 9.1?
I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?
If the answer is "it depends", then:
I have a largish database (...
- 1,413
48
votes
2
answers
64k
views
Convert right side of join of many to many into array
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:...
- 684
10
votes
2
answers
13k
views
How to apply ORDER BY and LIMIT in combination with an aggregate function?
A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685.
I have a simple table layout:
class
person: belongs to a class
I want to ...
- 415
5
votes
5
answers
5k
views
How to give precedence to rows from one table over matching rows in another?
I've got (key, value) pairs stored in two different tables - company_settings and branch_settings. A key can exist in none, one or both of the tables.
In case the key exists in both tables, I'd like ...
- 225
14
votes
1
answer
9k
views
Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?
I have the following tables (taken from the Sakila database):
film: film_id is pkey
actor: actor_id is pkey
film_actor: film_id and actor_id are fkeys to film/actor
I am selecting a ...
- 143
6
votes
2
answers
2k
views
Postgres not returning data on array_agg query as below
The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table library
--------------------------------
| id | name | owner |
----------...
- 219
7
votes
1
answer
6k
views
What is the recommended way to join junction tables for efficient ordering/pagination?
Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem.
Database: PostgreSQL 9.6
Simplified ...
- 173
1
vote
3
answers
2k
views
UPDATE BIGINT[] column from a SELECT
I have to update a bigint[] column named permissao_ver.
Example value: '{1,2,3,4,5,11,44,56,75,11}'.
Pseudo code:
UPDATE callcenter.pausa
SET permissao_ver = '{"(SELECT cod_grupo FROM crm....
- 239
2
votes
2
answers
1k
views
Build array from boolean columns
I have columns like role1, role2, role3, etc. They are all booleans.
I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the ...
- 290
3
votes
2
answers
2k
views
PostgreSQL: trigger to autopopulate other fields with sub-strings of other field inserted
I am looking to insert 1 to 8 sub-string values from an inserted string value into other fields as integers using a trigger in PostgreSQL. The integer values have a string prefix to identify them ...
3
votes
1
answer
3k
views
Multiply elements of equally sized postgresql array
In Postgresql 9.6, is there a clean way to multiply corresponding elements within two equally sized arrays?
For example
a = [1,2,9,4,5]
b = [5,8,4,0,12]
function(a, b) would return [5, 16, 36, 0, ...
- 170
3
votes
2
answers
3k
views
Select into specific array positions with array_agg()?
Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.)
For example, I would like to select an item and its stock ...
- 175
0
votes
1
answer
2k
views
How to get newly inserted row ids when inserting with execute?
I have a function which returns newly inserted row count:
BEGIN
EXECUTE (
SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
_tbl, ...
- 125
0
votes
1
answer
2k
views
How to remove NULL values in multidimensional array?
SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
