Search code examples
sqlmathtrigonometrypostgresql-9.3

postgresql INSERTs NULL values from SELECT COS(another field) query

If I run

SELECT 
(cos(radians(spa.spa_zenithangle)))
FROM generic.spa;

I get a sensible range of results from -1 to 1. but if I run this insert statement all the resulting values in the spa.spa_cos_zenith field are NULLs

INSERT INTO generic.spa
(spa_cos_zenith)
SELECT 
(cos(radians(spa.spa_zenithangle)))
FROM generic.spa;

The table definition is:

CREATE TABLE generic.spa (
 spaid INTEGER DEFAULT nextval('generic.spa_id_seq'::regclass) NOT NULL,
 measurementdatetime TIMESTAMP WITHOUT TIME ZONE,
 spa_zenithangle NUMERIC(7,3),
 spa_cos_zenith DOUBLE PRECISION,
 CONSTRAINT spa_pk PRIMARY KEY(spaid)
) 
WITH (oids = false);

Anyone know why the COS functions returns results ok but they cant be inserted into another field?

Solution

  • I suspect you want update, not insert:

    UPDATE generic.spa
    SET spa_cos_zenith = cos(radians(spa.spa_zenithangle));

    INSERT inserts new rows, so you are duplicating the rows. The only column in the new rows is the COS() value. Nothing changes in the old rows.