On 31st of January, Robert Haas committed patch: Like the XML data type, we simply store JSON data as text, after checking that it is valid. More complex operations such as canonicalization and comparison may come later, but this is enough for not. There are a few open issues here, such as whether we … Continue reading “Waiting for 9.2 – JSON”
This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references? Decided to take a look at this task.
Whenever people look for help with queries, one of the first things that I ask is: what is \d of the table. Which works great, with the caveat, that the person on the other end must be somewhat familiar with psql. And quite often it’s not the case. So I figured, I can try to … Continue reading “Now you can \d table not only in psql :)”
Title: On 9th of December 2022, Tom Lane committed patch: Add test scaffolding for soft error reporting from input functions. pg_input_is_valid() returns boolean, while pg_input_error_message() returns the primary error message if the input is bad, or NULL if the input is OK. The main reason for having two functions is so that we can … Continue reading “Waiting for PostgreSQL 16 – Add test scaffolding for soft error reporting from input functions.”
Lately in couple of places I recommended people that they can solve their problem with queries using LATERAL. In some cases recipient of such suggestion indicated that they had no idea what LATERAL is. Which made me think that it might be good idea to write more about them (lateral queries)… Also – I know … Continue reading “What is LATERAL, what is it for, and how can one use it?”
Every so often, on irc, someone asks how to get value from column that is passed as argument. This is generally seen as not possible, as pl/PgSQL doesn’t have support for dynamic column names. We can work around it, though. Are the workarounds usable, in terms of performance?
Some time ago James Courtney reported missing functionality. Specifically, when one uses auto-explain, logged explains contain query text. So, when such explain is then pasted on explain.depesz.com, it stands to reason that it should be able to extract the query on its own, without having to manually extract it and put it in query box. … Continue reading “Changes on explain.depesz.com – extracted query from auto-explain plans”
Yaroslav Schekin (ysch) reported on irc that Index Scans Backward do not display properly. After checking I found out that if explain is in JSON/YAML/XML – node type is changed to “Index Scan” (or “Index Only Scan” if it was originally “Index Only Scan Backward”).
On 11st of December 2020, Tom Lane committed patch: Allow subscripting of hstore values. This is basically a finger exercise to prove that it’s possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be … Continue reading “Waiting for PostgreSQL 14 – Allow subscripting of hstore values.”
The question was asked relatively recently on irc. And it proved to be non-trivial. Surely, if you want to have one row per category (one address per user), it’s trivial – add user_id column to addresses, make it unique, and we’re done. But what if we want to allow five addresses? Or five thousands? Let’s … Continue reading “How to limit rows to at most N per category”