Обсуждение: Combining several CTEs with a recursive CTE
Hello all, this is more a "just curious" question, rather than a real world problem. We can combine several CTEs into a single select using something like this: WITH cte_1 as ( select .... ), cte_2 as ( select ... where id (select some_col from cte_1) ) select * from cte_2; But this does not seem to work when a recursive CTE is involved WITH cte_1 as ( select .... ), recursive cte_2 as ( select ... where id (select some_col from cte_1) union all select ... ) select * from cte_2; This throws an error: syntax error at or near "cte_2" I'm just wondering if this is intended behavioury, simply not (yet) implemented or even invalid according to the standard?I didn't find any reference that it's not allowed in the manual. Regards Thomas
I'm just wondering if this is intended behavioury, simply not (yet) implemented or even invalid according to the standard? I didn't find any reference that it's not allowed in the manual.
Regards
Thomas
David Johnston, 20.09.2011 16:15: >> I'm just wondering if this is intended behavioury, simply not (yet) >> implemented or even invalid according to the standard? I didn't >> find any reference that it's not allowed in the manual. >> >> Regards Thomas >> >> > Try sticking the recursive keyword after the "with" if any of the > following CTEs are recursive. > > WITH RECURSIVE normal 1 AS () ,recursine1 AS () ,normal2 AS () > ,recursine2 AS () SELECT ... > > David J. Ah! That does the trick. Thanks Thomas
Hi All, I have a requirement where my application tries to enter the duplicate row in table using batchexceute code. And batch exceptionis thrown we checked error code and skip it but after this exception all my next update/insert gets error out withfollowing exception "Database error. SQL state 25P02. Database specific error code (if any) was 0. Database error message (if any) was: org.postgresql.util.PSQLException:ERROR: current transaction is aborted, commands ignored until end of transaction block.: Is there any way to proceed ahead like in sql server we have options while creating table IGNORE_DUP_KEY = ON if this isset warning is generated instead of Exception so the other insert/update can proceed ahead.
Hi<br /><br />There is no IGNORE_DUP_KEY equivalent in PostgreSQL.<br /><br />If you are 100% sure that you want to ignoreunique key violations, you can wrap your INSERT code in PL/PgSQL block and handle the exception yourself.<br /><br/>I mean:<br /><br />DO $$<br />BEGIN<br /> INSERT INTO foo (bar,baz) SELECT 42, 666;<br /> EXCEPTION WHEN unique_violationTHEN RAISE NOTICE 'row skipped';<br />END;<br />$$ <br /><br /><br />BTW - which version of PostgreSQL areyou using?<br /><br />Filip<br /><br /><br /><br /><div class="gmail_quote">2011/9/21 Amar Dhole <span dir="ltr"><<ahref="mailto:adhole@tibco.com">adhole@tibco.com</a>></span><br /><blockquote class="gmail_quote" style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> Hi All,<br /><br /> I have a requirement where myapplication tries to enter the duplicate row in table using batchexceute code. And batch exception is thrown we checkederror code and skip it but after this exception all my next update/insert gets error out with following exception<br/><br /> "Database error. SQL state 25P02. Database specific error code (if any) was 0. Database error message(if any) was: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until endof transaction block.:<br /><br /><br /> Is there any way to proceed ahead like in sql server we have options while creatingtable IGNORE_DUP_KEY = ON if this is set warning is generated instead of Exception so the other insert/update canproceed ahead.<br /><font color="#888888"><br /><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/>
Hi Filip,
No not sure 100% when this can happen. This approach will not be possible as in our application we are programmatically handling these cases and going in other route to add the record with increased key.
I am using 9.0.4 version.
Thanks
Amar
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Filip Rembialkowski
Sent: 21 September 2011 20:37
To: Amar Dhole
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] handling duplicate row exception
Hi
There is no IGNORE_DUP_KEY equivalent in PostgreSQL.
If you are 100% sure that you want to ignore unique key violations, you can wrap your INSERT code in PL/PgSQL block and handle the exception yourself.
I mean:
DO $$
BEGIN
INSERT INTO foo (bar,baz) SELECT 42, 666;
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'row skipped';
END;
$$
BTW - which version of PostgreSQL are you using?
Filip
2011/9/21 Amar Dhole <adhole@tibco.com>
Hi All,
I have a requirement where my application tries to enter the duplicate row in table using batchexceute code. And batch exception is thrown we checked error code and skip it but after this exception all my next update/insert gets error out with following exception
"Database error. SQL state 25P02. Database specific error code (if any) was 0. Database error message (if any) was: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block.:
Is there any way to proceed ahead like in sql server we have options while creating table IGNORE_DUP_KEY = ON if this is set warning is generated instead of Exception so the other insert/update can proceed ahead.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Hi Filip,
No not sure 100% when this can happen. This approach will not be possible as in our application we are programmatically handling these cases and going in other route to add the record with increased key.
I am almost sure this approach will be possible, for more details see:
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
I am using 9.0.4 version.
Thanks
Amar