Postgres Syntax error at or near "CAST"

Question

According to the docs and this tutorial. I can use;

CAST ( expression AS type );

I have a series of values stored as text which are monetary values (formatted as 200.00 i want to be decimal so I have attempted;

SELECT totalvalue 
CAST (table.totalvalue AS decimal(12,2))
FROM table;

But this just returns an error on the syntax

ERROR: syntax error at or near "CAST"

I've tried swapping the type from decimal to integer but i get the same problem.

Postrgres 9.6.2

Please show your full query statement. Currently, it looks like your query is only CAST ..., which won't work. This should be at least something like SELECT CAST (table.totalvalue AS decimal(12,2)) FROM table; as the tutorial you linked does explain. — dhke
– dhke, Commented Jun 29, 2017 at 17:31
Perhaps you forgot comma after the first column (totalvalue). Try: SELECT totalvalue, CAST (table.totalvalue AS decimal(12,2)) FROM table; — Michel Milezzi
– Michel Milezzi, Commented Jun 29, 2017 at 17:42
That worked but when i added this to UPDATE d_voa_record1 SET totalvalue = CAST (totalvalue AS numeric(12,2)); It runs through but doesn't change the records — mapping dom
– mapping dom, Commented Jun 29, 2017 at 17:55
Well that statement does not change the value of the column. Why do you expect that to change anything? What are you trying to achieve with that? — user330315
– user330315, Commented Jun 29, 2017 at 18:11

Michel Milezzi · Accepted Answer · 2017-06-29 19:08:38Z

1

I guess you're misunderstanding the purpose of CAST. Calling CAST will not change the type of your column, but only its value in the moment of SELECT. To change its type you must combine CAST with an ALTER COLUMN:

--Changing type of column using cast operator ::
ALTER TABLE d_voa_record1 
    ALTER COLUMN totalvalue TYPE NUMERIC(12,2) USING (totalvalue::NUMERIC(12,2));

--Changing type of column using cast function
ALTER TABLE d_voa_record1 
    ALTER COLUMN totalvalue TYPE NUMERIC(12,2) 
        USING CAST(totalvalue AS NUMERIC(12,2));

--Just changing returned value of column
SELECT CAST(totalvalue AS NUMERIC(12,2)) AS totalvalue_as_numeric,
    totalvalue AS unchanged_totalvalue FROM d_voa_record1;

edited Jun 29, 2017 at 19:08

answered Jun 29, 2017 at 18:16

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

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

Comments

Nick · Accepted Answer · 2017-06-29 18:31:51Z

1

Remove totalvalue that you put before CAST(..), and it should work:

SELECT 
  CAST (t1.totalvalue AS decimal(12,2))
FROM t1;

I also hope that your didn't use table as a name for your table, beause it's SQL keyword. If you did, then always double-quote it:

SELECT 
  CAST ("table".totalvalue AS decimal(12,2))
FROM "table";

answered Jun 29, 2017 at 18:31

Nick

2,54315 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres Syntax error at or near "CAST"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related