There is a column named ... it cannot be referenced from this part of the query sub query. How to fi-CodePudding

There's a simple table with columns a,b,c,d. I want value in D (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
)

CodePudding user response：

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.