How can I use an SQL aggregate function on data I directly input at the command line (e.g. AVG(1, 2, 3))?

Question

How can I enter multiple values into an aggregate function using just data I enter at the command line? Say, in Postgres, I run the following.

SELECT AVG(2);

I'll get the correct answer, but I can't find a way to enter multiple values, such as below, without getting an error.

SELECT AVG(1,NULL,2,3);

I've tried wrapping the numbers in various brackets but to no effect. What's the syntax I'm missing?

EDIT: Additionally, is there a way to include NULLs in the input?

Answer 1

AVG() is an aggregate that operates over multiple rows. So you need to convert your comma separated list to one row per value to be able to use an aggregate like avg(). This could be done using e.g. string_to_table

select avg(num::numeric)
from string_to_table('1,2,3', ',') as x(num)

If you want to include a NULL value, you could add it to the list and convert it to null before casting it to a numeric value:

select avg(nullif(num, 'null')::numeric)
from string_to_table('1,2,3,4,null', ',') as x(num)