CASE WHEN不在shell命令中工作

I am facing a strange issue while firing an SQL with CASE WHEN statements from within shell.

我在shell中使用CASE WHEN语句触发SQL时遇到了一个奇怪的问题。

My SQL looks like:

我的SQL看起来像:

insert into myschema.myTable (col1, col2, col3) 
select col1, 
         CASE 
            WHEN col2 = 'NULL' THEN NULL 
            ELSE col2 
         END, 
         col3 
from myschema.myTable_ext_table

(It is loading data from an external table that has some NULL values extracted as 'NULL' strings, and we cannot change the external table design)

(它从外部表加载数据,其中一些NULL值被提取为'NULL'字符串,我们无法更改外部表设计)

My objective is to run psql with nohup so that big data loading tasks can run in the background. My psql command looks like this:

我的目标是使用nohup运行psql,以便大数据加载任务可以在后台运行。我的psql命令如下所示:

nohup  sh -c 'date;psql -h myHAWQHost -d myHAWQDB --command "insert into myschema.myTable (col1, col2, col3) select col1, CASE WHEN col2 = 'NULL' THEN NULL ELSE col2 END, col3 from myschema.myTable_ext_table";date;' > myLog.out &

When I fire the SQL from any client (pgAdmin/Aginity) it works well. However when running from the psql command, it does not work, as the CASE WHEN is still putting 'NULL' strings in col2 column.

当我从任何客户端(pgAdmin / Aginity)触发SQL时,它运行良好。但是当从psql命令运行时,它不起作用,因为CASE WHEN仍然在col2列中放置'NULL'字符串。

2 个解决方案

#1

You have a quoting problem. In order to embed a single quote (') into a shell string which is also surrounded in single quotes, you need to switch to a double quoted string:

你有引用问题。为了将单引号(')嵌入到也用单引号括起来的shell字符串中,您需要切换到双引号字符串:

change:

cmd 'foo "bar 'baz' quux"'
#             ^   ^

to:

cmd 'foo "bar '"'"'baz'"'"' quux"'
#             ^^^^^   ^^^^^

You can use other escape styles, but this one is portable across all posix shells.

您可以使用其他转义样式,但是这个样式可以在所有posix shell中移植。

#2

In PostgreSQL you can use a own string limiters:

在PostgreSQL中,您可以使用自己的字符串限制器:

postgres=# select $$AHOJ$$;
 ?column? 
----------
 AHOJ
(1 row)

These literal separators can use from shel

这些文字分隔符可以使用shel

[pavel@localhost ~]$ sh -c 'psql postgres -c "select \$\$NULL\$\$"'
 ?column? 
----------
 NULL
(1 row)

[pavel@localhost ~]$ sh -c 'psql postgres -c "select \$\$$USER\$\$"'
 ?column? 
----------
 pavel
(1 row)

#1