CODEWITHSUNDEEP
mysqlsqldatabasepostgresql
user1632949
user1632949

Reputation: 635

How to avoid double quotes to column name in postgreSQL?

I am new to work with PostgreSQL. I faced the problem while executes the query (column not found) when I migrate the database from MySql to PostgreSQL.

ie, in MySQL I have on the table name provider in that one column name is authType. At the time of migration of database this column was created like "AuthType"(with quotes) in my PostgreSQL because of mixed case letters. When i run the select query like "select authType from provider" it is working fine in MySQL, but it is not working on PostgreSQL.

How to avoid the quotes to the column before execute the query. I have 100 more tables and each table has some columns with mixed case letters.

Upvotes: 3

Views: 7930

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61526

You may mass-convert all columns to lower case after creating tables. This function does it, taking the schema name as input:

CREATE FUNCTION lower_case_columns(_schemaname text) RETURNS void AS
$$
DECLARE
  colname text;
  tablename text;
  sql text;
BEGIN
  FOR tablename,colname in select table_name,column_name FROM information_schema.columns 
    WHERE table_schema=_schemaname AND column_name<>lower(column_name)
  LOOP
    sql:=format('ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
       _schemaname,
       tablename,
       colname,
       lower(colname));
    raise notice '%', sql;
    execute sql;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

So with this table provider("AuthType" int) in the public schema, after running select lower_case_columns('public'), this will become

 provider(authtype int)

and it may be queried with select authType from provider as well as select authtype from provider or any case variant without double quotes.

Upvotes: 6

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

You need to avoid mixed cases (or double quotes) when creating the tables. If it's all lowercase within the table's definition, you can use unquoted mixed case identifiers, and Postgres will silently lowercase them internally.

Upvotes: 4

Related Questions