Postgresql syntax error using keys

Question

I’m having trouble understanding why pgadmin is pointing syntax error in my query.

select 
    first_name,
    coalesce(to_char(avg(p.amount),'99.99'), 'nnn') valor_medio
from
{
select 
    c.first_name as nome_cliente,
    c.last_name as sobrenome_cliente,
    to_char(avg(p.amount),'99.99') as media_valor, 
    s.first_name as nome_funcionario,
    s.last_name as sobrenome_funcionario
from 
    payment p 
    left join customer c on c.customer_id = p.customer_id
    left join staff s on s.staff_id = p.staff_id 
} as 
    tmp
    left join staff s on c.staff_id = p.staff_id
    where media_valor > 1

This query is still in formulation, but here it is already giving error. In the error message comes the following:

ERROR:  syntax error at or near "{"
LINE 4: {
        ^
********** Error **********

ERROR: syntax error at or near "{"
SQL state: 42601
Character: 21

I’ve checked the line several times, but I still can’t identify what’s wrong. I asked a question very similar to this, but she did not present such a mistake. I wanted to know what the mistake is and why it is being pointed out.

Answer 1

SQL has no keys in its syntax. Use parentheses:

select 
    first_name,
    coalesce(to_char(avg(p.amount),'99.99'), 'nnn') valor_medio
from
(
select 
    c.first_name as nome_cliente,
    c.last_name as sobrenome_cliente,
    to_char(avg(p.amount),'99.99') as media_valor, 
    s.first_name as nome_funcionario,
    s.last_name as sobrenome_funcionario
from 
    payment p 
    left join customer c on c.customer_id = p.customer_id
    left join staff s on s.staff_id = p.staff_id 
) as 
    tmp
    left join staff s on c.staff_id = p.staff_id
    where media_valor > 1

I put in the Github for future reference.