sql round function throw error - Schema "pg_catalog" does not exist

Encountering the error "Schema "pg_catalog" does not exist" when using the ROUND function in a Vertica SQL query can be frustrating.

To understand the root cause, let's analyze a series of queries and their corresponding results:

WITH
il_grades(id,name,email,grade) AS (
            SELECT 42,'Joe','joe@foo.com',0
  UNION ALL SELECT 42,'Joe','joe@foo.com',1 
  UNION ALL SELECT 42,'Joe','joe@foo.com',2 
  UNION ALL SELECT 42,'Joe','joe@foo.com',3 
  UNION ALL SELECT 42,'Joe','joe@foo.com',4 
  UNION ALL SELECT 42,'Joe','joe@foo.com',5 
  UNION ALL SELECT 42,'Joe','joe@foo.com',6 
  UNION ALL SELECT 42,'Joe','joe@foo.com',7 
  UNION ALL SELECT 42,'Joe','joe@foo.com',8 
)
SELECT 
  name,
  email,
  ROUND(SUM(grade)/nullifzero(count(grade))) as avrage ----> ROUND(0/null)
FROM il_grades
GROUP BY name,email
LIMIT 10
;

In this query, the ROUND function is used to calculate the average grade for each student. Since there are no NULL values in the grade column, the calculation proceeds as expected, and the result is displayed.

WITH
il_grades(id,name,email,grade) AS (
            SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
  UNION ALL SELECT 42,'Joe','joe@foo.com',NULL::INT 
)
SELECT 
  name,
  email,
  ROUND(SUM(grade)/nullifzero(count(grade))) as avrage ----> ROUND(0/null)
FROM il_grades
GROUP BY name,email
LIMIT 10
;

In this query, all the grade values are set to NULL. When the ROUND function encounters a NULL value in the calculation, it returns a NULL result. Therefore, the average grade for each student is displayed as (null).

The error "Schema "pg_catalog" does not exist" is encountered when using an unsupported function or syntax in a Vertica SQL query. In this case, the ROUND function is not a standard Vertica function, and therefore, it is not recognized by the Vertica database.

To resolve this issue, you can use the built-in ROUND_NUMERIC function provided by Vertica. This function is specifically designed to handle numeric rounding operations and is compatible with Vertica's syntax and data types.

By replacing the ROUND function with ROUND_NUMERIC, the query will execute successfully, and the average grade for each student will be calculated and displayed correctly.