How to get an array from a field without an array for an INSERT command?

Question

I am trying to use the SELECT with the INSERT INTO . Everything will work fine if there was no small problem: some fields of the table are defined as real[] , but my input is numeric . So the question is:

Is there a function in PostgreSQL to create an array of real type (with only one element) from a single numeric input?

My setup looks like this:

 tempLogTable(..., logValue NUMERIC, ...) finalLogTable(..., logValues REAL[], ...) 

The idea is to insert tuples from tempLogTable into finalLogTable using INSERT INTO ... SELECT ... Unfortunately, for various reasons, data types are given, and I would not want to change them at the moment (so as not to break anything).

I am using PostgreSQL 9.2.

Answer 1

 SELECT ARRAY[thenumeric::real] FROM the_table; 

or

 SELECT ARRAY[thenumeric]::real[] FROM the_table; 

They do not differ from each other for a singleton array.

real has limits that numeric does not have. In particular, comparing real values ​​for equality does not work reliably; instead, you should compare that for two digits a smaller than a small (to some extent given a task) sum differs. It also cannot represent values ​​as large or small, as numerical. See the floating point guide for other information on comparing floats. It will be much harder to do it right when they are wrapped in arrays.

For the purpose of describing where it sounds like you are simply collecting statistics or historical data, this will not be a problem. This usually turns out to be a problem when people try to write:

 WHERE some_real = some_other_real 

which will lead to unexpected and unexpected behavior.

You should be fine with INSERT INTO ... SELECT as described.