Postgresql9.3 select query showing error

Question

Below DB query is showing below error on postgresql9.3.

SELECT '\'' || t2.name || '\'', '\'' || t1.phone_number || '\'', '\'' || t1.details || '\'', '\'' || t1.description || '\'', '\'' || (CASE WHEN t1.s_id IS NULL THEN 'N/A' ELSE t3.s_type END) || '\'', '\'' || t1.s_id || '\'' FROM abc_tble AS t1 LEFT JOIN pqrtable AS t2 ON t1.s_id = nid LEFT JOIN te AS t3 ON t1.s_id = t3.s_id;

Invalid command \''. Try \? for help.

But Above query is working fine in postgresql8.3.

user330315 · Accepted Answer · 2015-06-18 06:03:28Z

1

But above query is working fine in Postgres 8.3.

The SQL standard defines two single quotes to escape one inside a literal: ''''

Postgres 8.3 defaulted to a non-standard behavior where it was allowed to escape a single quote using a backslash: '\''

This deviation from the SQL standard was always discouraged and can be controlled through the configuration parameter standard_conforming_strings

With version 9.1 the default for this parameter was changed from off to on. Version 8.1 and later would emit a warning when you used the non-standard way of escaping single quotes (unless you explicitly turned that off)

answered Jun 18, 2015 at 6:03

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

yogesh Over a year ago

Yes, problem has resolved after changing ''''. Thanks for your support. How to change standard_conforming_strings value of postgresql.conf? I have checked but this option is not found in postgresql.conf.

user330315 Over a year ago

@yogesh. In a default generated postgresql.conf that setting is there somewhere near the end of the file. But I would not recommend changing that. Use standard compliant SQL instead.

Collectives™ on Stack Overflow

Postgresql9.3 select query showing error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related