Why Does My PostgreSQL INSERT Statement Fail with 'column \'X\' does not exist'?

PostgreSQL database error: "Column 'X' does not exist"

In the given PostgreSQL code, trying to insert data into the table results in the error:

Error message:

<code>psql:createConfigChangeLog.sql:11: ERROR:  column "5837-2016-08-24_09-12-22" does not exist</code>

Error reason:

This error occurs because PostgreSQL interprets the value of the last_config_version column as a column name rather than a literal string. PostgreSQL requires string constants to be enclosed in single quotes.

Solution:

To solve this problem, the literal string of the last_config_version column should be enclosed in single quotes:

INSERT INTO config_change_log(last_config_version, is_done, change_description )
VALUES('5837-2016-08-24_09-12-22', false, '{ ''key'':''value''}');

Escape of single quotes in data:

Alternatively, single quotes in the data can be escaped by double-writing:

INSERT INTO config_change_log(last_config_version, is_done, change_description )
VALUES("5837-2016-08-24_09-12-22", false, "{ 'key':'value'}");

Please note that the first method uses single quotes and escapes the inner single quotes, which is the more standard and recommended approach. The second method uses double quotes, which, while working in some cases, is not as clear and portable as the first method.

The above is the detailed content of Why Does My PostgreSQL INSERT Statement Fail with 'column \'X\' does not exist'?. For more information, please follow other related articles on the PHP Chinese website!