Обсуждение: Is there a bug in psql? (SELECT ''';)

On Wed, May 31, 2023 at 03:17:14PM +0800, 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;

In here, in prompt, you can see that it's waiting for you to end your
'-qutoed string.

You seem to expect
INSERT INTO test values (''');
to do something, which it won't as it's not full query.

What do you think this should be doing?

Best regards,

depesz

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