New answers tagged

0

Considering your table has some ID column, you can get it this way: with ct as ( select id, unnest(regexp_split_to_array(t, '\+')) part from a ), ct2 as ( select id, string_agg(part, '|' order by part) parts from ct group by id ) update a set t = ct2.parts from ct2 where ct2.id = a.id; id | t ...


2

The 4.6 minute COMMIT is nearly definitive that your IO system is either grossly inadequate, or just completely hosed. Which would explain all the rest of the problems. If connections can't commit, they won't ordinarily be closed, so they build up. Eventually you get "ERROR: no more connections allowed". If a client gives up on waiting for a commit ...


2

The long commit times indicate that I/O on the database server is seriously overloaded. The machine seems to come to a crawl, so that it cannot respond to connection requests in a timely fashion any more. Examine the database workload and see what causes the overload.


6

You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See: Get distinct on one column, order by another PostgreSQL DISTINCT ON with different ORDER BY SELECT * FROM ( SELECT DISTINCT ON (col1) col1, col2, col3 FROM test ORDER BY col1, col3 DESC ) sub ORDER BY col3 DESC, col2; Assuming that col2 ...


6

This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying. Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here. You can use this query - WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ...


Top 50 recent answers are included