Linked Questions

38
votes
3answers
33k 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 (...
11
votes
2answers
11k 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. An example will ...
3
votes
5answers
1k 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 ...
7
votes
2answers
2k 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 ...
13
votes
1answer
1k 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 ...
5
votes
1answer
2k 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 ...
1
vote
3answers
663 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....
2
votes
2answers
591 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 ...
2
votes
1answer
745 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, ...
3
votes
2answers
333 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 ...
2
votes
1answer
498 views

Arbitrary queries on n:m relationship, including “all” and “any”

I'm using postgres >= 9.6. I have tasks, tags and task_tags tables, for a typical n:m relationship between tasks and tags. I'd like to be able to programmatically build queries against the tasks ...
2
votes
3answers
78 views

Find all items where the 2 latest statuses meet condition

I have a table in a PostgreSQL database like this: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | EXPIRED 2 | 123 | ... | PENDING ... I want to query all ...
0
votes
1answer
162 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, ...