CODEWITHSUNDEEP
postgresqlexpressquotespg-dumppostgresql-copy
Andrew R
Andrew R

Reputation: 161

Single quotes stored in a Postgres database

I've been working on an Express app that has a form designed to hold lines and quotes.

Some of the lines will have single quotes('), but overall it's able to store the info and I'm able to back it up and store it without any problems. Now, when I want do pg_dump and have the database put into an SQL file, the quotes seem to cause some things to appear a bit wonky in my text editor.

Would I have to create a method to change all the single quotation marks into double, or can I leave it as is and be able to upload it back to the database without causing major issues. I know people will continue to enter in lines that contain either single or double quotations, so I would like to know any solution or answer that would help greatly.

Upvotes: 0

Views: 2058

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657867

Single quotes in character data types are no problem at all. You just need to escape them properly in string literals.

To write data with INSERT you need to quote all string literals according to SQL syntax rules. There are tools to do that for you ...

However, pg_dump takes care of escaping automatically. The default mode produces text output to be re-imported with COPY (much faster than INSERT), and single quotes have no special meaning there. And in (non-default) csv mode, the default quote character is double-quote (") and configurable. The manual:

QUOTE

Specifies the quoting character to be used when a data value is quoted. The default is double-quote. This must be a single one-byte character. This option is allowed only when using CSV format.

The format is defined by rules for COPY and not by SQL syntax rules.

Upvotes: 3

Related Questions