Thread: Writeable CTE Not Working?

<div dir="ltr"> Can someone explain how this writable CTE works?  Or does it not?<br /><br />What I tried to do was to
makethose non-null/non-empty values of suppliers.suppliercode unique by (1) nullifying any blank, but non-null,
suppliercode,then (2) appending the supplierid values to the suppliercode values for those duplicates.  The writeable
CTE,upd_code, did not appear to work, allowing the final UPDATE statement to, unexpectedly, fill what used to be empty
valueswith '-'||suppliercode.<br /><br />WITH upd_code AS (<br />  UPDATE suppliers SET suppliercode = NULL <br /> 
WHEREsuppliercode IS NOT NULL <br />  AND length(trim(suppliercode)) = 0<br />)<br />, ranked_on_code AS (<br /> 
SELECTsupplierid<br />  , trim(suppliercode)||'-'||supplierid AS new_code<br />  , rank() OVER (PARTITION BY
upper(trim(suppliercode))ORDER BY supplierid)<br />  FROM suppliers<br />  WHERE suppliercode IS NOT NULL<br />  AND
NOTinactive AND type != 'car'<br />)<br />UPDATE suppliers<br />SET suppliercode = new_code<br />FROM ranked_on_code<br
/>WHEREsuppliers.supplierid = ranked_on_code.supplierid<br />AND rank > 1;<br /><br />I have seen similar behavior
inthe past and could not explain it.  Any explanation is much appreciated.<br />Thanks,<br />-Kong<br /></div> 

2013/1/29 Kong Man <kong_mansatiansin@hotmail.com>:
> Can someone explain how this writable CTE works?  Or does it not?

They surely do, I use this feature a lot.
Take a look at the description in the docs:
http://www.postgresql.org/docs/current/interactive/queries-with.html#QUERIES-WITH-MODIFYING


> WITH upd_code AS (
>   UPDATE suppliers SET suppliercode = NULL
>   WHERE suppliercode IS NOT NULL
>   AND length(trim(suppliercode)) = 0
> )
> , ranked_on_code AS (
>   SELECT supplierid
>   , trim(suppliercode)||'-'||supplierid AS new_code
>   , rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplierid)
>   FROM suppliers
>   WHERE suppliercode IS NOT NULL
>   AND NOT inactive AND type != 'car'
> )
> UPDATE suppliers
> SET suppliercode = new_code
> FROM ranked_on_code
> WHERE suppliers.supplierid = ranked_on_code.supplierid
> AND rank > 1;

I see 2 problems with this query:
1) CTE is just a named subquery, in your query I see no reference to
the “upd_code” CTE.  Therefore it is never gets called;
2) In order to get data-modifying CTE to return anything, you should
use RETURNING clause,   simplest form would be just RETURNING *

Hope this helps.

--
Victor Y. Yegorov

<div dir="ltr"> Hi Victor,<br /><br />> I see 2 problems with this query:<br />> 1) CTE is just a named subquery,
inyour query I see no reference to<br />> the “upd_code” CTE.<br />> Therefore it is never gets called;<br /><br
/>So,in conclusion, my misconception about CTE in general was that all CTE get called without being referenced.<br
/><br/>Thank you much for the explanation.  <br />-Kong<br /><br /></div> 

Kong Man <kong_mansatiansin@hotmail.com> writes:
> Hi Victor,
>> I see 2 problems with this query:
>> 1) CTE is just a named subquery, in your query I see no reference to
>> the �upd_code� CTE.
>> Therefore it is never gets called;

> So, in conclusion, my misconception about CTE in general was that all CTE get called without being referenced.

I think this explanation is wrong --- if you run the query with EXPLAIN
ANALYZE, you can see from the rowcounts that the writable CTE *does* get
run to completion, as indeed is stated to be the behavior in the fine
manual.

However, for a case like this where the main query isn't reading from
the CTE, the CTE will get cycled to completion after the main query is
done.  I think what is happening is that the main query is updating all
the rows in the table, and then when the CTE comes along it thinks the
rows are already updated in the current command, so it doesn't replace
'em a second time.  This is a consequence of the fact that the same
command-counter ID is used throughout the query.  My recollection is
that that choice was intentional and that doing it differently would
break use-cases that are less outlandish than this one.  I don't recall
specific examples though.

Why are you trying to update the same table in two different parts of
this query, anyway?  The best you can really hope for with that is
unspecified behavior --- we will surely not promise that one of them
completes before the other starts, so in general there's no way to be
sure which one would process a particular row first.
        regards, tom lane

<div dir="ltr">I am troubled to find out that a SELECT statement produces fewer rows than the actual row count and have
notbeen able to answer myself as to why.  I hope someone could help shedding some light to this.<br /><br />I attempted
togenerate a set of INSERT statements, using a the following SELECT statement, against my translations data to reuse
elsewhere,but then realized the row count was 8 rows fewer than the source of 2,178.  COPY and pg_dump don't seem to
loseany data.  So, I compare the results to identify the missing data as follows.  I don't even see any strange
encodingin those missing data.<br /><br />What scenario could have caused my SELECT query to dump out the 8 blank rows,
insteadof the expected data?<br /><br />Here is how I find the discrepancy:<br
/>===============================================================================<br/>$ psql -c "CREATE TABLE
new_translationAS<br />  SELECT display_name, name, type, translation<br />  FROM translations t JOIN lang l USING
(langid)<br/>  WHERE display_name = 'SPANISH_CORP'<br />  ORDER BY display_name, name"<br />SELECT 2178<br /><br />$
psql-tAc "SELECT<br /> 'INSERT INTO new_translation VALUES ('<br />     ||quote_literal(display_name)||<br /> ',
'||quote_literal(name)||<br/> ', '||quote_literal(type)||<br /> ', '||quote_literal(translation)||');'<br />FROM
new_translation<br/>ORDER BY display_name, name" >/tmp/new_translation-select.sql <br /><br />$ pg_dump --data-only
--inserts--table=new_translation clubpremier |<br />  sed -n '/^INSERT/,/^$/p' >/tmp/new_translation-pg_dump.sql<br
/><br/>$ grep ^INSERT /tmp/new_translation-pg_dump.sql | wc -l<br />2178<br /><br />$ grep ^INSERT
/tmp/new_translation-select.sql| wc -l<br />2170<br /><br />$ diff /tmp/new_translation-select.sql
/tmp/new_translation-pg_dump.sql<br/>27c27<br />< <br />---<br />> INSERT INTO new_translation VALUES
('SPANISH_CORP','AGENCY_IN_USE_BY_COBRAND', NULL, 'La cuenta no puede ser eliminada porque está siendo utilizada
actualmentepor la co-marca #cobrand#');<br />506c506<br />< <br />---<br />> INSERT INTO new_translation VALUES
('SPANISH_CORP','CAR_DISTANCE_UNIT', NULL, 'MILLAS');<br />1115c1115<br />< <br />---<br />> INSERT INTO
new_translationVALUES ('SPANISH_CORP', 'HOTEL_PROMO_TEXT', 'label', NULL);<br />1131,1134c1131,1134<br />< <br
/><<br />< <br />< <br />---<br />> INSERT INTO new_translation VALUES ('SPANISH_CORP',
'INSURANCE_SEARCH_ADVERTISEMENT_SECTION_ONE','checkout', NULL);<br />> INSERT INTO new_translation VALUES
('SPANISH_CORP','INSURANCE_SEARCH_ADVERTISEMENT_SECTION_THREE', 'checkout', NULL);<br />> INSERT INTO
new_translationVALUES ('SPANISH_CORP', 'INSURANCE_SEARCH_ADVERTISEMENT_SECTION_TWO', 'checkout', NULL);<br />>
INSERTINTO new_translation VALUES ('SPANISH_CORP', 'INSURANCE_SEARCH_FOOTER', 'checkout', NULL);<br />1615c1615<br
/><<br />---<br />> INSERT INTO new_translation VALUES ('SPANISH_CORP', 'PAGE_FORGOT_PASSWORD', 'page_titles',
NULL);<br/>2215a2216<br />> <br />===============================================================================<br
/><br/>Thank you in advance for your help,<br />-Kong<br /></div> 

<div dir="ltr">This seems to answer my question.  I completely forgot about the behavior of NULL  value in the text
concatenation.<br/><br /><p class="MsoNormal"><a
href="http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE">http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE</a><p
class="MsoNormal"> <pclass="MsoNormal"><span style="font-size:9.0pt;font-family:"Verdana","sans-serif"; 
color:black;background:white">Because<span class="apple-converted-space"> </span></span><code><span
style="font-size:9.0pt;color:black;background:white">quote_literal</span></code><span
class="apple-converted-space"><spanstyle="font-size:9.0pt;font-family:"Verdana","sans-serif"; 
color:black;background:white"> </span></span><span style="font-size:9.0pt;
font-family:"Verdana","sans-serif";color:black;background:white">is labelled<span
class="apple-converted-space"> </span></span><tt><spanstyle="font-size: 
9.0pt;color:black;background:white">STRICT</span></tt><span style="font-size:
9.0pt;font-family:"Verdana","sans-serif";color:black;background:white">, it will always return null when called with a
nullargument. In the above example, if<span class="apple-converted-space"> </span></span><tt><span
style="font-size:9.0pt;color:black;background:white">newvalue</span></tt><spanclass="apple-converted-space"><span
style="font-size:9.0pt;font-family:"Verdana","sans-serif";
color:black;background:white"> </span></span><span style="font-size:9.0pt;
font-family:"Verdana","sans-serif";color:black;background:white">or<span
class="apple-converted-space"> </span></span><tt><spanstyle="font-size: 
9.0pt;color:black;background:white">keyvalue</span></tt><span class="apple-converted-space"><span
style="font-size:9.0pt;font-family:"Verdana","sans-serif";
color:black;background:white"> </span></span><span style="font-size:9.0pt;
font-family:"Verdana","sans-serif";color:black;background:white">were null, the entire dynamic query string would
becomenull, leading to an error from<span class="apple-converted-space"> </span></span><tt><span style="font-size: 
9.0pt;color:black;background:white">EXECUTE</span></tt><span style="font-size:
9.0pt;font-family:"Verdana","sans-serif";color:black;background:white">. You can avoid this problem by using the<span
class="apple-converted-space"> </span></span><code><span
style="font-size:9.0pt;color:black;background:white">quote_nullable</span></code><span
class="apple-converted-space"><spanstyle="font-size:9.0pt;font-family:"Verdana","sans-serif"; 
color:black;background:white"> </span></span><span style="font-size:9.0pt;
font-family:"Verdana","sans-serif";color:black;background:white">function, which works the same as<span
class="apple-converted-space"> </span></span><code><span
style="font-size:9.0pt;color:black;background:white">quote_literal</span></code><span
class="apple-converted-space"><spanstyle="font-size:9.0pt;font-family:"Verdana","sans-serif"; 
color:black;background:white"> </span></span><span style="font-size:9.0pt;
font-family:"Verdana","sans-serif";color:black;background:white">except that when called with a null argument it
returnsthe string<span class="apple-converted-space"> </span></span><tt><span style="font-size: 
9.0pt;color:black;background:white">NULL</span></tt><span style="font-size:
9.0pt;font-family:"Verdana","sans-serif";color:black;background:white">. For example,</span><span
style="color:#1F497D"></span><br/><br /></div>