STDEV Function - SQL Server to PostgreSQL Migration

In SQL Server, the STDEV is aggregte and analytic window function that calculates the standard deviation. In PostgreSQL, you can use the STDDEV function.

Consider the following sample table:

  CREATE TABLE products (name VARCHAR(30), category CHAR(1), items INT);
 
  -- Insert sample rows
  INSERT INTO products VALUES ('Apple', 'A', 5);
  INSERT INTO products VALUES ('Orange', 'A', 10);
  INSERT INTO products VALUES ('Melon', 'B', 15);

SQL Server:

  -- Get the standard deviation for all products
  SELECT STDEV(items) FROM products;
  /* 5 */
 
  -- Get the standard deviation for all products within a category (analytic window function)
  SELECT STDEV(items) OVER (PARTITION BY category) FROM products;
  /* 3.53553390593274 */
  /* 3.53553390593274 */
  /* NULL */

PostgreSQL:

  -- Get the standard deviation for all products
  SELECT STDDEV(items) FROM products;
  /* 5.0 */
 
  -- Get the standard deviation for all products within a category (analytic window function)
  SELECT STDDEV(items) OVER (PARTITION BY category) FROM products;
  /* 3.5355339059327376 */
  /* 3.5355339059327376 */
  /* NULL */

For more information, see SQL Server to PostgreSQL Migration.