CODEWITHSUNDEEP
c#postgresqlnpgsql
jealtw
jealtw

Reputation: 47

PostgreSQL: How to insert only one backslash into PostgreSQL database

Does anyone know how to store a single backslash into PostgreSQL Database?

I am using C# and Nqgsql to access PostgreSQL, and my case is I want to store "1\\0\\0\\0\\1\\0" into the database, and the expected string in DB field will be "1\0\0\0\1\0", that is I only need one backslash in the db field, thus when I get the data from db, it will still be "1\\0\\0\\0\\1\\0" in memory. But my problem is when the memory string is "1\\0\\0\\0\\1\\0", the string stored into db field is also "1\\0\\0\\0\\1\\0", then when I get the data from db, the memory string will be "1\\\\0\\\\0\\\\0\\\\1\\\\0".

The variables I used in c# code is set as the following format: var a = "1\\0\\0\\0\\1\\0"; var b = @"1\0\0\0\1\0";

when store into db, it seems that the backslashes in both variables have been doubled. How to deal with this issue?

Upvotes: 1

Views: 3954

Answers (2)

Gurduloo
Gurduloo

Reputation: 151

I had this problem as well. I was able to solve it by going in the database's config and changing "standard_conforming_strings" to OFF.

Upvotes: 1

Tometzky
Tometzky

Reputation: 23890

You should avoid this entirely by using parametrized queries. Consult an example in Npgsql: User's Manual in Using parameters in a query section.

But if you really want to construct a literal query then you can use E'' syntax, like this:

var sql = @"insert into table_name (column_name) values (E'1\\0\\0\\0\\1\\0')";

This syntax is independent of server or connection configuration like standard_conforming_strings. But it is Postgres specific.

If you want your code be portable between different database engines the you can issue set standard_conforming_strings=on just after connecting. Then this works: 

var sql = @"insert into table_name (column_name) values ('1\0\0\0\1\0')";

This option is turned on by default since PostgreSQL 9.1 and available since 8.2.

Upvotes: 2

Related Questions