Unlocking PostgreSQL: Insights on Grammar & Operators

Host

Welcome back to Log Blog Kebab! Today, we’re diving into some fascinating aspects of Postgres grammar. If you’ve ever wondered what makes Postgres unique, you’re in the right place.

Expert

Thanks for having me! I’m excited to share what I've learned from building a linter for PostgreSQL called Squawk, especially focusing on its handmade parser.

Host

That sounds intriguing! So, let's start with custom operators. I hear they play a significant role in Postgres. Can you explain that?

Expert

Absolutely! In Postgres, very few operators are defined in the grammar itself. Instead, a lot of features rely on custom operators. For instance, operators like <-> are used for comparing geometric types. There are many others too—like ##, @>, and even <@>.

Host

Wow, that’s a lot of operators! Do they all behave the same way?

Expert

Not quite. They can be either prefix or infix, but not postfix. A neat feature of custom operators is that they make it possible to use lambda expressions from Trino directly in Postgres, though the precedence can be a little off.

Host

Interesting! Speaking of precedence, I heard there’s a specific way complex selects work in Postgres? Can you elaborate?

Expert

Sure! When you write something like 'select foo union select bar order by baz,' it actually parses as '(select foo union select bar) order by baz.' This means the order by applies to the whole compound select, not just one part.

Host

That’s a clever way to manage queries. Now, I’m curious about percent types. How do they work in practice?

Expert

With functions, you can specify a type based on a table’s column type. For example, if you have a column 'c' on a table 't', you could define a function like this: 'create function f(a t.c%type) as 'select 1' language plpgsql;'. It’s a convenient way to ensure type consistency.

Host

That’s very useful! Now, I’ve heard that string literals can be combined in a unique way. Can you explain that?

Expert

Yes! If you have two string literals separated by a new line, they get merged into one. So, 'select 'foo' 'bar';' would return 'foobar'. But if there's a comment in between, like 'select 'foo' /* hmm */ 'bar';', it triggers a syntax error. This is actually part of the SQL standard.

Host

That’s quite the quirk! What about quoted identifiers? How do they function in Postgres?

Expert

In Postgres, you can quote your identifiers. For instance, 'select * from t;' is the same as 'select * from "t";'. If you need to include a double quote in your identifier, you can escape it like this: 'select * from "foo "" bar";'. This will select from the foo " bar table.

Host

I love how flexible that is! Are there any other interesting features related to operators?

Expert

Definitely! Instead of using an operator directly, you can use the operator function like this: 'select 1 operator(+)' 2. This lets you specify the operator’s schema too, making it very versatile.

Host

This has been an enlightening discussion! Custom operators really set Postgres apart from other databases, don't they?

Expert

Absolutely! While they can be tricky to implement, they add a lot of power to the language.

Host

Thanks for sharing your insights today! That’s it for this episode of Log Blog Kebab. Join us next time for more tech adventures!