In the name of simplicity I am using a Extract, Load and Transform (ELT) architecture on a few recent data warehouse build-outs. In my case, this means that one database server will do the transformation and serving of reporting data. Using postgres json tools I am able to dump my extracts immediately into my reporting database and begin the transformations from there.

This also means that database needs to be ready for nearly any kind of input. Queue dollar quoting…

Dollar Quoting

Dollar quoting is a feature that allows you to avoid using the common single-quotes and double-quotes for defining strings in your SQL. The immediate benefit is that “$$” is a less common occurrence in strings, this means less collisions and less escape characters. A small bonus feature is that it tends to be more prominent in your code so strings will become more obvious in your code.

INSERT INTO raw_json (raw_json_id, raw)
VALUES (1,
        $$
        {
            "issueId": 1,
            "issueName": "Mo Money Mo Mo Problems.",
            "issueSummary": "It appears that the more money I have, the more trivial problems enter my life as a result of said money."
        }
        $$
        )

As you can see this solution becomes more robust in terms of being able to handle a wider range of inputs. But we can do better. What happens in the above example when someone writing our inputs gets carried away with the dollar signs? We’re back to square one.

This is why it is also important to leverage dollar quote tags to further increase the uniqueness of your string quoting. You can make a dollar quote tag whatever you want. Something simple such as $string_literal$ will decrease chances of literal. If you will be ingesting a lot of raw json or text then you may want to take it to the next level and generate a random hash that will give you the best protection against collisions.

UPDATE raw_json
    SET raw = $0MYiG9nU9IpK$
                       {
                            "issueId": 1,
                            "issueName": "Mo $$ Mo Problems.",
                            "issueSummary": "It appears that the more $$ I have, the more trivial problems enter my life as a result of said $$."
                        }
              $0MYiG9nU9IpK$
WHERE raw_json_id = 1

In summary, using dollar quotes from the get go during development can save you a lot of time re-writing code or escaping characters. If nothing else it’s another tool you can throw in the toolbox until needed. ⤧  Next post Stargazer ⤧  Previous post Context Budgeting