Derby Database Table Column Name Format Inconsistent in Query

Question

When query a Derby database, I find out that for some tables I have to double quote the column name and use table name to qualify the column name, but for some other tables I don’t need to. What happens to these tables and how can I make all tables the same and can query them without the double quote and the table name qualifier? I am using NetBeans IDE’s Sql Command tool. Below are those different queries.

Set schema app;
Select * from table1 where table1.”state” = ‘CA’;
Select * from table2 where state = ‘CA’;

In fact I found some tables also need double quote and the query is like: Select “table3”.”customer” from “table3” where “table3”,”state” = ‘CA’; — David
– David, Commented Aug 30, 2012 at 20:03

Bryan Pendleton · Accepted Answer · 2012-08-31 14:32:33Z

3

Putting a tablename or column name in quotes, sometimes referred to by the jargon-y term "delimited identifiers" does two things:

Allows you to use words that are otherwise reserved keywords (e.g., naming a column "WHERE" or "SELECT")
Instructs the database system to process the name using case sensitive rules, rather than case-insensitive rules

So if you originally created "table3" with a CREATE TABLE statement that specified "table3" in double quotes like this, then you will forever after have to refer to it with the name in double quotes.

select * from table3

will be automatically processed by the database as if it was

select * from TABLE3

while

select * from "table3"

will successfully match the table you created as create table "table3"

See: http://db.apache.org/derby/docs/10.9/ref/crefsqlj34834.html

answered Aug 31, 2012 at 14:32

Bryan Pendleton

16.4k3 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Thank you very much for your help. The database is created by other people. Your explanation on the double quote is very clear. But why I have to qualify every column name with the table name in my query? Even I copy the old table to a new table with the following commands: create table new_table as select * from "old_table" with no data; insert into new_table select * from "old_table";

Bryan Pendleton Over a year ago

Try using the ij "show tables" and "describe" commands to examine the schema of the database. What do "show tables" and "describe" return for table "table3"?

Collectives™ on Stack Overflow

Derby Database Table Column Name Format Inconsistent in Query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related