Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Vick Khera]

On Sun, Jan 24, 2016 at 11:12 PM, drum.lu...@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.


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

Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Vitaly Burovoy]

On 1/24/16, Christophe Pettus  wrote:
>
> On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher
>  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


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

Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Charles Clavadetscher]

Hello Vitaly

> -Original Message-
> From: Vitaly Burovoy [mailto:vitaly.buro...@gmail.com]
> Sent: Montag, 25. Januar 2016 14:25
> To: Christophe Pettus <x...@thebuild.com>; clavadetsc...@swisspug.org
> Cc: Postgres General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
> 
> On 1/24/16, Christophe Pettus <x...@thebuild.com> wrote:
> >
> > On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher
> > <clavadetsc...@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



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

Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Christophe Pettus]

On Jan 24, 2016, at 8:17 PM, Christophe Pettus  wrote:
> 2. Use the NOT VALID option on ALTER TABLE ... ADD constraint, which allows 
> the addition of a constraint without actually checking its validity.

And note that you might miss some potential planner optimizations this way, as 
the planner will not assume the constraint holds true unless you remove the NOT 
VALID condition from it with VALIDATE CONSTRAINT.

--
-- Christophe Pettus
   x...@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

Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Charles Clavadetscher]

Hello

Althought both options are technically correct, I guess that the first one is 
the only reasonable one. What is the point of 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 point in 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 extend the
constraints to include the point in time from which it must apply.

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@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.lu...@gmail.com" <drum.lu...@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
>x...@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



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

Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Christophe Pettus]

On Jan 24, 2016, at 8:12 PM, "drum.lu...@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
   x...@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

Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2

[Christophe Pettus]

On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher  
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.

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