CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionpostgresql-9.2postgresql-9.0
nfisher
nfisher

Reputation: 57

Error using INSERT and WITH statements together

PostgreSQL: v9.2 on a Windows machine.

UPDATE: the version of postgres on the server is 9.0, the version on my machine is 9.2.1.

I am trying to use the following query to update a table I have created. 

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
);

When the I run the query in pgAdmin, the data is returned as one would expect. However, when I un-comment the --INSERT INTO nei_data_by_county I get the following error: 

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

I know from the documentation that you are allowed to use the WITH statement and the INSERT statement together, but I cannot get this query to function properly.

Has someone else run into this problem?

Upvotes: 1

Views: 169

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

Data-modifying CTEs were only introduced with PostgreSQL 9.1.
Your incorrect information (version 9.2) had everybody fooled, including yourself.

The syntax is just not possible with 9.0.

But the solution is simple: use subqueries instead:

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

Upvotes: 2

Related Questions