sqlpostgresqlquoted-identifier

Postgres solving a syntax error caused by a dash (-)

I am trying to query from a database called physionet-data.mimiciii_clinical.diagnoses_icd

PostgresSQL returns the following error message:

quan.sql:273: ERROR:  syntax error at or near "`"
LINE 132: from `physionet-data.mimiciii_clinical.diagnoses_icd` icd

I think it is caused by the dash. If I change the `for ' the same error comes up

quan.sql:273: ERROR:  syntax error at or near
"'physionet-data.mimiciii_clinical.diagnoses_icd'"

Any clue on how to fix that?

Solution

  • You would need to quote that schema name, using double-quotes:

    select ...
from "physionet-data".mimiciii_clinical.diagnoses_icd

    Note that quoting an identifier makes it case-sensitive. You would need to ensure that the character case the schema was created with matches the one you are using here.

    Using identifiers that require quoting is not a good idea in general; as you are fiding out, this requires quoting them every where you use it later on. If that's not too late, I would recommend changing the schema name to a name that does not require quoting.