PostgreSQL Count – How to Keep Count in Temporary Variable for Later Use in Query

countpostgresql

I have used WITH query for this as below 

WITH my_count AS 
(
    SELECT count(emp_name) 
    FROM   employee 
    WHERE  joined_date < '2017-11-30'::Date 
    AND    region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value) 
VALUES ('count des', '2016-11-30', my_count);

But this gives me an error as below:

ERROR: column "my_count" does not exist
LINE 4: …rty_value) VALUES ('count des', '2016-11-30', my_co…
^
SQL state: 42703
Character: 244

So How do we keep count in a temporary variable for later use? If this is not possible, is there any other way to achieve this?

Best Answer

You need to base your insert statement on a select:

WITH my_count (cnt) AS (
  SELECT count(emp_name) 
  FROM employee 
  WHERE joined_date < '2017-11-30'::Date AND region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value) 
select 'count des', '2016-11-30', cnt
from my_count;

Or use a scalar select:

WITH my_count (cnt) AS (
  SELECT count(emp_name) 
  FROM employee 
  WHERE joined_date < '2017-11-30'::Date AND region = 'UK'
)
INSERT INTO my_summary (property_key, month, property_value) 
values
('count des', '2016-11-30', (select cnt from my_count));

Also, both methods could be implemented without a CTE. Taking the first query as an example, the variation without a CTE would look like this:

INSERT INTO my_summary (property_key, month, property_value) 
SELECT 'count des', '2016-11-30', count(emp_name) 
FROM employee 
WHERE joined_date < '2017-11-30'::Date AND region = 'UK';
Related Question