issue with SELECT inside WHERE clause postgres

Question

I'm trying to create a table using these commands:

SELECT
    data_req,
    count(servico) as qtdeServico,
    count(metodo) as qtdeMetodo,
    consumerid,
    metodo,
    status_metodo,
    servico,
    sum(tempo_req)
INTO
    consolidado
FROM 
    requisicoes AS r
WHERE 
    r.data_req > SELECT MAX(c.data_req) FROM consolidado c  
GROUP BY 
    data_req, consumerid, metodo, status_metodo, servico;

Unfortunately I'm getting an error at the select clause inside where. I'm using postgres.

Any suggestions? Thanks in advance.

shouldn't the subquery in the where clause be wrapped in brackets? — user2399035
– user2399035, Commented Sep 11, 2018 at 17:26
The query doesn't make sense. You seem to be trying to create a table called consolidado while also selecting from that same table. Sample data and desired results are really helpful. — Gordon Linoff
– Gordon Linoff, Commented Sep 11, 2018 at 17:27

Evan Carroll · Accepted Answer · 2018-09-11 22:49:39Z

Your query has numerous idiosyncrasies. The following would be more correct syntax:

create table consolidado as 
    select data_req, count(servico) as qtdeServico, count(metodo) as qtdeMetodo,
           consumerid, metodo, status_metodo, servico,
           sum(tempo_req) as sum_tempo_req
    from requisicoes r
    where r.data_req > (select max(c.data_req) from consolidado c) 
    group by data_req, consumerid, metodo, status_metodo, servico;

This fixes the following errors:

Postgres recommends CREATE TABLE AS over SELECT INTO (see here).
All columns should be named when creating a table.
You need parens around the subquery.

However, the query doesn't make sense. It is creating a table called consolidado and also reading from the table. That is logically troublesome. In addition, qtMetodo and qtdeServico will generally have the same values -- they are in the GROUP BY columns meaning that they are separated by groups.

Maor Barazani · Accepted Answer · 2018-09-11 17:27:46Z

0

Try surrounding the SELECT sentence with "(....)":

SELECT
    data_req,
    count(servico) as qtdeServico,
    count(metodo) as qtdeMetodo,
    consumerid,
    metodo,
    status_metodo,
    servico,
    sum(tempo_req)
    into consolidado
    FROM requisicoes as r
    WHERE 
    r.data_req > (select max(c.data_req) from consolidado c  
    GROUP BY data_req, consumerid, metodo, status_metodo, servico);

answered Sep 11, 2018 at 17:27

Maor Barazani

8803 gold badges18 silver badges40 bronze badges

Comments

Aaron Dietz · Accepted Answer · 2018-09-11 17:30:33Z

0

Assuming consolidado already exists.. You will need to use INSERT INTO ()... SELECT syntax. The subquery should be inside parentheses as well:

INSERT INTO consolidado (data_req, qtdeServico.. rest of cols)
SELECT
    data_req,
    count(servico) as qtdeServico,
    count(metodo) as qtdeMetodo,
    consumerid,
    metodo,
    status_metodo,
    servico,
    sum(tempo_req)
FROM requisicoes as r
WHERE r.data_req > (select max(c.data_req) from consolidado c)
GROUP BY data_req, consumerid, metodo, status_metodo, servico;

answered Sep 11, 2018 at 17:30

Aaron Dietz

10.3k1 gold badge17 silver badges27 bronze badges

Comments

Evan Carroll · Accepted Answer · 2018-09-11 22:52:01Z

0

In addition to other's points about CREATE TABLE AS vs SELECT INTO, I would highly suggest you checkout CREATE MATERIALIZED VIEW which sounds like more of what you want..

answered Sep 11, 2018 at 22:52

Evan Carroll

1

Collectives™ on Stack Overflow

issue with SELECT inside WHERE clause postgres

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related