PostgreSQL syntax error using keys

2

I'm having trouble understanding why pgadmin is pointing to a 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 the 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 have looked several times in the line, but I still can not identify what is wrong. I asked a question very much like this, but she made no such mistake. I wanted to know what the error was and why it was being pointed out.

    
asked by anonymous 22.12.2015 / 14:35

1 answer

4

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
    
22.12.2015 / 14:36