I've recently read a blog post where the author is documenting one of their struggles when migrating from Oracle Database to PostgreSQL. I want to drill down on the core problem further and expose what's the difference between the two SQL dialects. This is the query that caused problems the original author: CASE WHEN TO_CHAR(varmonth, ' MI ' ) + 1 = 60 THEN varhr - 1 || TO_CHAR(varmonth,…
Lutra General-purpose query language Home Docs Code Zulip chat For the past year I've been working on my own query language that I now call Lutra. It's a continuation of my work on PRQL , with more focus on language design and correctness. In its current state, Lutra is not ready for production use, but it is in the form where it can be tried out, critisized, and most importantly,…
This simple query does what we expect it to do. ( SELECT ' a ' LIMIT 1 ) UNION ( SELECT ' b ' LIMIT 1 ) It returns: ?column? a b What if we remove parenthesis? SELECT ' a ' LIMIT 1 UNION SELECT ' b ' LIMIT 1 syntax error at or near "UNION" LINE 2: UNION Alright, a syntax error. What if we remove paretenthesis from the last select? ( SELECT ' a ' LIMIT 1 ) UNION SELECT ' b ' LIMIT 1 ?column? a…
I want to write down how like to deal with projects in software development. These things might be obvious to some, a debatable point for others and blasphemy to many. Source code The basic unit of software is a directory of source code. Preferrably, this is git repository, so the code is versioned and can be rolled-back, and worked on concurrently with other team members. It should not contain…
In a way, writing programs is just attaching names to pieces of data and then passing them trough functions. We write elevation , but in reality, it will be 2864 . We write movie , but in reality, it will be {id = 3, title = "Parasite"} We write request , but in reality, it will be GET /mondrian HTTP/1.1\n\r . So the names that we throw around the code can represent very different data.…
In the age of Single-Page-Applications and REST APIs (JSON over HTTP), the cool kids use JSON as the data serialization format. In other words, to transfer data over the wire, they convert the data structures from memory into a series of bytes which are, nominally, valid JSON documents. Most of the time, REST responses have a predefined schema for each endpoint and will always respond with that…
When GROUP ing an SQL relation, additional constraints are applied to what columns can be used elsewhere in the query. Grouping keys can be referred to plainly, while all other columns can only appear within aggregation functions. SELECT status, SUM (total) FROM invoices GROUP BY status -- OK SELECT status, total FROM invoices GROUP BY status -- ERROR: column "invoices.total" must appear in the…
When SQL contains a query parameter (i.e. $1 ), PostgreSQL will try to determine types of that parameter or determine that the type is unknown and fallback to string encoding. For example here: SELECT $ 1 ::uuid; ... postgres will infer that $1 is of type pg_catalog.uuid . Great. As a bonus, when passing parameters to your PostgreSQL client of choice, they will probably also accept a text-encoded…
In SQL, VALUES query is used to construct a "relation literal". Using PostgreSQL, that would look like this: VALUES ( ' Hello ' , 42 ), ( ' world ' , 13 ), ( ' ! ' , 0 ) column1 column2 Hello 42 world 13 ! 0 But what if one would like to construct a relation with no columns but with, say, 3 rows? VALUES ( ) , ( ) , ( ) psql:commands.sql:1: ERROR: syntax error at or near ")" LINE 1: VALUES (), (),…
Most modern databases use data model named Relational Algebra (RA), which defines a relation as a set of tuples and a few operation you can do on relations. I want to point out a specific detail about the tuples: its fields (also known as attributes or columns) have a unique identifier within the tuple. In practical terms, columns have names. This also holds for SQL, which is used to express RA in…
In the last decade I noticed a shift in culture of watching movies. It was probably fueled by advances in internet bandwidth and video streaming, or maybe a change of our lifestyles in general or maybe even something about business side of making video content. Regardless of the reason, I've noticed that my habits of watching video shifted from a mindful selection of content toward a mediocre…
Command line shells are interactive programs for running other programs. To run a program, just type its name followed by a list of arguments. This rudimentary language does not know any other types than strings, so the name of the program and all of its arguments are just strings, delimited by spaces. ./build/my_program some-command --with-a-flag -m message input_file.txt Although very…
TLDR; use vscode, it's very simple. Even if you have no use for version control, future you may have, and more importantely, your collegues may have. Setup Install vscode . This is not a requirement, but it has nice interface so this tutorials advises you to do so. Install git Set your username and email. This will be displayed as author of your work to other collaborators. Open a terminal…
I have a few repositories that I don't want to share with the world or even GitHub, BitBucket or GitLab. Not because they contain top-secret information, but because I believe that we should store and pay for our own data instead of spreading it over dosen of different could platforms. If you have a private server with ssh access, then this is an easy task. You don't need any special…
A few weeks back I've seen a post about how an organized file system is not the way many people today use computers. Instead, they manipulate their data in the way of fragments (think documents, links, open tabs, images), which they move, save and copy-paste around without explicit intent to ever see them again. In the case when they do, they have little recollection of where the fragment is,…
TLDR; Let's make a framework for protocols that introduces the notion of 'user' and handles federation and user device connectivity. If I were to become an all powerful god who could dictate all and any decisions made, we would have instant messaging. Not Signal, WhatsApp, Facebook Messenger, iMessage or even Matrix, but only instant messaging. When you would want someone's…