postgresql - insert result of query SELECT EXTRACT into another table

Question

I have the following table in postgresql (table1):

 Var1,
 var2,
 var3,
 timestamp1  timestamp without time zone NOT NULL,
 timestamp2  timestamp without time zone NOT NULL,
 diff double precision,

The column diff is empty.

I calculate the variable diff by the following code: SELECT EXTRACT(EPOCH FROM ((timestamp1 – timestamp2)/1800))

I want insert the result of this operation in variable diff of table 1.

I write the following code, but do not work…

CREATE TEMPORARY TABLE temptablename AS
SELECT EXTRACT(EPOCH FROM ((timestamp1 – timestamp2)/1800)) AS diff2 from table1;

INSERT INTO table1 (diff) SELECT diff2 FROM temptablename;

ERROR:  null value in column "" violates not-null constraint
DETAIL:  Failing row contains (null, null, null, null, null,83).

Well what do you expect? You are trying to insert null into columns that are defined as not null — user330315
– user330315, Commented Mar 13, 2014 at 17:17

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-03-13 17:17:19Z

1

Assuming your arithmetic is right, it sounds like you just need an update statement.

update table1
set diff = extract(epoch from ((timestamp1 – timestamp2)/1800))
where diff is null;

The WHERE clause isn't strictly necessary, since you already know that column is empty. But it guards against overwriting values the second time you run that statement.

answered Mar 13, 2014 at 17:17

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

Collectives™ on Stack Overflow

postgresql - insert result of query SELECT EXTRACT into another table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related