[FORMATTING] Column named text is converted to upper case

[jgeurts]

Input data

Which SQL and options did you provide as input?

Language: PostgreSQL
Keyword case: Upper

CREATE TABLE foo (text TEXT)

Expected Output

CREATE TABLE foo (text TEXT)

Actual Output

CREATE TABLE foo (TEXT TEXT)

Usage

• How are you calling / using the library? Prettier
• What SQL language(s) does this apply to? postgres
• Which SQL Formatter version are you using? 15.2

[nene]

Thanks for reporting.

This is essentially the same issue as #302. Though something else also goes wrong here. TEXT should be detected as data-type, but is for some reason currently detected as plain keyword.

There's currently no good way to fix this fundamental problem in SQL Formatter. Basically the choices are:

1. detect text as data-type keyword, resulting in CREATE TABLE foo (TEXT TEXT)
2. detect text as identifier, resulting in CREATE TABLE foo (text text)

Though the latter would only happen if you were to use identCase: "lower".

But currently something goes wrong in here with the classification of the word text. Aha... I found the problem. The word text is also classified as a function name. It is first detected as a function name, and then all the function names are checked for a ( after them, and if there is none, they get turned to plain keywords. Should probably just remove text from list of function names.

However, as you're already using Prettier, I suggest you instead give a try to prettier-plugin-sql-cst, which does a much better job at formatting PostgreSQL and is actually using the Prettier layout algorithm, unlike prettier-plugin-sql which is just a thin wrapper around this library which has no connection to Prettier.

[nieomylnieja]

I have the following config:

  "keywordCase": "upper",
  "dataTypeCase": "lower",
  "functionCase": "lower",
  "identifierCase": "lower",

and yet, text[] data type is uppercased, ideas why?

[nene]

Thanks for reporting. Please open another issue if you care about this. I can confirm that there is a problem, but it's not directly related to this closed issue (it affects text[] not the plain text data type).

Though in general the SQL Formatter library is fundamentally not capable of properly distinguishing between keywords, data types, column names, etc. The best it can do is to make a guess. There's a much higher chance of getting this properly implemented in prettier-plugin-sql-cst: nene/prettier-plugin-sql-cst#13