Can we create two columns with same name but different case in database?-CodePudding

Can we create two (or more) columns in Postgres/ MySQL with the same name but a different case? As in does case-sensitivity matter in column names?

For e.g., Can a table contain two columns with names - COL and col?

Linked post: can we create two columns with same name in database? only talks about same names, but not about case sensitivity.

CodePudding user response：

In standard SQL, quoted identifiers are case sensitive and Postgres follows that standard.

So the following:

select column_one as "COL",
       column_two as "col"
from ...

Or as part of a table

create table dont_do_this
(
   "COL" integer,
   "col" integer
);

Those are two different names as they become case sensitive due to the use of the double quotes.

But I would strongly advise to not do that. This will most probably create confusion and problems down the line.

I think this should work with MySQL as well, but as it traditionally doesn't care about following the SQL standard, I don't know.

CodePudding user response：

Can we create two (or more) columns in Postgres/ MySQL with the same name but a different case? As in does case-sensitivity matter in column names?

Not possible to create columns with same name - yes case-sensitivity matters.

Example in MySQL:

CREATE TABLE test(
  id int,
  id int
 );
 
 CREATE TABLE test1(
  id int,
  ID int
 );

Output in MySQL:

Schema Error: Error: ER_DUP_FIELDNAME: Duplicate column name 'id'

Output in PostgreSQL:

Schema Error: error: column "id" specified more than once

SELECT statement:

 SELECT 
     id as "id",
     ID1 as "id"
 FROM test;

Output:

id
2

Case-sensitive SELECT:

 SELECT 
     id as "id",
     ID1 as "ID"
 FROM test;

Output:

id  ID
1   2