在參數化查詢中傳遞數據類型間隔的值

上下文正在從休息伺服器連接到 Postgres 數據庫。

考慮一個假設的代表性範例：我希望能夠獲得一個名稱列表，其中帳戶創建日期比任意值早/新。

在下面的範例查詢中，表結構很簡單 -name是 type text，並且creation_date是 type timestamp。所以當我做類似的事情時

server_pg_module:query("select name from new_table where 
current_timestamp - creation_date < '6 days'") 

它工作得很好。但我真正想做的是6 days從伺服器獲取該值。所以我嘗試類似

server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]

它拋出一個錯誤。我試過了'6 days'，"'6 days'"還有其他一些混合物，都拋出錯誤。所以要檢查我添加了一個新interval的類型列interval並嘗試了一個類似的查詢

server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]). 

哪個有效，但是

server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]). 

休息。為了更好地衡量，除了"'3 days'"上面提到的混合物之外，我還嘗試$2::interval了（我不確定這是否合法），但它不起作用。

因此，我相信它可能與在參數查詢中表達間隔有關，或者與我正在使用的模組有關。任何關於導致麻煩的原因以及如何做這類事情的想法都將不勝感激。或者可以縮小範圍，問題不在於 pg 而在於模組，然後我必須在其他地方解決它。

Postgres 版本：10.x

我正在使用的模組是 pgo（用於 Erlang 程式語言）https://github.com/SpaceTime-IoT/pgo。我得到的錯誤消息（當我傳遞"2 days"或"'2 days'"作為查詢參數時）看起來像：

{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
                         line => <<"575">>,
                         message => <<"insufficient data left in message">>,
                         routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
                         {unknown,86} => <<"ERROR">>}}}

當我'2 days'作為參數傳遞時，它會引發badarg錯誤。

TLDR：跳到下面的“高級查詢”一章。

您沒有透露您正在使用的模組，但問題顯然是類型轉換之一。看起來您的參數是作為類型值傳遞的，我假設textor varchar。text->沒有隱式類型轉換interval：

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'interval'::regtype;

鑄造資源 | 投射目標 | 演員表
:--------------------- | :--------- | :----------
沒有時區的時間| 間隔 | 一世 
間隔 | 間隔 | 一世 

db<>在這裡擺弄

如果我的假設是正確的，您應該會看到如下錯誤消息：

ERROR:  operator does not exist: interval < text

我非常有信心您未公開的模組可以傳遞不同的數據類型或無類型的字元串文字。Postgres 確實提供了這個功能。

你還說：

我也試過$2::interval

這很奇怪，因為顯式類型轉換也應該起作用。

展示

-- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works

| ？柱子？|
| :------- |
| f |

--untyped string literal
SELECT current_timestamp - timestamp '2018-05-04 18:40' < '6 days'; -- works

| ？柱子？|
| :------- |
| f |

-- text typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < text '6 days'; -- fails!

錯誤：運算符不存在：區間 < 文本

第 2 行： … current_timestamp - 時間戳 ‘2018-05-04 18:40’ < text ‘6 … ^ 提示：沒有運算符與給定的名稱和參數類型匹配。您可能需要添加顯式類型轉換。

-- text typed value, with explicit cast
SELECT current_timestamp - timestamp '2018-05-04 18:40' < ('6 days'::text::interval); -- works

| ？柱子？|
| :------- |
| f |

準備好的語句也是如此：

PREPARE fooplan_typed_interval(interval) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_interval('6 days');

| ？柱子？|
| :------- |
| f |

PREPARE fooplan_untyped AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_untyped('6 days');

| ？柱子？|
| :------- |
| f |

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_text('6 days');  -- only this one fails!

錯誤：運算符不存在：區間 < 文本

第 3 行：…選擇 current_timestamp - 時間戳 ‘2018-05-04 18:40’ < $1; ^ 提示：沒有運算符匹配給定的名稱和參數類型。您可能需要添加顯式類型轉換。

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1::interval;

EXECUTE fooplan_typed_text('6 days');

| ？柱子？|
| :------- |
| f |

db<>在這裡擺弄

高級查詢

除了所有這些，您的查詢都不能使用索引（不是“sargable”）。 改用這樣的東西！

server_pg_module:query("select name from new_table
where creation_date > localtimestamp - interval '1 day' * $1", [6])

您可以乘以intervalwith integer。

或者，如果您發現傳遞類型參數的問題：

server_pg_module:query("select name from new_table
where creation_date > localtimestamp - $1::interval", ['6 days'])

用來localtimestamp指出“目前時間”取決於您目前的時區設置，類型為timestamp. 細節：

• 如何獲取 PostgreSQL 中 2 個時間戳之間的天數差異
• 在 Rails 和 PostgreSQL 中完全忽略時區

引用自：https://dba.stackexchange.com/questions/208580