Re: Request to share information regarding errors

Good morning

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yogesh Sharma
Sent: Mittwoch, 16. November 2016 07:09
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Dear All,

Thanks for your support.

I am using postgresql 9.3.6 rpm in RHEL6 version.

In postgresql logs, I have found below error messages.
ERROR: null value in column "column name" violates not-null constraint.

You are trying to insert a row in a table that has a not-null constraint on a field.
Find the record that causes the error and include a valid value for the column that has the not null constraint.

Second error found:
ERROR: there is no unique constraint matching given keys for referenced table "table2"
alter table1 table_name add constraint fk_key foreign key (id, phone_id) references table2 (id, phone_id) on delete
restrict on update restrict;

A foreign key must match the primary key of the table it refers to. So you should create the primary key contraint on table2 using
id and phone_id.

CREATE UNIQUE INDEX table2_pkey ON table2 (id, phone_id);
ALTER TABLE table2 ADD PRIMARY KEY USING INDEX table2_pkey;

After that you should be able to add the foreign key to table1.

Hope this helps.
Charles

Could you please share the details of these errors and how to resolve these errors from logs.

Regards,
Yogesh

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dear Charles,

Thanks for sharing such information.
First thing, I want to confirm.

You are trying to insert a row in a table that has a not-null constraint on a field.
Find the record that causes the error and include a valid value for the column that has the not null constraint.

Entry is already deleted from table and value is defined not null in create table query.
But this type of error is not displaying every time.

A foreign key must match the primary key of the table it refers to. So you should create the primary key contraint on table2 using id and phone_id.

At this time, DB is shutdown.
This shutdown is related to this error or not?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles Clavadetscher
Sent: Wednesday, November 16, 2016 3:44 PM
To: Yogesh Sharma; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Good morning

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yogesh Sharma
Sent: Mittwoch, 16. November 2016 07:09
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Dear All,

Thanks for your support.

I am using postgresql 9.3.6 rpm in RHEL6 version.

In postgresql logs, I have found below error messages.
ERROR: null value in column "column name" violates not-null constraint.

You are trying to insert a row in a table that has a not-null constraint on a field.
Find the record that causes the error and include a valid value for the column that has the not null constraint.

Second error found:
ERROR: there is no unique constraint matching given keys for referenced table "table2"
alter table1 table_name add constraint fk_key foreign key (id,
phone_id) references table2 (id, phone_id) on delete restrict on
update restrict;

A foreign key must match the primary key of the table it refers to. So you should create the primary key contraint on table2 using id and phone_id.

CREATE UNIQUE INDEX table2_pkey ON table2 (id, phone_id); ALTER TABLE table2 ADD PRIMARY KEY USING INDEX table2_pkey;

After that you should be able to add the foreign key to table1.

Hope this helps.
Charles

Could you please share the details of these errors and how to resolve these errors from logs.

Regards,
Yogesh

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yogesh Sharma
Sent: Mittwoch, 16. November 2016 07:59
To: Charles Clavadetscher <clavadetscher@swisspug.org>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Dear Charles,

Thanks for sharing such information.
First thing, I want to confirm.

You are trying to insert a row in a table that has a not-null constraint on a field.
Find the record that causes the error and include a valid value for the column that has the not null constraint.

Entry is already deleted from table and value is defined not null in create table query.
But this type of error is not displaying every time.

Well yes, it only displays if the record that you are trying to insert violates the constraint, i.e. if you are trying to assign a
null value to a not-null column. Otherwise it doesn't.

A foreign key must match the primary key of the table it refers to. So you should create the primary key contraint

on table2 using id and phone_id.
At this time, DB is shutdown.

If the DB would be shut down, then you would not get any message from it. So that cannot be true.

This shutdown is related to this error or not?

No. The error message states clearly that a table cannot be modified because a precondition for that change is not given. As
mentioned above, you would never get a message from the DB if it's not running.

Bye
Charles

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
Clavadetscher
Sent: Wednesday, November 16, 2016 3:44 PM
To: Yogesh Sharma; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Good morning

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Yogesh Sharma
Sent: Mittwoch, 16. November 2016 07:09
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding errors

Dear All,

Thanks for your support.

I am using postgresql 9.3.6 rpm in RHEL6 version.

In postgresql logs, I have found below error messages.
ERROR: null value in column "column name" violates not-null constraint.

You are trying to insert a row in a table that has a not-null constraint on a field.
Find the record that causes the error and include a valid value for the column that has the not null constraint.

Second error found:
ERROR: there is no unique constraint matching given keys for referenced table "table2"
alter table1 table_name add constraint fk_key foreign key (id,
phone_id) references table2 (id, phone_id) on delete restrict on
update restrict;

A foreign key must match the primary key of the table it refers to. So you should create the primary key contraint
on table2 using id and phone_id.

CREATE UNIQUE INDEX table2_pkey ON table2 (id, phone_id); ALTER TABLE table2 ADD PRIMARY KEY USING INDEX
table2_pkey;

After that you should be able to add the foreign key to table1.

Hope this helps.
Charles

Could you please share the details of these errors and how to resolve these errors from logs.

Regards,
Yogesh

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general