I've dug around on here and accepted answers to other questions haven't worked for me.

I have a table "mytable" defined as such

create table mytable (
column1 varchar(50) PRIMARY KEY,
column2 integer,
column3 money
)

column3 is all null, and I want to insert column2/1.25 into column3

I've tried these:

--This gets 23502 "violates not-null constraint"
insert into mytable (column3)
select column2/1.25 from mytable

--This gets 42601 "syntax error near "select""
insert into mytable (column3)
select column2/1.25 from mytable

--Same as above with parentheses, gets the same error
insert into mytable (column3) values
(select column2/1.25 from mytable )

--Error 42601 "syntax error at or near "select""
insert into mytable (column1,column3) values
(select column1, column2/1.25 from mytable)

I've tried other slight variations of parentheses, fields in double quotes, but can't figure out why this is throwing me errors. Column1 is a primary key and not nullable, but I'm not trying to insert into that column.

Any help would be appreciated

CodePudding user response：

INSERT adds new rows to the table, which you do not want. To modify existing rows, use UPDATE:

UPDATE mytable
SET column3 = column2 / 1.25;

But that is a bad idea for several reasons:

1. Avoid the money type. Use something like numeric(15,2) instead.

2. Don't keep redundant data in the database unless it is unavoidable for performance reasons. You could use a view:

   CREATE VIEW myview AS
   SELECT column1, column2,
          CAST (column2 / 1.25 AS numeric(15,2)) AS column3
   FROM mytable;
   

3. If you really want to persist the redundant data, use a generated column:

   ALTER TABLE mytable
   ADD column3 numeric(15,2) GENERATED ALWAYS AS (column2 / 1.25) STORED;
   

CodePudding user response：

INSERT command is used to insert a new row in the table. What you want is to update an existing row.

https://www.postgresqltutorial.com/postgresql-update/

UPDATE mytable
SET column3 = column2/1.25