Linked Questions

140 votes
8 answers
455k 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, ...
Christoph's user avatar
  • 1,593
39 votes
3 answers
42k 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 (...
François Beausoleil's user avatar
54 votes
2 answers
72k 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:...
Ced's user avatar
  • 754
10 votes
2 answers
15k views

How to combine ORDER BY and LIMIT with an aggregate function?

Here is a fiddle for my question. I have a simple table layout: class person: belongs to a class I want to select all classes, and for each class, I want the first two person identifiers of the ...
Jarius Hebzo's user avatar
14 votes
1 answer
11k 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 ...
Jelly Orns's user avatar
5 votes
5 answers
6k 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 ...
Code Poet's user avatar
  • 225
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 | ----------...
PaxPrz's user avatar
  • 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 ...
Jeff Camera's user avatar
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....
sealabr's user avatar
  • 249
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 ...
lurf jurv's user avatar
  • 330
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 ...
mountainclimber11's user avatar
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, ...
Noxville's user avatar
  • 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 ...
plaes's user avatar
  • 175
0 votes
1 answer
3k 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, ...
Somil's user avatar
  • 125
2 votes
2 answers
2k views

How to get lexemes in original sort order from ts_vector?

Postgres' full text search to_tsvector() function returns a sorted array of lexemes. I need exactly that, just with lexemes in original sort order. Is there any such Postgres function, or do I need to ...
zlatko's user avatar
  • 175

15 30 50 per page