How to Handle SQL Column Names That Are SQL Keywords?

1. Introduction

Sometimes, SQL keywords are used as column names due to legacy systems, data migrations, or business needs where non-technical stakeholders choose familiar terms like select, order, or group. Although this can cause confusion and syntax errors, it’s not unusual in database design, especially when dealing with third-party systems, auto-generated schemas, or inherited databases.

In this tutorial, we’ll discuss how to properly manage SQL keywords as column names in PostgreSQL, MySQL, and SQL Server, ensuring that queries can run without issues.

2. Model

We’ll use the Baeldung University schema to write the queries. Specifically, we’ll work with the Course table with one modification. In this tutorial, this table will include a column called group with the following values: beginner, intermediate, and advanced (although this column is not yet part of the current database setup).

Let’s move on with writing SQL queries to retrieve the distinct values for the group column from the Course table.

3. PostgreSQL

In PostgreSQL, we can use double quotes (“) to enclose column names to escape reserved keywords in SQL queries. Let’s write the query to fetch the distinct group names:

SELECT DISTINCT "group" 
FROM Course

We enclosed the column name group with double quotes to avoid conflicts with the reserved keyword. If we omit the quotes, we’ll encounter an error:

[Code: 0, SQL State: 42601]  ERROR: syntax error at or near "group" Position: 17  [Script position: 288 - 293]

This error occurs because GROUP is a reserved keyword in SQL, leading to a syntax error when not properly escaped.

Using double quotes works not only for SELECT statements but also for other SQL commands, such as CREATE, ALTER, and UPDATE.

Additionally, it’s important to note that PostgreSQL generally converts unquoted identifiers to lowercase. Using double quotes not only allows us to escape reserved keywords but also enables us to maintain case sensitivity in column names. If a column name is expected to be case-sensitive, double quotes should be used to ensure the correct casing is preserved. Furthermore, we can use double quotes to escape special characters, such as spaces, in the column names.

4.  MySQL

MySQL also allows using SQL keywords as column names but requires backticks (`) to escape such identifiers. Like in PostgreSQL, enclosing a keyword in backticks ensures that MySQL treats it as a column name rather than an SQL keyword.

Let’s look at the same query in MySQL to fetch distinct group names:

SELECT DISTINCT `group` 
FROM Course;

If we omit the backquote, we get the error:

[Code: 1064, SQL State: 42000]  You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use 
near 'group FROM Course' at line 1  [Script position: 337 - 370]

We can also use backticks to escape reserved keywords and special characters, such as spaces.

Furthermore, if we enable the ANSI QUOTES mode, we can use double quotes in MySQL in the same way as in PostgreSQL.

5. SQL Server

In SQL Server, we can use square brackets ([]).

Let’s check how to write the previous query in SQL Server:

SELECT DISTINCT [group] 
FROM Course;

So, we enclosed the reserved keyword in square brackets to escape it. We can also use this approach to handle column names with special characters, such as spaces.

Furthermore, if the QUOTED_IDENTIFIER setting is enabled in SQL Server, we can use double quotes for column names. This works similarly to PostgreSQL for escaping special characters and reserved keywords.

6. Conclusion

In this article, we explored how to handle reserved keywords in SQL queries for PostgreSQL, MySQL, and SQL Server.

PostgreSQL uses double quotes for this purpose, MySQL utilizes backticks, and SQL Server uses square brackets. These methods apply not only to SELECT statements but also to other commands like ALTER and CREATE.

Additionally, when ANSI mode is enabled, both SQL Server and MySQL also allow the use of double quotes, similar to PostgreSQL.