General Language Elements

In this section, you could find information about general statements of Greenplum SQL.

Lower/Upper case identifiers

While in the Greenplum environment, unquoted identifiers are automatically set to lowercase, in Snowflake they are set to uppercase. To better understand this difference, let's take a look at the following code:

Unquoted identifiers

CREATE TABLE Table1 (
    column1 integer
);

SELECT * FROM TABLE1;
SELECT * FROM table1;
SELECT * FROM taBLE1;

In Greenplum, all the SELECT and the CREATE TABLE statements reference the same object saved internally as "table1", all in lowercase. This code is supported in Snowflake too, as there will be no problem since all the table references will be mapped to "TABLE1", all in uppercase.

The problem arises when quoted identifiers are used because the case within double quotes is sensitive. Now, let's look at the following code using some quoted identifiers.

Quoted identifiers

CREATE TABLE Table1 (
    column1 integer
);

SELECT * FROM TABLE1;
SELECT * FROM "table1";
SELECT * FROM taBLE1;

The only difference is line 7, which now references the object named exactly "table1", all in lowercase. In Greenplum, there will be no problem because this is actually the name used when the table is created. However, if this code is executed in Snowflake, there will be a problem when executing line 7, because the object called "table1" (lowercase) does not exist; instead, the actual name of the table in Snowflake will be "TABLE1" (uppercase).

This situation is difficult to handle automatically by SnowConvert. While in this simple example is clear that all identifiers refer to the same object, it is not always possible to know the specific case in which objects were created, as objects can be created dynamically or the input code of SnowConvert might not include all object definitions. One possible solution would be to double quote all object's definitions and uses and automatically set them uppercase however, this is not only expensive but it is also unnecessary in most cases and clients usually don't like this kind of approach.