On 12/16/21 13:11, Shaozhong SHI wrote:
When I used SQL identifier, it stopped working. The command line gets
interpreted as following:
insert into stats select "1" as id, 'count of nulls in
"UNIQUE_REFERENCE_NUMBER"' as checks,
count("""UNIQUE_REFERENCE_NUMBER""") from
points_of_interest."pointx_v2_National_Coverage_Sep21" where
"""UNIQUE_REFERENCE_NUMBER""" is null
"""UNIQUE_REFERENCE_NUMBER""" is a tip off that your are quoting the
double quotes when passing the identifier name into the format:
select format('select * from %I', '"UNIQUE_REFERENCE_NUMBER"');
format
---------------------------------------------
select * from """UNIQUE_REFERENCE_NUMBER"""
instead of doing as shown here
(https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT),
which is pass in just a string with no double quotes:
select format('select * from %I', 'UNIQUE_REFERENCE_NUMBER');
format
-----------------------------------------
select * from "UNIQUE_REFERENCE_NUMBER"
This:
'count of nulls in "UNIQUE_REFERENCE_NUMBER"'
is just plain incorrect syntax.
I used select count("UNIQUE_REFERENCE_NUMBER") from a_table where
"UNIQUE_REFERENCE_NUMBER" is null in SQL.
It always worked.
This can not be replicated in Execute Format.
Regards,
David
On Thu, 16 Dec 2021 at 20:24, David G. Johnston
<david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:
On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI
<shishaozhong@xxxxxxxxx <mailto:shishaozhong@xxxxxxxxx>> wrote:
The following command runs but does not produce results as expected.
Execute Format('insert into stats select %L as id, %2$L as
checks, count(%3$s) from %4$s where %5$s is null', i, 'count of
nulls in '||col, col, t_name, col);
All columns have got capital letters in. How to ensure that the
columns are double-quote when they are fed in as variables.
Quoting the relevant doc section:
https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT
<https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT>
type (required)
The type of format conversion to use to produce the format
specifier's output. The following types are supported:
s formats the argument value as a simple string. A null value is
treated as an empty string.
I treats the argument value as an SQL identifier, double-quoting it
if necessary. It is an error for the value to be null (equivalent to
quote_ident).
L quotes the argument value as an SQL literal. A null value is
displayed as the string NULL, without quotes (equivalent to
quote_nullable).
David J.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
