I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.
During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.
If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off. However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').
Only the server machine has postgres installed, so it is the only machine with a postgresql.conf file where the standard_conforming_strings option can be turned on or off. I know this option is being disabled correctly on the server machine, but for some reason, when data is saved on the client machine, backslashes are not saved correctly, even though the data is being saved to the same database.
If both these computers are saving to the same database, they should refer to the same postgresql.conf file, shouldn't they? Data should be stored in the same manner on both machines. What is going wrong here?
Any help or suggestions would be greatly appreciated!
Edit: As requested, I've enabled logging of statements on postgres and this is what showed up in the log file when I tried to save data containing backslashes:
2014-06-02 13:44:01 PDT LOG: statement: SELECT * FROM tblapp; 2014-06-02 13:44:38 PDT WARNING: nonstandard use of escape in a string literal at character 135 2014-06-02 13:44:38 PDT HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. 2014-06-02 13:44:38 PDT LOG: statement: BEGIN;DELETE FROM tblapp WHERE app_key='ClientApp_ArchivePath';INSERT INTO tblapp (app_key,app_value) VALUES ('ClientApp_ArchivePath','C:\134Users\134Stephane\134Documents\134Archive'); 2014-06-02 13:44:38 PDT LOG: statement: COMMIT
The data shows up like this in pgAdmin:
ClientApp_ArchivePath | C:\Users\Stephane\Documents\Archive
Note: I did this on a machine that is saving backslashes correctly. I do not currently have access to a machine that is saving them incorrectly, but hopefully I will soon and I will go through the same process and post the results here.