"TRUNCATE"或接近 的语法错误 - Aurora Postgres 10.12

Question

我正在尝试重构一个查询并删除一组窗口函数，以按原样捕获所有记录。当我将查询粘贴到中以替换我的函数时，pgAdmin在'Truncate'语句附近指示语法错误。我只是搞不清它是什么，很可能是显而易见的，但我会欣赏另一双(或多双(眼睛。

我上的是研究生10.12。

这是我试图插入的代码(最后在它后面包含了"before"代码。任何建议都将不胜感激。已经尝试了括号、逗号和分号的所有类型和组合，并在智慧结束时…

我试图插入的代码

预期结果：从暂存中提取行并插入目标中。完成时截断暂存

WITH ins_agent AS (
INSERT INTO agents (
currentagentsnapshot,
previousagentsnapshot,
agentarn,
eventid,
eventtimestamp,
eventtype,
instancearn)
SELECT 
agent_stg.currentagentsnapshot::jsonb AS currentagentsnapshot,
agent_stg.previousagentsnapshot::jsonb AS previousagentsnapshot,
agent_stg.agentarn::text AS agentarn,
agent_stg.eventid::text AS eventid,
agent_stg.eventtimestamp::timestamptz AS eventtimestamp,
agent_stg.eventtype::text AS eventtype,
agent_stg.instancearn::text AS instancearn
FROM agents_staging AS agent_stg
WHERE agent_stg.agentarn IS NOT NULL)
TRUNCATE agents_staging;

我正在尝试更改代码

预期结果：选择不同的代理并捕获其最近的快照。将agent_id插入另一个表中。解释追加订单。

更改原因：除了在数百万条记录上运行查询之外，它几乎没有什么价值，因为它会因为不同而丢弃除最新代理快照之外的所有代理快照。不是我们想要的。

WITH ins_agent AS (INSERT INTO agents (currentagentsnapshot,
previousagentsnapshot,
agentarn,
eventid,
eventtimestamp,
eventtype,
instancearn)
SELECT DISTINCT LAST_VALUE(currentagentsnapshot::jsonb)
OVER (PARTITION BY agentarn ORDER BY lat.eventtimestamp DESC ) AS previousagentsnapshot,
LAST_VALUE(previousagentsnapshot::jsonb)
OVER (PARTITION BY agentarn ORDER BY lat.eventtimestamp DESC ) AS previousagentsnapshot,
agentarn,
LAST_VALUE(eventid)
OVER (PARTITION BY agentarn ORDER BY lat.eventtimestamp DESC)  AS eventid,
LAST_VALUE(lat.eventtimestamp)
OVER (PARTITION BY agentarn ORDER BY lat.eventtimestamp DESC)  AS eventtimestamp,
LAST_VALUE(eventtype)
OVER (PARTITION BY agentarn ORDER BY lat.eventtimestamp DESC)  AS eventtype,
LAST_VALUE(instancearn)
OVER (PARTITION BY agentarn ORDER BY lat.eventtimestamp DESC)  AS instancearn
FROM agents_staging AS agent
CROSS JOIN LATERAL (SELECT agent.eventtimestamp::timestamp AS eventtimestamp) AS lat
WHERE NOT (
agent.agentarn IS NULL
OR agent.currentagentsnapshot IS NULL
OR agent.currentagentsnapshot = '{}')
ON CONFLICT (agentarn)
DO NOTHING
RETURNING agentarn, agent_id)
UPDATE contact_trace_records
SET agent_id=ins_agent.agent_id
FROM ins_agent
WHERE contact_trace_records.agent_id IS NULL
AND ins_agent.agentarn = contact_trace_records.agent ->> 'ARN';
TRUNCATE agents_staging;

Answer 1

语法错误是CTE不能附加到TRUNCATE命令。仅限于INSERT、UPDATE、DELETE或SELECT。(从技术上讲，也是TABLE，但这只是SELECT * FROM的简短语法。(

删除WITH子句，然后运行两个单独的命令。

INSERT INTO agents (
currentagentsnapshot,
previousagentsnapshot,
agentarn,
eventid,
eventtimestamp,
eventtype,
instancearn)
SELECT 
agent_stg.currentagentsnapshot::jsonb AS currentagentsnapshot,
agent_stg.previousagentsnapshot::jsonb AS previousagentsnapshot,
agent_stg.agentarn::text AS agentarn,
agent_stg.eventid::text AS eventid,
agent_stg.eventtimestamp::timestamptz AS eventtimestamp,
agent_stg.eventtype::text AS eventtype,
agent_stg.instancearn::text AS instancearn
FROM agents_staging AS agent_stg
WHERE agent_stg.agentarn IS NOT NULL;
TRUNCATE agents_staging;

如果您需要抵御竞争条件，请将整个操作封装在事务中，并首先对表进行写锁定：

BEGIN;
LOCK TABLE agents_staging;
INSERT ...;
TRUNCATE agents_staging;
COMMIT;

旁白：外部SELECT中的表别名在INSERT命令中不执行任何操作。这些充其量只是文件。但由于所有列都已经有了最终名称，所以这实际上只是噪音。

如果源列具有兼容的数据类型(对目标类型进行隐式或赋值强制转换，而不是显式强制转换(，则可能不需要表限定或显式强制类型转换。看起来两个表一开始都有相同的数据类型？然后你可以在很大程度上简化为：

INSERT INTO agents
(currentagentsnapshot, previousagentsnapshot, agentarn, eventid, eventtimestamp, eventtype, instancearn)
SELECT currentagentsnapshot, previousagentsnapshot, agentarn, eventid, eventtimestamp, eventtype, instancearn
FROM   agents_staging
WHERE  agentarn IS NOT NULL;