The problem of case sensitivity based on PostgreSql alias

PostgreSql is case sensitive

If the size of the alias is inconsistent, an error will be prompted:

SELECT
 *
FROM
 (
  SELECT
   cpi."product_item_id" "PRODUCT_ITEM_ID"
  FROM
   prd.up_product_item cpi ) a
WHERE
 a.PRODUCT_ITEM_ID=1

A very simple subquery, but an error occurs. Although the case is consistent from the statement, lower case is still used in the internal query. PostgreSql needs to be double quoted for uppercase, otherwise it will still be considered lowercase.

The correct way to write it is as follows

SELECT
 *
FROM
 (
  SELECT
   cpi."product_item_id" "PRODUCT_ITEM_ID"
  FROM
   prd.up_product_item cpi ) a
WHERE
 a."PRODUCT_ITEM_ID"=1

Supplementary: If the alias of the postgresql query field is capitalized, double quotes should be added

PostgreSQL is case sensitive to table names and field names.

It can be created normally in the graphical interface. When using SQL statements, you need to add double quotes. If you are querying jdbc, remember to use escape symbols.

PostgreSQL is case-insensitive in SQL statements.

select ID from t_user and select id from t_user

The id field will be queried from the t_user table. If you want to query the fields of capital letters, you also need to add double quotes: select "ID" from t_user

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.