How to cut off decimals from query results

Question

When I query a catalog like NSC DR2, the R.A. and dec. columns list up to 14 decimals. I want to cut off some of the decimals (to 7 decimals max) to reduce the size of the results table and thought the ROUND function would be the best way. Here's a little test code:

SELECT ROUND(ra, 7) as ra, dec
FROM nsc_dr2.object
WHERE 't' = Q3C_RADIAL_QUERY(ra, dec,0.0,0.0,0.01) 

It throws an error message though.

>Error: PSQLException: ERROR: function round(double precision, integer) does not exist.

What's the best/working way to cut off decimals from a column like ra or dec?

Answer 1

Hi, thanks for reaching out. You could use CAST:

SELECT cast(ra as numeric(10,7)), cast(dec as numeric(10,7)) from nsc_dr2.object limit 5

Note that the casting will cost extra time; how much, I don't know, you should experiment with some LIMIT values first.

Best,
Robert

Comments

Thanks Robert. I'll give it a try. :)

I'm still interested though why ROUND() doesn't work. I thought Astro Data Lab is using PostgreSQL which knows this function?

---

I'm either doing something wrong or the code is wrong. I get an error message:

Error: IllegalArgumentException: net.sf.jsqlparser.JSQLParserException JSQLParserException: ParseException: Encountered "( ra as" at line 1, column 21. Was expecting one of: "AS" ... "INTO" ... "FROM" ... <S_IDENTIFIER> ... <S_QUOTED_IDENTIFIER> ... "," ... "." ... "(" "NULL" ... "(" "CASE" ... "(" "?" ... "(" "+" ... "(" "-" ... "(" "{fn" ... "(" <S_IDENTIFIER> "." ... "(" <S_IDENTIFIER> "(" ... "(" <S_DOUBLE> ... "(" <S_INTEGER> ... "(" <S_IDENTIFIER> "*" ... "(" <S_IDENTIFIER> "/" ... "(" <S_IDENTIFIER> "+" ... "(" <S_IDENTIFIER> "-" ... "(" <S_IDENTIFIER> "," ... "(" <S_IDENTIFIER> ")" ... "*" ... "/" ... "+" ... "-" ...

---

Gave it another test. "cast()" is working with the Download client. I've tested with a (python) query resulting in ~42,000 rows and it didn't slow the process down very much. So thanks again Robert. :)

The error above came from the online Query interface (https://datalab.noirlab.edu/query.php). When I tried the sample code from above it asked me to remove the limit so I just tested with:

> SELECT cast(ra as numeric(10,7)), cast(dec as numeric(10,7)) from nsc_dr2.object

---

Hi Martin,

thanks for diagnosing a bit more. We did the same, and this is the gist:

- The web query form uses ADQL, and "ROUND" is a reserved keyword in ADQL, but our implementation doesn't have it.

- When you use the datalab command line client, or the queryClient python package, you can submit a query as Postgres SQL (which is the default), and that does support ROUND(). But, curiously, the ROUND() function expects the first argument to be 'numeric', so one has to cast it as such. Meaning, this works:

from dl import queryClient as qc

print(qc.query(sql='SELECT ra, CAST(ra AS numeric(10,7)) AS ra_numeric, ROUND(CAST(ra AS numeric),4) AS ra_round FROM nsc_dr2.object LIMIT 3'))

ra,ra_numeric,ra_round
-8.972056466339541,-8.9720565,-8.9721
-8.960232627460641,-8.9602326,-8.9602
-8.956693787104939,-8.9566938,-8.9567

I.e., you might as well stick with just the "CAST AS NUMERIC(10,7)" or so.

Cheers,
Robert

---

Hi Robert,

thanks for this thorough investigation. :)

> The web query form … implementation doesn't have it.

The error message makes sense then and it's good to know the web query uses ADQL instead of SQL.

> When you use the datalab command line client, or the queryClient python package…

I've mixed those two up as being the same thing but upon further investigation I understand now there's a crucial difference between the command line client and using the queryClient python package.
To be 100% precise, I was indeed using the queryClient python package and not the command line client when testing and finding out that cast() was working as intended.

> … you might as well stick with just the "CAST AS NUMERIC(10,7)" or so.

I think you're right. Makes sense to just cast() and save the extra work of ROUND().