Is there a bug in psql? (SELECT ''';)
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?
Yours,
Wen Yi
You have to mask the single quote:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
Von: Wen Yi <896634148@qq.com>
Gesendet: Mittwoch, 31. Mai 2023 09:17
An: pgsql-general <pgsql-general@lists.postgresql.org>
Betreff: Is there a bug in psql? (SELECT ''';)
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?
Yours,
Wen Yi
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