postgresql

Implementing SQL Server's WITH TIES in PostgreSQL

How to implement SQL Server's WITH TIES in PostgreSQL

Neil Sainsbury

3 minute read

SQL Server supports a TOP (N) WITH TIES clause which can be used to return the top N rows from a query along with any additional rows that have the same sort value as the Nth row. It’s handy functionality that can come in use in many situations. In this post, we’ll take a look at how you can implement the equivalent functionality in PostgreSQL (you can find the full source code to reproduce this example on GitHub here for SQL Server and here for PostgreSQL).

Joins: USING vs ON for join conditions

A subtle difference between ON and USING when establishing join conditions

Neil Sainsbury

2 minute read

When establishing the join condition between two tables in PostgreSQL, and you’re performing an equality join where the columns are named the same in both tables, a nice shorthand syntax to establish the join is with USING. You might think this is identical to establishing the join condition with ON, but there’s a subtle difference. Let’s dive in and take a look with an example (you can find the full source code to reproduce this example on GitHub here)

Permission Denied error when using PostgreSQL's COPY FROM/TO command

Getting a permission denied error when using COPY? Here's why and how to fix it.

Neil Sainsbury

2 minute read

COPY FROM is commonly used to import data from a file (CSV, plain text, etc.) in to PostgreSQL. Likewise, COPY TO is used to export data from a table or a query result to a file. When you’re using these commands if you’re getting a “Permission Denied” error, here’s the likely reason why and how to fix it.

Using Distinct in SQL

A quick look at the different ways you can use DISTINCT and PostgreSQL's powerful DISTINCT ON

Neil Sainsbury

4 minute read

The DISTINCT keyword has a bunch of interesting use-cases in ANSI SQL and offers some additional functionality when used in PostgreSQL. Let’s dive in and take a look with an example (you can find the full source code to reproduce this example on GitHub here)