I was faced with interesting problem. Which schema, in my DB, uses the most disk space? Theoretically it's trivial, we have set of helpful functions: pg_column_size pg_database_size pg_indexes_size ...

On 1st of February, Peter Eisentraut committed patch: Add CREATE RECURSIVE VIEW syntax This is specified in the SQL standard. The CREATE RECURSIVE VIEW specification is transformed into a normal CREATE VIEW statement with a WITH RECURSIVE clause. reviewed by Abhijit Menon-Sen and Stephen ...

December 9th, 2012 by depesz | Tags: , , , , , , , | 2 comments »

In PostgreSQL 8.4 we got CTE - Common Table Expressions. Since then we have this great tool available, but apparently for some people it's still black magic. CuTE, but still magic. I'll try to make it a bit less magical, ...

Yesterday on irc someone asked: Hi, how do I get top 5 values from a column group by another column?? From further discussion, I learned that: total rows in table is 2 million. It'll have unique words of less than 1 million.. (approx ...

Yesterday, on #postgresql on irc some guy asked: 22:28 < rafasc> i am trying to use plpgsql to find the shortest path between two cities, each pair of cities has one or more edges, each edge has a different wheight. 22:28 < ...

I got asked on irc to show some examples how to use recursive CTE. Apparently my previous post wasn't good enough :) I think that most of the users will use recursive cte to deal with trees I decided to show ...

Today, on Stack Overflow there was interesting question. Generally, given table that looks like this: room | people | price | hotel 1 | 1 | 200 | ...

Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description: We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 ...

On 4th of September Tom Lane committed another great patch. This one is very large, and even after applying - it's has some rough edges. There will be need for additional patches to make the functionality fully robust, but the ...