null value in column violates not-null constraint PostgreSQL

Question

I'm trying to import a csv into PostgreSQL database table.When i executing the following query:

My table name is trendmania_video

COPY public.trendmania_video FROM 'C:\Users\Shahnawaz Irfan\Desktop\0.csv' DELIMITER ',' CSV HEADER;

a following error occurred:

ERROR:  null value in column "v_id" violates not-null constraint
DETAIL:  Failing row contains (null, null, UgzYr_WZlR73yFBnRdx4AaABAg, yar 
kindly ap urdu m b toturial bna lety wordpress k liye to hma..., null, null, 
null, null, null, null, null, null, null, null).
CONTEXT:  COPY trendmania_video, line 10: ",,UgzYr_WZlR73yFBnRdx4AaABAg,yar 
kindly ap urdu m b toturial bna lety wordpress k liye to hmari b he..."
SQL state: 23502

I also tried manually by using import button, but same error occurs.

Have values for each row in v_id, or make the column nullable — JGH
– JGH, Commented Dec 20, 2019 at 20:08
@JGH v_id coloumn has primary key constraint and when i add v_id coloumn nullable, an error occurs "set primary_key feild as null=False" — Shahnawaz Irfan
– Shahnawaz Irfan, Commented Dec 20, 2019 at 20:13
It depends on your use case. You can use a sequence, or you can populate the column in the csv.... or you can use another column for the PK, or... — JGH
– JGH, Commented Dec 20, 2019 at 20:20

MEDZ · Accepted Answer · 2019-12-20 20:44:26Z

16

In your table trendmania_video, you have v_id to be not null which causes this issue. You one option is to get ride of the not null constrain:

ALTER TABLE public.trendmania_video ALTER COLUMN v_id DROP NOT NULL;

If this is a new table then it's better to recreate it with a new table with an auto-cremented id while v_id is another value.

CREATE TABLE trendmania_video(
   id SERIAL PRIMARY KEY,
   v_id VARCHAR
   --the rest of the columns
);

edited Dec 20, 2019 at 20:44

answered Dec 20, 2019 at 20:24

MEDZ

2,2952 gold badges17 silver badges19 bronze badges

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

10 Comments

Shahnawaz Irfan Over a year ago

ERROR: column "v_id" is in a primary key SQL state: 42P16

Shahnawaz Irfan Over a year ago

i can't fill records in v_id because its our project criteria.

Miles Elam Over a year ago

Or creating a temporary table as a working space to get things loaded and cleaned before copying to the final table. I find this two-step process to be easier than trying to fix the CSV or breaking existing table constraints.

MEDZ Over a year ago

@ShahnawazIrfan I've updated my answer with an alternative. You can re-create the table be adding a separated auto-cemented id and v_id as a normal column.

Shahnawaz Irfan Over a year ago

Dear, i can't set multiple primary keys field but another error occurs after executing "multiple primary keys for table trendmania_video" are not allowed.

|

cherni · Accepted Answer · 2023-09-18 18:21:41Z

0

if you have this autogenerated id in your entity , just delete it : @GeneratedValue(strategy = GenerationType.IDENTITY)

answered Sep 18, 2023 at 18:21

cherni

1

Collectives™ on Stack Overflow

null value in column violates not-null constraint PostgreSQL

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related