1

For whatever reason, there is a column in the database named order. I can't select it in a query in Snowflake. The following all fail due to

SQL compilation error: syntax error line 1 at position 9 unexpected 'order'.

// Obviously, this fails.
select order
from example_table

// Also fails
select a.order
from example_table as a

// Identifier doesn't exist.
select "order"
from example_table

I'm out of ideas on how I'm supposed to select this column. It is out of my hands to change the column's naming in the underlying database table.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
zojwek
  • 121
  • 9
  • Can you try some of the options listed here: https://stackoverflow.com/questions/2901453/sql-standard-to-escape-column-names – ImAtWar Aug 14 '21 at 11:51
  • @ImAtWar these all fail. Specifically, only the "order" keyword triggers this. There are a variety of poorly named columns, such as "index", but doing something like a.index works fine. I've not run into this issue outside of Snowflake, which is why this question is tagged Snowflake. – zojwek Aug 14 '21 at 12:04

2 Answers2

4

It works as intended in both scenarios:

CREATE OR REPLACE TABLE example_table("order" INT);
INSERT INTO example_table VALUES (1),(2);

SELECT "order" FROM  example_table;
-- 1
-- 2

SELECT e."order" FROM  example_table e;
-- 1
-- 2

Most likely there is invisible character used or name is not lowercase as stated in question. When quoting identifier with " it has to be 1:1 as it was created.

Identifier Requirements

If you put double quotes around an identifier (e.g. “My identifier with blanks and punctuation.”), the following rules apply:

The case of the identifier is preserved when storing and resolving the identifier (e.g. "id" is stored and resolved as id).

CREATE OR REPLACE TABLE example_table("Order" INT);
INSERT INTO example_table VALUES (1),(2);

SELECT "order" FROM  example_table;
-- Error: invalid identifier '"order"' (line 17)

SELECT "Order" FROM  example_table;
-- 1
-- 2

The "actual" column name could be found using one of the methods described below:

DESCRIBE TABLE example_table;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ILIKE 'example_table';

SELECT GET_DDL('table', 'public.example_table');
-- create or replace TABLE EXAMPLE_TABLE ( "Order" NUMBER(38,0) );
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    Ah, incredible, sleep deprivation got me on that one haha. You're right, all the columns are all-capsed when migrated into Snowflake. I was reading off the production tables rather than the ones in Snowflake. Thus the issue with using double quotes. Thanks! – zojwek Aug 15 '21 at 06:12
2

Try using double quotes for the column names in the create statement create or replace table example_table("order" int)

Nayana R
  • 23
  • 6