Linked Questions

130 votes
8 answers

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, ...
Christoph's user avatar
  • 1,493
39 votes
3 answers

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 (...
François Beausoleil's user avatar
48 votes
2 answers

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:...
Ced's user avatar
  • 684
10 votes
2 answers

How to apply ORDER BY and LIMIT in combination with an aggregate function?

A fiddle for my question can be found on I have a simple table layout: class person: belongs to a class I want to ...
Jarius Hebzo's user avatar
5 votes
5 answers

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 ...
Code Poet's user avatar
  • 225
14 votes
1 answer

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 ...
Jelly Orns's user avatar
6 votes
2 answers

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 | ----------...
PaxPrz's user avatar
  • 219
7 votes
1 answer

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 ...
Jeff Camera's user avatar
1 vote
3 answers

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....
sealabr's user avatar
  • 239
2 votes
2 answers

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 ...
lurf jurv's user avatar
  • 290
3 votes
2 answers

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 ...
mountainclimber11's user avatar
3 votes
1 answer

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, ...
Noxville's user avatar
  • 170
3 votes
2 answers

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 ...
plaes's user avatar
  • 175
0 votes
1 answer

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, ...
Somil's user avatar
  • 125
0 votes
1 answer

How to remove NULL values in multidimensional array?

SELECT,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
vyshu Ysh's user avatar

15 30 50 per page