monetdb set identifier as quoted

Question

I want to get Identifiers with quotes in monetdb.

Say I have a Table tableA which has two columns ColumnA and ColumnB.

I want the quote to be set dynamically. if my ColumnA is quoted and columnB is not it should add quotes for ColumnB.

select "columnA", ColumnB from "tableA" query should run as below query

select "columnA", "columnB" from "tableA" in monet db

I have a scenario where I am getting the dynamic queries. for some identifier (Column name) we are getting as quoted and some are not. I want to make it unified. like those columns names are not quoted it should add qoute for them in monetdb

Exmaple select "tableA".ColumnnameA, tableA.ColumnB from "tableA" when I will execute the above query I will get the error no such column 'tableA.ColumnB' but when I will ad the quote to the second column table name it works fine

select "tableA".ColumnnameA, "tableA".ColumnB from "tableA" this query work for me but I can't add manually quote to the all the query. Is there are property which I can set in the monetDB to add quote whenever it is missing.

Can I implement the same in monetDB?

Joeri van Ruth Joeri van Ruth · Accepted Answer · 2017-10-10T09:00:58

I'm afraid that's not possible in MonetDB, or -- I expect -- in any other SQL database.

The reason is that the SQL standard defines SQL to be case insensitive, so the identifiers foo, Foo, fOo and FOO are considered the same. However, inside double quotes strange characters are allowed and case matters, so "foo", "Foo", "fOo" and "FOO" are distinct.

In fact, MonetDB maps foo, Foo, fOo and FOO all to "foo", as you can see in the following example:

sql>create table foo(i int, "J" int);
create table foo(i int, "J" int);
operation successful (8.551ms)
sql>select i from foo;
select i from foo;
+---+
| i |
+===+
+---+
0 tuples (1.464ms)
sql>select "i" from foo;
select "i" from foo;
+---+
| i |
+===+
+---+
0 tuples (2.686ms)
sql>select "I" from foo;
SELECT: identifier 'I' unknown

so, to wrap up, you're best off either

a) quoting all column names, making sure to get upper/lower case right, or

b) not quoting any column names so case doesn't matter.

When in doubt I'd pick option (a) as it's probably most future proof.

Joeri

monetdb set identifier as quoted

1 Answers