sql - 一起使用 INSERT 和 WITH 语句时出错

Question

PostgreSQL:Windows 机器上的 v9.2。

更新:服务器上的postgres版本是9.0，我机器上的版本是9.2.1。

我正在尝试使用以下查询来更新我创建的表。

WITH
    nei_nox AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%nitrogen%' GROUP BY fips),
    nei_sox AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%sulfur%' GROUP BY fips),
    nei_co AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%monoxide%' GROUP BY fips),
    nei_voc AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%PM10%' GROUP BY fips),
    nei_nh3 AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%PM2.5%' GROUP BY fips),
    nei_pm10 AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%volatile%' GROUP BY fips),
    nei_pm25 AS (SELECT fips, sum(total_emissions) AS x FROM nei_data WHERE 
        pollutant_name ILIKE '%ammonia%' GROUP BY fips)

-- INSERT INTO nei_data_by_county
(
    SELECT dat.fips, nei_nox.x as "nox",
         nei_sox.x as "sox",
         nei_co.x as "co",
         nei_voc.x as "voc",
         nei_nh3.x as "nh3",
         nei_pm10.x as "pm10",
         nei_pm25.x as "pm25"
    FROM bts2dat_55.cg_data dat
    LEFT OUTER JOIN nei_nox ON dat.fips = nei_nox.fips
    LEFT OUTER JOIN nei_sox ON dat.fips = nei_sox.fips
    LEFT OUTER JOIN nei_co ON dat.fips = nei_co.fips
    LEFT OUTER JOIN nei_voc ON dat.fips = nei_voc.fips
    LEFT OUTER JOIN nei_nh3 ON dat.fips = nei_nh3.fips
    LEFT OUTER JOIN nei_pm10 ON dat.fips = nei_pm10.fips
    LEFT OUTER JOIN nei_pm25 ON dat.fips = nei_pm25.fips
);

当我在 pgAdmin 中运行查询时，数据按预期返回。但是，当我取消注释 --INSERT INTO nei_data_by_county 时，出现以下错误:

ERROR:  syntax error at or near "INSERT"
LINE 33: INSERT INTO nei_data_by_county
         ^

我从the documentation知道您可以同时使用 WITH 语句和 INSERT 语句，但我无法使此查询正常运行。

有其他人遇到过这个问题吗？

Answer 1

Data-modifying CTEs仅在 PostgreSQL 9.1 中引入。
您的不正确信息(版本 9.2)让所有人都上当了，包括您自己。

语法在 9.0 中是不可能的。

但解决方案很简单:改用子查询:

INSERT INTO nei_data_by_county (<<provide column list!!>>)
SELECT dat.fips
      ,nei_nox.x   -- as "nox" -- column alias is only for you documentation
       -- more columns
FROM   bts2dat_55.cg_data dat
LEFT   OUTER JOIN (
   SELECT fips, sum(total_emissions) AS x
   FROM   nei_data
   WHERE  pollutant_name ILIKE '%nitrogen%'
   GROUP  BY fips
   ) nei_nox USING (fips)
LEFT OUTER JOIN ... -- more subqueries