Change to CAST syntax from "::" PostgreSQL [duplicate]

Question

I am working through the book Practical SQL by Anthony DeBarros. I am on chapter 6 and came across this code:

SELECT county_name AS county,
       state_name AS state,
       area_water::numeric / (area_land + area_water) * 100 AS pct_water
FROM us_counties_pop_est_2019
ORDER BY pct_water DESC;

on line 3, author uses CAST to change the data type of area_water to numeric, in doing so he uses the short-form notation for CAST from PostgreSQL.

I have been trying to use the CAST notation instead of the double colon, and I have been unable to get a correct query. Can someone help me with the correct syntax for the query here?

First Code from book

SELECT county_name AS county,
       state_name AS state,
       area_water::numeric / (area_land + area_water) * 100 AS pct_water
FROM us_counties_pop_est_2019
ORDER BY pct_water DESC;

Correct Result:

"Keweenaw County"   "Michigan"  90.94723747453215452900
"Leelanau County"   "Michigan"  86.28858968116583102500
"Nantucket County"  "Massachusetts" 84.79692499185512352300
"St. Bernard Parish"    "Louisiana" 82.48371149202893908400
"Alger County"  "Michigan"  81.87221940647501072300

Code I tried for CAST notation:

SELECT county_name AS county,
       state_name AS state,
       area_water CAST (area_water AS numeric)
       area_water / (area_land + area_water) * 100 AS pct_water
FROM us_counties_pop_est_2019
ORDER BY pct_water DESC;

Result:

ERROR:  syntax error at or near "("
LINE 3:        area_water CAST (area_water AS numeric)

What is it that I am doing wrong here?

I would NOT use a space between CAST and the ( . (But that does not seem to be the problem here ) the problem is that you have the column-alias area_water before the column value CAST (area_water AS numeric) (and a missing comma at the end of that line). — Luuk
– Luuk, Commented Oct 17, 2023 at 16:09

Frank Heikens · Accepted Answer · 2023-10-17 15:59:43Z

0

Try this one:

SELECT county_name AS county,
       state_name AS state,
       CAST (area_water AS numeric) / (area_land + area_water) * 100 AS pct_water
FROM us_counties_pop_est_2019
ORDER BY pct_water DESC;

CAST() is a function and in your case, you need the result from this function to do the calculation. Use this order and you will be fine.

answered Oct 17, 2023 at 15:59

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change to CAST syntax from "::" PostgreSQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related