主页 / dba / 问题 / 123871
Accepted
Ivan Voras
Asked: 2015-12-17 12:59:39 +0800 CST

PostgreSQL 在给表起别名时需要“GROUP BY”吗？

  • 772

这里有一些奇怪的东西：有一个名为“products”的大表，它通过继承进行分区，因此有两个子表：products_active 和 products_inactive，约束是 products_activestatus=1和 products_inactive 获取所有其他状态。

有一个连接一堆表的大型查询，这是第一部分：

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 ...

（完整查询：http: //pastebin.com/VjJPTQWj）

问题是：请注意FROM products...部分，如果我将其替换为FROM products_active AS products，则查询错误为：

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....

这两个表（products 和 products_active）具有相同的结构！他们是互相继承的！

我错过了别名表名的微妙之处吗？

postgresql alias

2 个回答

  1. Best Answer

    问题是这两个表不相同。

    第一个有一个(id)，PRIMARY KEY而第二个有(id)一个UNIQUE约束（或索引，没关系）。这似乎是一个小细节，但事实并非如此。

    Postgres 在9.1 版本中添加了一个功能，即功能相关的列不需要在GROUP BY子句中提及（如果它们所依赖的列在,和子句中提到GROUP BY）并且仍然使用。但是，实施并非 100% 完成。它不会识别所有可能的功能依赖关系，而只会识别那些来自约束的功能依赖关系。虽然具有所有列的约束与约束没有任何有意义的区别，但实现不考虑约束。HAVINGSELECTORDER BYPRIMARY KEYUNIQUENOT NULLPRIMARY KEYUNIQUE

    因此，例如，使用这两个非常相似的表（在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)
  ) ;

    第一个表的查询将成功：

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

    虽然它会第二次失败：

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

    和：

    错误：列“p.status”必须出现在 GROUP BY 子句中或在聚合函数中使用

    • 2

  2. 正如其中一位评论者所说：原始查询应该有一个“GROUP BY”，PostgreSQL 在某些情况下会接受没有它的查询（请参阅为什么 GROUP BY 语句中的通配符不起作用？）。结合 UNIQUE 索引与PRIMARY KEY 不同（PK 不允许 NULL）的信息，解决了这个问题。

    • 0

相关问题