PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – sql round function throw error – Schema "pg_catalog" does not exist

I’m querying Vertica using node-vertica npm packge (Node server), I noticed that every time the ROUND expression is 0/null, I’m getting the error – ERROR: Schema "pg_catalog" does not exist.

EDIT:

For example:

SELECT 
  name,
  email,
  ROUND(SUM(grade)/nullifzero(count(grade))) as avrage ----> ROUND(0/null)
FROM il_grades
GROUP BY name,email
LIMIT 10

Return this error. Once I remove the ROUND() – everything is working properly.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The vertica-client package was the issue (unmaintained) , after I removed it and install the official Vertica's vertica-nodejs node pkg it works as expected!


  2. 0

    Now, I don’t run into your error.
    Well, it’s standard Vertica as it runs everywhere, on Linux from an RPM, on AWS, on GCP, on Azure …

    with grade with int values 0 through 8:

    WITH
il_grades(id,name,email,grade) AS (
            SELECT 42,'Joe','[email protected]',0
  UNION ALL SELECT 42,'Joe','[email protected]',1 
  UNION ALL SELECT 42,'Joe','[email protected]',2 
  UNION ALL SELECT 42,'Joe','[email protected]',3 
  UNION ALL SELECT 42,'Joe','[email protected]',4 
  UNION ALL SELECT 42,'Joe','[email protected]',5 
  UNION ALL SELECT 42,'Joe','[email protected]',6 
  UNION ALL SELECT 42,'Joe','[email protected]',7 
  UNION ALL SELECT 42,'Joe','[email protected]',8 
)
SELECT 
  name,
  email,
  ROUND(SUM(grade)/nullifzero(count(grade))) as avrage ----> ROUND(0/null)
FROM il_grades
GROUP BY name,email
LIMIT 10
;
 name |    email    |        avrage        
------+-------------+----------------------
 Joe  | [email protected] | 4.000000000000000000

    With grade set to NULL everywhere:

    WITH
il_grades(id,name,email,grade) AS (
            SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',NULL::INT 
  UNION ALL SELECT 42,'Joe','[email protected]',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
;
 name |    email    | avrage 
------+-------------+--------
 Joe  | [email protected] | (null)
(1 row)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search