154 Articles tagged β€œPostgresql”

on Json and SQL

PostgreSQL has had proper json support for a while now. The unique extensibility approach of the PostgreSQL system allows it to enable native SQL friendly JSON processing.

In this article we’ll play with the Magic: the Gathering card data in JSON format data set, provided with a CC0 licence, and process the information provided. We also see how to normalize a JSON document into a proper database model that benefits from some PostgreSQL advanced features, and how to then inject the JSON documents into the normalized database schema. Finally, we compare some non-trivial processing done against both versions of the database schema.

Mastering PostgreSQL in Application Development is the full title of the book I am currently writing. Running the PostgreSQL is YeSQL series of blog posts has shown me developers need a PostgreSQL book for developers. A book with the same properties as the YeSQL series articles in this blog: we use real world data sets to put every query and SQL technique we learn in the context of a user story or business case,

In a previous article here we saw How to Write SQL in your application code. The main idea in that article is to maintain your queries in separate SQL files, where it’s easier to maintain them. In particular if you want to be able to test them again in production, and when you have to work and rewrite queries.

The reason why I like Unicode a lot is because it allows me to code in text based environments and still have nice output. Today, we’re going to play with Regional Indicator Symbol, which is implemented as a Unicode combinaison of letters from πŸ‡¦ to πŸ‡Ώ. For instance, if you display πŸ‡« then πŸ‡· concatenated together, you get πŸ‡«πŸ‡·. Let’s try that from our PostgreSQL prompt!

The modern calendar is a trap for the young engineer’s mind. We deal with the calendar on a daily basis and until exposed to its insanity it’s rather common to think that calendar based computations are easy. That’s until you’ve tried to do it once. A very good read about how the current calendar came to be the way it is now is Erik’s Naggum The Long, Painful History of Time.

Business logic is supposed to be the part of the application where you deal with customer or user facing decisions and computations. It is often argued that this part should be well separated from the rest of the technical infrastructure of your code. Of course, SQL and relational database design is meant to support your business cases (or user stories), so then we can ask ourselves if SQL should be part of your business logic implementation. Or actually, how much of your business logic should be SQL?

Sometimes you need to dive in an existing data set that you know very little about. Let’s say we’ve been lucky to have had a high level description of the business case covered by a database, and then access to it. Our next step is figuring out data organisation, content and quality. Our tool box: the world’s most advanced open source database, PostgreSQL, and its Structured Query Language, SQL.

How to Write SQL

Kris Jenkins cooked up a very nice way to embed SQL in your code: YeSQL for Clojure. The main idea is that you should be writing your SQL queries in .sql files in your code repository and maintain them there.

The idea is very good and it is now possible to find alternative implementations of the Clojure yesql library in other languages. Today, we are going to have a look at one of them for the python programming language: anosql.

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer