How to get array from non-array field for INSERT command?

Question

I'm trying to use a SELECT statement together with a INSERT INTO command. Everything would work fine, if there wasn't a small problem: some fields of the table are defined as real[] but my input is numeric. Thus, the question:

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

My setting looks like this:

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

The idea is to insert the tuples from the tempLogTable to the finalLogTable using INSERT INTO ... SELECT .... Unfortunately, because of various reasons the data types are given and I would not like to change these for the moment (not to break anything).

I'm using PostgreSQL 9.2.

Craig Ringer · Accepted Answer · 2013-05-31 16:30:17Z

3

SELECT ARRAY[thenumeric::real] FROM the_table;

or

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

They're not really any different for a one-element array.

real has limits that numeric doesn't. In particular, comparing real values for equality doesn't work reliably; you should instead compare for two numerics being different by smaller than a small (somewhat task-specific) amount. It also can't represent values as big or small as numeric can. See the floating point guide among other info on comparing floats. This will be much harder to do right when they're wrapped in arrays.

For the purpose you describe, where it sounds like you are just collecting stats or historical data, that isn't going to be a problem. It usually only turns out to be an issue where people try to write:

WHERE some_real = some_other_real

which will result in surprising and unexpected behaviour.

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

edited May 31, 2013 at 16:30

answered May 31, 2013 at 16:10

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

3 Comments

navige Over a year ago

Ah! That's a nice solution! Thanks also for the hint for comparing real!

navige Over a year ago

I'm really sorry about not being precise! I updated my question with more information!

navige Over a year ago

Thanks for the very clarifying answer! It works very good! Thanks!

Collectives™ on Stack Overflow

How to get array from non-array field for INSERT command?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related