PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why does dividing one string into 2 lines of strings work with `format()`?

On psql, I could call format() with 'Hello World' as shown below:

postgres=# SELECT format('Hello World');
   format
-------------
 Hello World
(1 row)

Now, dividing 'Hello World' into 'Hello ' and 'World' in one line with one space got the error as shown below:

                              -- ↓ One space
postgres=# SELECT format('Hello ' 'World');
ERROR:  syntax error at or near "'World'"
LINE 1: SELECT format('Hello ' 'World');

But, dividing 'Hello World' into 'Hello ' and 'World' in two lines could call format() without error as shown below:

postgres=# SELECT format('Hello '
postgres(# 'World');
   format
-------------
 Hello World
(1 row)

So, why does dividing one string into 2 lines of strings work with format() as just above?

In addition, putting no space between 'Hello ' and 'World' could call format() without error but it contains ' as shown below:

                             -- ↓↓ No space
postgres=# SELECT format('Hello ''World');
    format
--------------
 Hello 'World
(1 row)

And, putting , between 'Hello ' and 'World' could call format() without error but only Hello is outputted without World as shown below:

postgres=# SELECT format('Hello ', 'World');
 format                       -- ↑ Here
--------
 Hello
(1 row)
Tags:

2

Answers


  1. 0

    There are a few things going on in here:

    • How quoting works
    • How format works

    To start with quotes, a quote can be escaped by using another quote ”. See the manual about the details.

    Format can take multiple inputs and there must be at least one. In your example there is no placeholder for the content, so the content cannot be part of the output. See the manual for more examples.

    Login or Signup to reply.
  2. 0

    format() is only marginally involved in this.
    The chapter "String Constants" in the manual addresses your question exactly:

    Two string constants that are only separated by whitespace with at
    least one newline     are concatenated and effectively treated as if the
    string had been written as one constant. For example:

    SELECT 'foo'
'bar';

    is equivalent to:

    SELECT 'foobar';

    but:

    SELECT 'foo'      'bar';

    is not valid syntax. (This slightly bizarre behavior is specified by
    SQL; PostgreSQL is following the standard.)

    There is even the comment I would have added if the manual did not already: a bizarre artifact from old ideas in the SQL standard.

    The rest is quoting basics. See:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search