I am trying to insert values from a select query using this:
INSERT INTO cb (vol_sec)
SELECT sum(vol)
FROM cb
GROUP BY cusec;
but the error message is ERROR: the value null for column «id» restriction violates not null. I have tried to change NOT NULL to NULL but i have another message 'The column <> is in the primary key. Is there any way to avoid this errors?.Thanks in advance.
There must be follow the step :
drop the constraint PRIMARY KEY
ALTER TABLE schema.cb DROP CONSTRAINT name_of_constraint;
update the constrait not nul .
ALTER TABLE schema.cb ALTER COLUMN id DROP NOT NULL;
execute your insert.
As id column in target table cb is primary key and it seems you does not define any default value for id column.
when value is inserted in cb table there is no value for primary column id. so you are getting this issue.
ERROR: the value null for column «id» restriction violates not null.
try for assign value to primary key column id. you can do it by changing column data type to serial it will auto increment.
drop the table cb and create with updated data type for id
--drop TABLE cb;
CREATE TABLE cb
(
id integer serial,
vol_sec integer,
--other columns,
CONSTRAINT pk_id PRIMARY KEY (id)
);
hope it works for you.
First i apologize for the delay. All your comments helped me to solve the problem. Following the steps proposed i finally got to insert the column (id) as serial type and executing the query the data were inserted successfully. Thanks.
idcolumn which is a primary key (henceNOT NULL), but doesn't have a default value. You probably need to add a sequence (or use an existing one if appropriate) and set theDEFAULTforidto grab the next value of that sequence. But of course, the exact details of your database schema and/or the way you use that table may have an influence on what exactly you should be doing.