SnowConvert AI - Vertica - Identifier differences between Vertica and Snowflake¶

Quoted identifiers¶

In Vertica, quoted identifiers stick to the case sensitivity rules, which means that, for example, column names are still case insensitive even when quoted. Thus, identifiers "ABC", "ABc", and "aBc" are synonymous, as are ABC, ABc, and aBc :

Vertica¶

CREATE TABLE test.quotedIdentTable
(
  "col#1" INTEGER
);

SELECT "col#1" FROM test.quotedIdentTable;

SELECT "COL#1" FROM test.quotedIdentTable;

In Snowflake, case sensitivity of quoted identifiers depends on the session parameter QUOTED_IDENTIFIERS_IGNORE_CASE, by default quoted identifiers comparison is case sensitive, this means that the result code from migrating the above example:

Snowflake¶

CREATE TABLE test.quotedIdentTable
(
  "col#1" INTEGER
);

SELECT
  "col#1"
FROM
  test.quotedIdentTable;

SELECT
  "COL#1"
FROM
  test.quotedIdentTable;

Will fail when executing the second select unless the session parameter is set to TRUE.

How SnowConvert AI migrates quoted identifiers¶

SnowConvert AI will analyze quoted identifiers to determine if they contain non-alphanumeric characters or are reserved words in Snowflake, if they do SnowConvert AI will leave them as they are, alphanumeric identifiers will be left unquoted:

Vertica¶

CREATE TABLE test.identsTable1
(
  "col#1" INTEGER,
  "col2" INTEGER
);

-- Group is a reserved word
SELECT 
"col#1" AS "group",
"col2" AS "hello"
FROM
test.identsTable1;

Snowflake¶

CREATE TABLE test.identsTable1
(
  "col#1" INTEGER,
  col2 INTEGER
);

-- Group is a reserved word
SELECT
  "col#1" AS "group",
  col2 AS hello
FROM
  test.identsTable1;