sql: doesn't allow 'index' as a column name

[steven-sheehy]

Describe the problem

Attempting to convert a PostgreSQL based application to CockroachDB. Found that CockroachDB is more strict than necessary in disallowing SQL keywords as column names. PostgreSQL works with a column named index while CockroachDB does not. Index is a pretty common name for fields since it denotes an application generated identifier that differs from a DB identifier. Since it does not appear where the SQL keyword usually appears it should not be considered a syntax error.

To Reproduce

1. cockroach demo
2. Run SQL with reserved keyword index as a column name

[email protected]:26257/movr> create table foo(index bigint);
invalid syntax: statement ignored: at or near ")": syntax error
SQLSTATE: 42601
DETAIL: source SQL:
create table foo(index bigint)
                             ^
HINT: try \h CREATE TABLE

3. Watch it succeed after adding a 2

[email protected]:26257/movr> create table foo(index2 bigint);
CREATE TABLE

Time: 4ms total (execution 4ms / network 0ms)

Expected behavior
Parity with PostgreSQL to allow index as a column name.

Additional data / screenshots

Environment:

• CockroachDB version v21.1.6
• Server OS: MacOS
• Client app: cockroach sql

Additional context

Had to rename columns

Jira issue: CRDB-9589

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[jordanlewis]

This is a known deficiency that doesn't have an easy fix. As a workaround, you can double-quote your column name (CREATE TABLE foo ("index" BIGINT)).