What Exactly Do Quotation Marks Around the Table Name Do

What exactly do quotation marks around the table name do?

Putting double-quotes around an identifier in Oracle causes Oracle to treat the identifier as case sensitive rather than using the default of case-insensitivity. If you create a table (or a column) with double-quotes around the name, you must always refer to the identifier with double quotes and by correctly specifying the case (with the exception of all upper case identifiers, where double-quotes are meaningless).

Under the covers, Oracle is always doing case-sensitive identifier matching. But it always casts identifiers that are not double-quoted to upper case before doing the matching. If you put double-quotes around an identifier, Oracle skips the casting to upper case.

So if you do something like

CREATE TABLE my_table( 
  col1 number,
  col2 number
)

you can

SELECT * FROM my_table
SELECT * FROM MY_TABLE
SELECT * FROM My_Table
SELECT * FROM "MY_TABLE"

but something like

SELECT * FROM "my_table" 

will fail.

On the other hand, if you do something like

CREATE TABLE "my_other_table"( 
  col1 number,
  col2 number
)

you cannot do

SELECT * FROM my_other_table
SELECT * FROM MY_OTHER_TABLE
SELECT * FROM My_Other_Table
SELECT * FROM "MY_OTHER_TABLE"

but this

SELECT * FROM "my_other_table" 

will work

Oracle DB quote column names

It doesn't have to do with objects or tables, it has to do with how these objects/tables have been created.

If you do
create table "blabla" then you always need to address this table with "blabla", if you do create table blabla then you can address this table via BLABLA or blabla or bLabLa. Using " " makes the name case sensitive and that is the reason why most developers don't use " " because usually you don't want case sensitive names .

When do Postgres column or table names need quotes and when don't they?

Thanks to @TimBiegeleisen's comment, I was able to pinpoint the problem; I used a reserved keyword ("user") as a column name.

Link to reserved keywords in the doc: https://www.postgresql.org/docs/current/sql-keywords-appendix.html.

Now I know not to use quotes to query column names, but rather to avoid reserved keywords as column names.

Unquoted table names don't work

It requires double quote names only in case the name you are using is a keyword (and your example - table is in this situation) or if you want to include special characters or non-alphanumeric characters (e.g. "Multi word table name").

The names enclosed in " are case sensitive.

How do I use quoted identifier for user + table name combination in Oracle?

If you have created the table using quoted identifier, then you must always use double-quotation marks wherever you refer the object.

From documentation,

Database Object Naming Rules

Every database object has a name. In a SQL statement, you represent
the name of an object with a quoted identifier or a nonquoted
identifier.

• A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you
  must use the double quotation marks whenever you refer to that object.

• A nonquoted identifier is not surrounded by any punctuation.

For example,

SQL> CREATE TABLE "USER"(A NUMBER);

Table created.

SQL>
SQL> SELECT COUNT(*) FROM LALIT.USER;
SELECT COUNT(*) FROM LALIT.USER
                           *
ERROR at line 1:
ORA-00903: invalid table name


SQL>
SQL> SELECT COUNT(*) FROM LALIT."USER";

  COUNT(*)
----------
         0

SQL>

So, you need to refer the table as a quoted identifier:

SELECT COUNT(*) FROM SYS0MYUSER."USER";

Update OP updated his question regarding table alias.

What's about table alias do I have to use double quotes too ?

Table alias has nothing to do with the quoted identifier.

For example,

SQL> SELECT t.* FROM LALIT."USER" t;

no rows selected

SQL>

Python: Why are the column names in PostgreSQL table wrapped in double quotes?

You do need to double-quote the "LoanAgreementID" column name. Your CREATE TABLE statement probably did (especially if you generated your schema with a framework), so in that case you have a LoanAgreementID column as you expect. However, unquoted names in queries are automatically lowercased so you are actually searching for a column named loanagreementid. Postgres is case-sensitive about object names unless they're quoted, so it can't find it.

Surround the LoanAgreementID and LoanAgreements names in your query with double-quotes and they should work. Or you could change those names to snake_case in the database (more idiomatic for Postgres, potentially less so elsewhere); it's up to you.

Omitting the double quote to do query on PostgreSQL

Your problem with this query started when you created your table. When you create your table, don't use quotes.

Use this:

CREATE TABLE a ( ... );

Not this:

CREATE TABLE "A" ( ... );

The latter will make it so that you always have to quote it later. The former makes it a normal name and you can use SELECT * FROM a; or SELECT * FROM A;

If you can't just recreate your table, use the ALTER TABLE syntax:

ALTER TABLE "A" RENAME TO a;

Are quotes around tables and columns in a MySQL query really necessary?

What if you have a table named table, or a column named where. These are reserved keywords. If you used those in your queries without backticks, they'd produce an invalid query (Of course, using reserved keywords is bad practice).

SELECT something FROM table WHERE where = 1;

vs.

SELECT something FROM `table` WHERE `where` = 1;

---

Related Topics