Check constraints do not seem to be working!!!

Consider this table definition: create table t ( i serial, b bool, c bool, constraint b_c check ( (b = true and c is not null ) or (b is distinct from true and c is null) ) constraint b_c check ( (b = true and c is not null )

Re: Check constraints do not seem to be working!!!

, wrote: > On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal > wrote: > >> Despite the above two constraints, the following rows get into the table: >> insert into t (b , c) values (null, true), (null, false); >> > > This behavior is described in the docs > htt

Re: Check constraints do not seem to be working!!!

Thanks Tomas Understood... My bad Was just not looking at that aspect Thanks once again, Regards, Jitendra On Wed, 11 Nov 2020 at 16:17, Tomas Vondra wrote: > > On 11/11/20 10:06 AM, Jitendra Loyal wrote: > > Thanks Nikolay > > > > I read that but is there

Re: Check constraints do not seem to be working!!!

Thanks Laurenz This is interesting...b is True Thanks and regards, Jitendra On Wed 11 Nov, 2020, 22:52 Laurenz Albe, wrote: > On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote: > > you may do this, for example: > > > > (b it not null and b = true) and (c is not null) > > > > Or somethi

BEFORE ... Statement-level trigger

The behaviour is not clear for BEFORE Statement-level Trigger. This is because transition tables cannot be used. So how does one get access to the rows being affected? It is not documented either. Thanks Jiten

Statement-level trigger results in recursion

The AFTER Statement-level Trigger runs into infinite execution when another set of rows are affected for the same table through this trigger. Consider this use case where a table storage_locations that manages a hierarchy of storage_locations in stores, and thus having following columns (for simpli

Re: BEFORE ... Statement-level trigger

Thanks Adrian I am trying to understand as to how a BEFORE statement-level trigger can be used. Since it is a trigger, one needs to know which rows are being affected. Regards, Jiten On Mon 18 Feb, 2019, 9:42 PM Adrian Klaver, wrote: > On 2/18/19 4:06 AM, Jitendra Loyal wrote: >

Re: Statement-level trigger results in recursion

Unfortunately no! Where can I see those? Will I don't my answer there; I have referred to the documentation and tried various things. Thanks and regards, Ken i On Mon 18 Feb, 2019, 9:59 PM Adrian Klaver, wrote: > On 2/18/19 8:23 AM, Jitendra Loyal wrote: > > My bad! > > &g

Re: Statement-level trigger results in recursion

I do understand that the statement level trigger will be executed once before the operation. My point is.. if one does not know the rows, what kind of use it can be put to. What is the use case? Like in after triggers, one gets the rows in transition tables, how does one do with vefore trigger. Th

Re: Statement-level trigger results in recursion

(); Thanks and regards, Jiten On Mon 18 Feb, 2019, 9:32 PM Adrian Klaver, wrote: > On 2/18/19 4:11 AM, Jitendra Loyal wrote: > > > > The AFTER Statement-level Trigger runs into infinite execution when > > another set of rows are affected for the same table through this >

Re: BEFORE ... Statement-level trigger

I have gone through the documentation quite a number of times to establish the understanding. However, I had been wondering about the recursion in the case I put forth. Is there a better way to handle this requirement? The point is that the trigger is being called when no rows are affected. Thank

Re: Statement-level trigger results in recursion

Thanks for all your efforts. I appreciate it. Let us wait and see if someone can enlighten us, or you locate the conversation. Thanks once again Regards, Jiten On Tue 19 Feb, 2019, 3:19 AM Adrian Klaver, wrote: > On 2/18/19 9:07 AM, Jitendra Loyal wrote: > > I do understand

Re: BEFORE ... Statement-level trigger

I will not prefer to use a row trigger on this case for that will be relatively inefficient. So can we conclude that a sanity check is essential when using statement level trigger. Thanks and regards, Jiten

RECURSIVE allowed only ONCE in a CTE

RECURSIVE) On the second use, I get syntax error. Kindly confirm that RECURSIVE can be used only once. I will find an alternate mechanism. Thanks, Jitendra Loyal

Re: RECURSIVE allowed only ONCE in a CTE

Thanks Andrew I will try this and revert; I was specifying RECURSIVE for the second CTE as well. Regards, Jitendra

Re: RECURSIVE allowed only ONCE in a CTE

Thanks Got it Regards, Jitendra On Fri 22 Feb, 2019, 4:03 PM mariusz On Fri, 22 Feb 2019 13:23:11 +0530 > Jitendra Loyal wrote: > > > I find that the RECURSIVE can be used only once in a CTE. > > > > I have the following use-case where there is a hierarchy of &g

Use case for BEFORE STATEMENT level trigger

I had been wondering as to where one can use BEFORE STATEMENT level trigger, more so because one does not know (access) what rows are getting affected. Only thing which comes to my mind is that if one wants to do something at a table-level, then this trigger can be used; this is quite unusual thoug