Question 90141 in PostgreSQL Question posted in
The official documentation can be found here.

"There is a column named … it cannot be referenced from this part of the query." How to fix this? – Postgresql

There’s a simple table with columns a,b,c,d. I want value in d to be a+b if c>0 and a-b if c<=0. How can I do this?

I tried this code and it doesn’t work. How can I dynamically insert data in a table?

INSERT INTO my_table VALUES(1,2,3, 
CASE
    WHEN c<0
    THEN a+b
    ELSE a-b
END
)
Tags:

2

Answers


  1. 0

    I want value in D (a+b) if C>0 and (a-b) if C<=0. How can I do this?

    You can’t reference columns in values. Instead, you need to repeat the values. Assuming you’re issuing the query in a programming language using bind parameters…

    insert into my_table(a, b, c, d)
  values(
    $1, $2, $3,
    case
    when $3 > 0 then $1 + $2
    else $1 - $2
  )

    You can also write a function.

    --  I want value in D (a+b) if C>0 and (a-b) if C<=0.
create function my_insert(a int, b int, c int)
returns void
language plpgsql
as $$
declare d integer;
begin
  case
  when c > 0 then d = a + b;
  else d = a - b;
  end case;

  insert into my_table ("a", "b", "c", "d") values (a, b, c, d);
end;
$$

select my_insert(1, 2, 3);

    Demonstration.

    Login or Signup to reply.
  2. 0

    Working with literal values, you can achieve the same with a subquery:

    INSERT INTO my_table (a,b,c,d) 
SELECT *, CASE WHEN c<0 THEN a+b ELSE a-b END AS d
FROM (
   VALUES
     (3,2, 1)  -- input values here, once
   , (3,2,-1)  -- multiple rows if need be
   ) sub(a,b,c);

    This works without explicit type casts for basic types like integer or text as the assumed default types for the literals happen to match. You may need explicit data type casts for other data types in a separate VALUES expression. See:

    More fundamentally, avoid the problem by not storing redundant data in the table to begin with.

    CREATE TABLE my_table (
  a int
, b int
, c int
--  NO d; will be computed on the fly
);

    INSERT is back to basics:

    INSERT INTO my_table (a,b,c) VALUES
  (3,2, 1)
, (3,2, 0)
, (3,2,-1)
;

    The additional column d can cheaply be computed on the fly. Typically much cheaper than reading more data pages due to increased storage:

    SELECT *, CASE WHEN c<0 THEN a+b ELSE a-b END AS d
FROM   my_table;

    You might persist the logic in a VIEW:

    CREATE VIEW my_view AS
SELECT *, CASE WHEN c<0 THEN a+b ELSE a-b END AS d
FROM   my_table;

SELECT * FROM my_view;

    If you positively need the computed value stored (like when computation is relatively expensive, or you do other things with the column), use a STORED generated column (Postgres 12+):

    ALTER TABLE my_table
ADD column d int GENERATED ALWAYS AS (CASE WHEN c<0 THEN a+b ELSE a-b END) STORED;

SELECT * my_table;

    fiddle

    See:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search