Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
6

I've done this in stages using CTEs so that you can see how it's done as the queries progress. Each CTE adds a column in the output in order to show you progress. It's pretty much self-documenting with the CTE names, to be honest. with lags as ( select player, dt, is_winner, lag(is_winner) OVER (partition by player ORDER BY dt ...


3

I don't care much about the cluster column just need ids properly aggregated. In that case we can make use of the uniq and sort functions in the intarray extension: with recursive a as ( select id, array_agg(distinct clst) clst from w group by id) , t(id,pid,clst) as ( select id,id,clst from a union all select t.id,a.id,t.clst|a.clst from t join ...


3

You can change log_min_messages to notice which will then write all notices to the server's log file. Note that a client can overwrite that setting any time, so it's not 100% reliable.


3

With PostgreSQL in general (not EDB specifically) you would do: stop the PostgreSQL server start postgres in a shell in single user mode. UPDATE pg_database SET datallowconn=true WHERE ... [to undo the error]. Be aware that template0 should normally be left with datallowconn=false. Type Ctrl+D to quit the single user mode. start PostgreSQL normally.


2

You can add LIMIT 1, but LIMIT is applied after aggregation (so after the count). Adding it in the same query level does nothing useful, since there is only 1 row left to return anyway: the row count. Would be misleading nonsense. If you want to go that route, add LIMIT in a subquery and count in the outer SELECT, like: SELECT count(*) FROM ( SELECT ...


2

Partitioning is mostly about speeding up deletes and sequential scans. If your biggest pain is the big deletes by account_id, list partitioning on that column would be the best solution. If your biggest pain is getting rid of all data older than a certain date, range partitioning by time would be the solution. If you have may queries that have to perform ...


2

Once you drop cluster for version 9.5, you may upgrade your 9.3 to 9.5 with this command: sudo pg_upgradecluster -v 9.5 9.3 main If you omit -v 9.5 it will upgrade the cluster to the latest postgresql version you have installed. For more information, type man pg_upgradecluster. Now, since you already upgraded to 10, you have to stop the new cluster (...


2

Because that is how it was designed and implemented. See here If BC was not specified, and if the year field was two digits in length, then adjust the year to four digits. If the field is less than 70, then add 2000, otherwise add 1900. (although two digits should probably be "one or two") People often use the last digits of a recent past or near future ...


1

Looks like this can be done with the event triggers of 9.4. There are some weak points: pg_trigger_depth() always returns zero (Is this a bug?). This is why I had to use a non-temporary table to prevent recursion. We need to filter by the altered object name, but there's no way to get it in 9.4. current_query() returns the client's statement even if we're ...


1

You can do it with "fancy Postgres" features - much easier than "fancy MS SQL features" ;) You can aggregate all the group IDs into an array and then compare that. If with "all of" you mean those users that are assigned to exactly those groups, you can use something like this: SELECT u.id FROM users u JOIN user_group ug on ug.user_id = u.id group by u....


1

An exclusion constraint would seem like the weapon of choice: ALTER TABLE tbl ADD CONSTRAINT tbl_names_overlap EXCLUDE USING gist (names WITH &&); Alas, as of Postgres 11, still no GiST indexes for arrays. And GIN indexes are not (yet) allowed to implement exclusion constraints. There is a TODO item "Allow GIN indexes to be used for exclusion ...


1

This should perform nicely: UPDATE books AS b SET users = bu.users FROM ( SELECT b1.name, u.users FROM books b1 CROSS JOIN LATERAL ( SELECT ARRAY( SELECT user_id FROM unnest(b1.users) WITH ORDINALITY u(user_id, ord) JOIN users u USING (user_id) ORDER BY u.ord ) ) u(users) ) bu ...


1

It needs to be in the GROUP by of the "xy1" subselect, not of the outer query. But it probably really needs to be taken out of the select list of "xy1" and not put in the GROUP BY. Having it in both the string_agg and the GROUP BY would be legal, but seems rather pointless. Where the error message says "or be used in an aggregate function", it means "or ...


1

If there are multiple rows with the lowest value for index, then no, there is no guarantee you will always get the same row. If you only specify ordering for a column that contains duplicates, then the database does not have to provide anything more than any row that meets the criteria. Same would be true if you were to say ORDER BY index DESC - which row ...


1

I agree with that person. Here is an implementation. Basically, add a UNIQUE array column of all chem_ids to table mixtures and keep it current with triggers. Arrays must be sorted consistently, I use the additional module intarray for that and to optimize performance. For lack of definition I assume frequent multi-row writes, making it a perfect ...


1

They are alphanumeric in some alphabet, just not the one you want to use. You can alter your text search configuration to drop the types of tokens you do not want. You should probably copy and alter, not alter in place. CREATE TEXT SEARCH CONFIGURATION ascii_only ( COPY = pg_catalog.english ); alter text search configuration ascii_only drop mapping for ...


1

It is only slightly more code to skip the UPDATE if there is nothing to do. And the code is much cleaner. And less kludgy.


1

From https://www.postgresql.org/docs/9.4/sql-explain.html: Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases.


1

By default Postgres automatically compresses everything TEXT. It uses a simple lzcompress algorythm: https://www.postgresql.org/docs/9.3/storage-toast.html There is a plugin that will probably evolve to LZ4 compression support for TEXT: https://github.com/zilder/pg_lz4 There is a FDW that also support compression: https://www.citusdata.com/blog/2014/04/...


Only top voted, non community-wiki answers of a minimum length are eligible