[Postgres]: AST is incorrect for double quoted columns in columns and where clause

[tbantle22]

node-sql-parser v4.17.0 Updated: for v5.0.0+

In v4.17.0 the columns were correct, just not the where block columns

The result of astify on a postgres sql statement with double quoted columns in the columns and where clause should be the same as if the column is not double quoted.

For example, the results of these should be equal, but they are not:

const query = `SELECT "col1" FROM "tablename" WHERE "col1" = 'value'`
parser.astify(, { database: "POSTGRESQL" });

Result:

{
      "with": null,
      "type": "select",
      "options": null,
      "distinct": {
        "type": null
      },
      "columns": [
        {
          "type": "expr",
          "expr": {
            "type": "column_ref",
            "table": null,
            "column": {
              "expr": {
                "type": "double_quote_string", // HERE
                "value": "col1"
              }
            }
          },
          "as": null
        }
      ],
      "into": {
        "position": null
      },
      "from": [
        {
          "db": null,
          "table": "tablename",
          "as": null
        }
      ],
      "where": {
        "type": "binary_expr",
        "operator": "=",
        "left": {
          "type": "column_ref",
          "table": null,
          "column": {
            "expr": {
              "type": "double_quote_string", // HERE
              "value": "col1"
            }
          }
        },
        "right": {
          "type": "single_quote_string",
          "value": "value"
        }
      },
      "groupby": null,
      "having": null,
      "orderby": null,
      "limit": {
        "seperator": "",
        "value": []
      },
      "window": null
    }

Should be the same as:

const query = `SELECT col1 FROM "tablename" WHERE col1 = 'value'`
parser.astify(, { database: "POSTGRESQL" });

Result

{
      "with": null,
      "type": "select",
      "options": null,
      "distinct": {
        "type": null
      },
      "columns": [
        {
          "type": "expr",
          "expr": {
            "type": "column_ref", // CORRECT
            "table": null,
            "column": {                 // Why is this not just "column": "col1" anymore though?
              "expr": {
                "type": "default",
                "value": "col1"
              }
            }
          },
          "as": null
        }
      ],
      "into": {
        "position": null
      },
      "from": [
        {
          "db": null,
          "table": "tablename",
          "as": null
        }
      ],
      "where": {
        "type": "binary_expr",
        "operator": "=",
        "left": {
          "type": "column_ref", // CORRECT
          "table": null,
          "column": {
            "expr": {
              "type": "default",
              "value": "col1"
            }
          }
        },
        "right": {
          "type": "single_quote_string",
          "value": "value"
        }
      },
      "groupby": null,
      "having": null,
      "orderby": null,
      "limit": {
        "seperator": "",
        "value": []
      },
      "window": null
    }

[tbantle22]

Also related in v5.0.0+

sqlifying a postgres query does not escape column names in where clauses with double quotes like it used to.

 Expected: "SELECT \"name\", \"age\" FROM \"tablename\" WHERE \"id\" = 1"
 Received: "SELECT \"name\", \"age\" FROM \"tablename\" WHERE id = 1"

[taozhi8833998]

1. To keep the original quoted type of column, I changed the simple column string to {type, value}, related to #1607
2. sqlify a postgres query does not escape column names in order to keep the original the quoted of column, if the column quoted, then we quoted it, if not then we not.

[taozhi8833998]

@tbantle22 Do you need keep column quoted always, while ignore the original?

[tbantle22]

Ok thanks for explaining I understand this change better now. This change broke some of our existing structures but I will try to update them to the new structure for columns