Thread: ERROR: check constraint - PostgreSQL 9.2
Hi all, need some help to add a constraint to an existing table (with data).
I'm running the command:
Query:
ALTER TABLE integrations.accounts DROP CONSTRAINT IF EXISTS cc_at_least_one_setting_needed, ADD CONSTRAINT cc_at_least_one_setting_needed CHECK (("qb_settings" IS NOT NULL) or ("xero_settings" IS NOT NULL) or ("freshbooks_settings" IS NOT NULL) or ("myob_settings" IS NOT NULL) or ("ppy_settings" IS NOT NULL));But, I got the following error:
ERROR: check constraint "cc_at_least_one_setting_needed" is violated by some row
So, I made a SELECT to get some data, to see where's the issue:
SELECT * FROM integrations.accounts WHERE qb_settings IS NULL OR xero_settings IS NULL OR freshbooks_settings IS NULL OR myob_settings IS NULL OR ppy_settings IS NULL;And I got 59 rows. So, it seems that's my problem - I have NULLS that violate the CHECK CONSTRAINT.
Question:
How can I solve the problem? How can I get the command successfully be done?
Cheers;
Lucas PossamaiOn Jan 24, 2016, at 8:12 PM, "drum.lucas@gmail.com" <drum.lucas@gmail.com> wrote: > How can I solve the problem? How can I get the command successfully be done? Two options: 1. Fix the data. 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows the addition of a constraint without actuallychecking its validity. -- -- Christophe Pettus xof@thebuild.com
On Jan 24, 2016, at 8:17 PM, Christophe Pettus <xof@thebuild.com> wrote: > 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows the addition of a constraint without actuallychecking its validity. And note that you might miss some potential planner optimizations this way, as the planner will not assume the constraintholds true unless you remove the NOT VALID condition from it with VALIDATE CONSTRAINT. -- -- Christophe Pettus xof@thebuild.com
Hello Althought both options are technically correct, I guess that the first one is the only reasonable one. What is the pointof having a check constraint that is not checked? If all fields in the check constraint must not be null there must be a reason for it.Possibly the "wrong" data is useless anyway (some test data that was not deleted) or the constraint only applies from a certain pointin time because something in the system built on top of it changed. In the latter case, since the data has a time stamp you may extendthe constraints to include the point in time from which it must apply. Bye Charles > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Christophe Pettus > Sent: Montag, 25. Januar 2016 05:18 > To: Postgres General <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2 > > > On Jan 24, 2016, at 8:12 PM, "drum.lucas@gmail.com" <drum.lucas@gmail.com> wrote: > > > How can I solve the problem? How can I get the command successfully be done? > > Two options: > > 1. Fix the data. > > 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows the addition of a constraint without > actually checking its validity. > > -- > -- Christophe Pettus > xof@thebuild.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher <clavadetscher@swisspug.org> wrote: > What is the point of having a check constraint that is not checked? Well, it *is* checked going into the future; it's just not checked at the time the constraint is added. Ultimately, youdo want to fix the data, but this makes it a two-step process, and reduces the time the table is locked against access.
On 1/24/16, Christophe Pettus <xof@thebuild.com> wrote: > > On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher > <clavadetscher@swisspug.org> wrote: > >> What is the point of having a check constraint that is not checked? > > Well, it *is* checked going into the future; it's just not checked at the > time the constraint is added. Ultimately, you do want to fix the data, but > this makes it a two-step process, and reduces the time the table is locked > against access. NOT VALID constraint checks new and updated rows, and gives an extra time to fix current data and be sure there will be no new rows that violates the check constraint during and after the fixing process. N.B.: Prior 9.4 it does *NOT* reduce the time the table is locked because VALIDATE CONSTRAINT requires ACCESS EXCLUSIVE[1] and uses seqscan for check table's rows. P.S. Lucas, If you have not received answers, you can find all of them as a thread by the link: http://www.postgresql.org/message-id/flat/CAE_gQfXTns1FR5Fx9wxpo1oZYwat639ua-gAqWZyNg201HCU=Q@mail.gmail.com P.P.S.: Christophe, Charles! Please, use "Relpy to all" to be sure the sender gets your answers even if he haven't subscribed to the mailing list. [1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html -- Best regards, Vitaly Burovoy
Hello Vitaly > -----Original Message----- > From: Vitaly Burovoy [mailto:vitaly.burovoy@gmail.com] > Sent: Montag, 25. Januar 2016 14:25 > To: Christophe Pettus <xof@thebuild.com>; clavadetscher@swisspug.org > Cc: Postgres General <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2 > > On 1/24/16, Christophe Pettus <xof@thebuild.com> wrote: > > > > On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher > > <clavadetscher@swisspug.org> wrote: > > > >> What is the point of having a check constraint that is not checked? > > > > Well, it *is* checked going into the future; it's just not checked at the > > time the constraint is added. Ultimately, you do want to fix the data, but > > this makes it a two-step process, and reduces the time the table is locked > > against access. > > NOT VALID constraint checks new and updated rows, and gives an extra > time to fix current data and be sure there will be no new rows that > violates the check constraint during and after the fixing process. > > N.B.: Prior 9.4 it does *NOT* reduce the time the table is locked > because VALIDATE CONSTRAINT requires ACCESS EXCLUSIVE[1] and uses > seqscan for check table's rows. > > P.S. Lucas, If you have not received answers, you can find all of them > as a thread by the link: > http://www.postgresql.org/message-id/flat/CAE_gQfXTns1FR5Fx9wxpo1oZYwat639ua-gAqWZyNg201HCU=Q@mail.gmail.com > > P.P.S.: Christophe, Charles! Please, use "Relpy to all" to be sure > the sender gets your answers even if he haven't subscribed to the > mailing list. Oops. Honestly I did not think of that. I will keep that in mind in the future. Thank you for the hint. Charles > > [1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html > -- > Best regards, > Vitaly Burovoy
On Sun, Jan 24, 2016 at 11:12 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote: > So, I made a SELECT to get some data, to see where's the issue: > > SELECT * FROM integrations.accounts WHERE qb_settings IS NULL OR > xero_settings IS NULL OR freshbooks_settings IS NULL OR myob_settings IS > NULL OR ppy_settings IS NULL; If you have a check constraint X, and you want to find all the rows that are currently NOT X, you are using the wrong query here. You need to AND your parts together. Look up DeMorgan's laws for boolean logic.