Data Loss from SQL SELECT (vs. COPY/pg_dump) - Mailing list pgsql-sql

<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>