The end of the year has come again. For one, it’s a moment of celebration last year’s achievements, for the other it’s a relief a new year will dawn. The old will soon be replaced by something new. There is both beauty and sadness in the circle of life.

From a programmer’s' perspective, I am always fascinated by the concept of bootstrapping, a technique for producing a self-compiling compiler. It’s like the story of the phoenix, where the source of life is obtained from the ashes of its predecessor. So the compiler compiles source code generated by its own predecessor compiler. It somehow feels quite fitting to ponder about this at the end of the year.

I am no compiler expert at all, but I had to think about all of this when I was working at a project of late. I had to share some data records from my PostgreSQL database to my colleague’s' database. Of course I could have made a SQL dump and shared that. But I wanted to share just a couple of records, specific to some constraints. Though I can’t share the details of the project, let me share the idea!

Let’s say you have an 'animal' table with the following records:

For our case, we want to import the cats into another database only. To retrieve this data, we could write a query easily:

SELECT type, name FROM animal WHERE type = 'cat';

This output data cannot be imported into another database, because databases uses INSERT queries to do just that. But when you think about this, we could generate the INSERT queries as output, just like the self-compiling compiler compiles its own source. The trick to do this is rather simple. A SELECT query can also select a hardcoded string.

To give you an example, let’s say you would add an imported flag to the output data:

SELECT type, name, 'import' as flag FROM animal WHERE type = 'cat';

Armed with this knowledge, we can also create entire queries. So a simple INSERT query can easily be made:

SELECT 'INSERT INTO animal (id, type, name) VALUES (''' || id || ''',''' || type || ''',''' || name || ''');' FROM animal WHERE type = 'cat';

Do not be troubled about the quotes and the || characters. Let me explain: Instead of the commonly used \, you need to escape a quote with another quote. To end the string you need another quote. That’s three in total.

To concat a string you can either use the CONCAT function or the || operator (see spec). After running the query, the output will be a bunch of INSERT queries!

And so this little story comes to an end. But new beginnings are already in the making. Though you probably will not read this at the end of this year, I still wish you all the best!