Re: Is there a bug in psql? (SELECT ''';)

On 5/31/23 00:17, Wen Yi wrote:
> Hi team,
> when I learn the postgres, I try to store the ' into the database,
> but something unexpected happend.
> 
> postgres=# CREATE TABLE test (str varchar);
> CREATE TABLE
> postgres=# INSERT INTO test values (''');
> postgres'# SELECT * FROM test;
> postgres'# exit
> Use control-D to quit.
> postgres'# \q
> Use control-D to quit.
> postgres'#
> \q
> bash-5.1$
> 
> And I do another test
> 
> postgres=# SELECT ''';
> postgres'# exit
> Use control-D to quit.
> postgres'# \q
> Use control-D to quit.
> postgres'#
> \q
> bash-5.1$
> 
> Is there a bug in psql?

See

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

4.1.2.4. Dollar-Quoted String Constants

"While the standard syntax for specifying string constants is usually 
convenient, it can be difficult to understand when the desired string 
contains many single quotes or backslashes, since each of those must be 
doubled. To allow more readable queries in such situations, PostgreSQL 
provides another way, called “dollar quoting”, to write string 
constants.  ... "

Read the entire section for the full story.

As example:

CREATE TABLE test (str varchar);

The hard way:

INSERT INTO test values ('''');
INSERT 0 1

The easier way:

INSERT INTO test values ($$'$$);
INSERT 0 1

select * from test;
  str
-----
  '
  '



> 
> Yours,
> Wen Yi
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com