Question 21760 in Mysql Question posted in
The official MySQL documentation can be found here.

Can we create two columns with same name but different case in database? – Mysql

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search