Content AND Presentation

Last edit

Summary: remove obsolete info about Polish words (they've been translated to English a long time ago anyway).


< (Note to English-speaking readers: the links entitled //Komentarze na tej stronie// lead to comment pages.)


< (//Więcej// means //More// in Polish; click it to see older entries.)

2020-02-15 PostgreSQL and null values in jsonb

Some time ago I was writing some PostreSQL queries involving columns of type jsonb. For some reason I could not understand my query didn’t work as expected. I decided to dig a little bit deeper, and here is what I learned.

As we know, SQL (and PostgreSQL as well, of course) has the null value, which is kind of tricky (for instance, it is not equal to itself). That makes sense, because SQL’s null means “of unknown value”.

As we also know, JSON also has the null value (or type). In JavaScript, it is equal (and even identical) to itself. This also makes sense, because JSON’s null means “empty/no value”.

When we use PostgreSQL to query JSON values which may contain null values, however, hilarity ensues. First of all, if you want to actually type a JSON null in Postgres, you can say e.g. 'null'::jsonb (let’s agree on using jsonb and avoiding json, and calling it just JSON). Of course, this value is different from an SQL null. If you try to say null::jsonb instead, you get an SQL null, since (apparently) casting null to anything results in null (I could not find evidence for this in the PostgreSQL manual, but a few experiments seem to confirm this behavior, and it makes perfect sense).

Now there are at least two ways one can achieve some kind of null when extracting parts of JSON objects: either an explicit null is present in JSON, or the requested property is missing altogether. In the former case, we get a JSON null, and in the latter an SQL null, which is slightly weird given the semantic above, but I can accept it in light of the fact that SQL does not have an “empty” type/value, and there are no natural candidates for the job of representing a value not present in the JSON object (well, technically, PostgreSQL could throw an error in such a case, but it doesn’t – go figure).

Then, there are two ways one can extract something from JSON in PostgreSQL – as JSON (e.g. ->) or as text (e.g. ->>).

Combining the above we get the following results.

select '{"a": 1, "b": null}'::jsonb->'b';

yields a JSON null (so something that does not satisfy the SQL “predicate” is null). However,

select '{"a": 1, "b": null}'::jsonb->'c';

gives a proper SQL null.

Using the text extraction operators, however, we get an SQL null both times:

select '{"a": 1, "b": null}'::jsonb->>'b' is null;
select '{"a": 1, "b": null}'::jsonb->>'c' is null;

The latter is not unexpected at all – we get an SQL null, which (after casting to text) remains null. The former, however, came as a surprise to me. I thought it should be the SQL string 'null', not the SQL null, as casting an explicit JSON null to text gives that string.

The programmers behind PostgreSQL didn’t agree, though. I could not understand why, and I asked a question on StackOverflow about it. After several hours I got a very detailed and interesting response. To cut a long story short, here is the rationale behind this seemingly strange behavior: if PostgreSQL returned 'null' (i.e., a string) in response to select '{"a": 1, "b": null}'::jsonb->>'b';, then the result would be indistinguishable from the result of select '{"a": 1, "b": "null"}'::jsonb->>'b';. One of the design principles of PostgreSQL, however, is that casting anything to text should give something parsable back to the original value (whenever possible).

Well, nice to learn something new.

And while we are at PostgreSQL null and related topics, here is a nice tip I learned some time ago: if you put the line \pset null '∅' in your .psqlrc file, psql will render null values as Unicode “empty set” symbol. It is extremely useful if you need to distinguish between null and an empty string in psql​’s output (unless you have Unicode empty set symbols in your data, of course).

And finally another tip, this time related to the jsonb type, is the jsonb_strip_nulls function. Given a JSONB object, it removes all properties whose values are explicit null​s (and it does it recursively, so deeper levels of hierarchy are affected, too). Note that it keeps null​s that are elements of arrays, though.

CategoryEnglish, CategoryBlog, CategoryPostgreSQL

Comments on this page

2020-02-10 My first steps with Lua

Well, the title of this post is a misnomer – I am already well past my first steps with Lua, since I’ve written a few (admittedly, very simple) Lua scripts. In the near future, though, I’d like to play around with Lua a tad more seriously. (I still do not have a lot of spare time for that, but we’ll see how it goes.)

One thing I missed when I tried Lua last time (it could have been a year or so ago) was a feature found in Node.JS’s npm package manager. In Node, you can describe all the packages you need for your applications/script/whatever and run npm install to have them installed in a local node_modules directory. While this is not necessarily optimal with respect to the disk usage, it is very convenient – every app can be pretty much self-contained. When I researched Lua, it seemed to me that you cannot do a similar thing with Lua “rocks” (which is a cool name btw) – you just have to install them globally (per system or per user).

Well, apparently I was wrong. It seems that it is possible to make Lua behave like Node and install modules under the current directory, and it was already possible to do that almost four years ago. The trick to do that (since it is a trick, this is not something Luarocks can do by default) is described here.

Since reading is one thing and learning is another, let us apply the method from that guide to install a Lua module locally. I decided to use Lua-CSV for that. (Spoiler alert: it wasn’t as simple as I wished.)

I started with creating an empty directory and installing it there using Luarocks:

cd /tmp
rm -rf lua-csv-test
mkdir lua-csv-test
cd lua-csv-test
luarocks install --tree lua_modules lua-csv

Then, following the howto, I created the set-paths.lua file:

local version = _VERSION:match("%d+%.%d+")
package.path = 'lua_modules/share/lua/' .. version .. '/?.lua;lua_modules/share/lua/' .. version .. '/?/init.lua;' .. package.path
package.cpath = 'lua_modules/lib/lua/' .. version .. '/?.so;' .. package.cpath

Finally, let us create the script which is going to actually use the Lua-CSV module. (Of course, I am going to utilize the example script from the README for that.)

local csv = require("lua-csv.csv")
local f ="file.csv")
for fields in f:lines() do
  for i, v in ipairs(fields) do print(i, v) end

The tricky part here was the call to require. It turns out that require("lua-csv") wouldn’t work, as it would try to find the file ./lua_modules/share/lua/5.3/lua-csv.lua, and the luarocks install command installed the module as ./lua_modules/share/lua/5.3/lua-csv/csv.lua. Frankly, I have no idea how to predict where the module file will land after installing other than inspecting the lua_modules directory. (I told you these are my first steps with Lua!).

To top it off, we need the csv file itself:


And now, I can say lua -l set-paths lua-csv-test.lua, and it Just Works™!

All I can say is that this is way more complicated than just npm install module followed by const module = require('module'), but that won’t discourage me. In the next installment (some day!), I am going to use Lua to process my Org agenda in csv format.

CategoryEnglish, CategoryBlog, CategoryLua

Comments on this page

2020-02-02 Encrypted Org-mode journal

I use the Org-mode capturing feature to write a daily journal, where I record various important events in the case I’m going to need the information about them. Some time ago it occured to me that encrypting that journal could be a good idea, so I decided to explore that possibility.

Now Emacs has this nice feature that if you save a file with the gpg extension, it gets automatically encrypted (during the first save, Emacs will ask you for the keys to use, and if you select none, for a passphrase). It turns out that combining the Org-mode capture templates with handling gpg files turns out to be trivial: it is enough to tell Emacs that the journal file ends with .gpg, like this:

(setq org-capture-templates
 '(("j" "Journal entry" entry
    (file+olp+datetree "~/")
    "* %i%?\n"
    :time-prompt t
    :unnarrowed t)))

(I personally like :unnarrowed for journaling, but YMMV) – and that’s it.

CategoryEnglish, CategoryBlog, CategoryEmacs

Comments on this page


CategoryEnglish, CategoryBlog