PostgreSQL - number treated as the string, most of aggregate functions don't work

Question

I am new to PostgreSQL. I am watching the tutirial from FreeCodeCamp by following their examples:

But instead of PostgreSQL I use the Server-based platform (PhpPgAdmin).

The problem is, that neither SUM nor AVG and many others aggregate functions cannot be executed.

The problem seem to be the same constantly:

"Function ... does not exist"

No function matches the given name and argument types. You might need to add explicit type casts.

I found some similar problem here:

but it's related to more complicated example.

What I guess, the PhpPgAdmin treats all my numbers as the strings and here is the problem.

I tried this example:

but it returns the other error:

operator does not exist: character varying = bigint

I think the $ before the price is not a problem as the MIN and MAX functions work.

What is the reason behind it?

"*it treats all my numbers as the strings and here is the problem.*" - yes, you've stored the dollar amounts as strings. "*I think the $ before the price is not a problem*" - it very much is. A number cannot contain dollar signs. It's a string, a character sequence. MIN and MAX "work" because they are also defined on strings, but they don't compare the numbers (and `"10" < "2"` for example - you just don't notice because all your values have the same number of digits) — Bergi, Jul 09 '21 at 09:30
"*PhpPgAdmin treats all my numbers as the strings*" - unlikely. Please show us how you created/defined the table, and how you entered the data. — Bergi, Jul 09 '21 at 09:31
[Sum exists and works.](https://www.db-fiddle.com/f/uC375jRPZ3kbNseYJRKJgx/0). But it does not make much sense to count the values returned by an aggregate function. The result is always 1. — ceving, Jul 09 '21 at 09:33

score 1 · Accepted Answer · answered Jul 09 '21 at 09:26

1

You may trim the leading $ in the price column, then cast the string amount to float, before summing, e.g.

SELECT SUM(CAST(TRIM('$' FROM price) AS float)) AS total_sum
FROM car;

answered Jul 09 '21 at 09:26

Tim Biegeleisen

Yes it works! However, why the Youtube example don't looks so complicated? It still must be some reason behind it or is it the phpPGadmin reason? – Geographos Jul 09 '21 at 09:33
While that may work, the proper fix is of course to use number data in the first place – Bergi Jul 09 '21 at 09:33

1 Answers1