Início / dba / Perguntas / 123871
Accepted
Ivan Voras
Asked: 2015-12-17 12:59:39 +0800 CST

O PostgreSQL requer "GROUP BY" ao criar um alias para uma tabela?

  • 772

Aqui está algo estranho: há uma grande tabela chamada "products" que é particionada com herança de forma que haja dois filhos: products_active e products_inactive, e a restrição é que em products_active, status=1, e products_inactive obtenham todos os outros status.

Há uma grande consulta que une várias tabelas, das quais esta é a primeira parte:

SELECT
  products.id, products.status, products.brand_id, products.name, products.description, products.data, products.website,
  products.packaging, products.container, products.country_of_origin, products.category_id, products.product_type_id, products.tsv_keywords,
  COUNT(prices.id) as prices_count,
  ROUND(AVG(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as avg_price,
  ROUND(MAX(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as high_price,
  ROUND(MIN(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as low_price,
  ts_rank(tsv_keywords, plainto_tsquery('merlot')) as rank,
  ROUND(AVG(ST_Distance(ST_GeographyFromText('SRID=4326;POINT(0.001 0.001)'),ST_GeographyFromText('SRID=4326;POINT(' || stores.longitude || ' ' || stores.latitude || ')')))) AS distance
FROM
  products
  JOIN product_types ON products.product_type_id = product_types.id
  JOIN categories ON products.category_id = categories.id
  JOIN prices ON prices.product_id = products.id 
  JOIN currencies ON prices.currency_id = currencies.id
  JOIN stores ON prices.store_id = stores.id
  JOIN brands ON products.brand_id = brands.id
  JOIN merchants ON stores.merchant_id = merchants.id
  JOIN manufacturers ON brands.manufacturer_id = manufacturers.id
, delivery_zones
WHERE ...

(consulta completa aqui: http://pastebin.com/VjJPTQWj )

O problema é: observe a FROM products...parte, se eu substituir isso por FROM products_active AS products, a consulta dá erro com:

ERROR:  column "products.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:   products.id, products.status, products.brand_id, products....

As duas tabelas (products e products_active) possuem estruturas idênticas! Eles são herdados um do outro!

Estou perdendo uma sutileza em nomes de tabelas de alias?

postgresql alias

2 respostas

  1. Best Answer

    O problema é que as 2 tabelas não são idênticas.

    O primeiro tem (id)como PRIMARY KEYenquanto o segundo tem (id)uma UNIQUErestrição (ou índice, não importa). Pode parecer um pequeno detalhe, mas não é.

    O Postgres adicionou na versão 9.1, um recurso onde as colunas funcionalmente dependentes não precisam ser mencionadas em uma GROUP BYcláusula (se a coluna da qual elas dependem for mencionada no GROUP BY) e ainda usadas nas cláusulas HAVING, SELECTe . ORDER BYNo entanto, a implementação não está 100% concluída. Ele não identifica todas as dependências funcionais possíveis, mas apenas aquelas provenientes de PRIMARY KEYrestrições. Embora uma UNIQUErestrição com todas as colunas NOT NULLnão seja significativamente diferente de uma PRIMARY KEYrestrição, a implementação não considera UNIQUEas restrições.

    Então, por exemplo, com essas duas tabelas bem parecidas (teste no SQLfiddle ):

    CREATE TABLE products
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    PRIMARY KEY (id)
  ) ;

CREATE TABLE products_active
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    UNIQUE (id)
  ) ;

    A consulta será bem-sucedida para a primeira tabela:

    SELECT p.id, p.status, p.name
FROM products AS p
GROUP BY p.id ;

    enquanto falhará no segundo:

    SELECT p.id, p.status, p.name
FROM products_active AS p
GROUP BY p.id ;

    com:

    ERRO: a coluna "p.status" deve aparecer na cláusula GROUP BY ou ser usada em uma função agregada

    • 2

  2. Como disse um dos comentaristas: a consulta original deveria ter um "GROUP BY" e o PostgreSQL aceitará uma consulta sem ele em determinadas circunstâncias (consulte Por que os curingas nas instruções GROUP BY não funcionam? ). Isso, combinado com a informação de que índices UNIQUE não são iguais a PRIMARY KEYs (PK's não permitem NULLs), resolveu esta questão.

    • 0

relate perguntas