Search code examples
sqlpostgresqlselectsyntax-error

How to declare and use a variable in PostgreSQL?

I'm new to PostgreSQL, so maybe my question is unconvinced. But what I want is to put the result of one query into a variable and then used it again in another query. I run my query inside pgAdmin4, Below is my query:

Also I tried those solution1, solution2 without achieving my goal.

WITH vars AS (
    (select count(*) from employee) AS vars_id
)
select 
    *
from 
    employee
where
    id=vars.vars_id;

The error is:

ERROR:  syntax error at or near "AS"
LINE 2:  (select count(*) from employee) AS vars_id
                                         ^
SQL state: 42601
Character: 49
Solution

  • The result of a CTE is a table expression. You can't just refer to it as a scalar, you need to query from it:

    WITH vars AS (
    SELECT COUNT(*) AS vars_id FROM employee
)
SELECT *
FROM   employee e
JOIN   vars ON e.id = vars.vars_id