PostgreSQL: foreign table and discarded records

Question

where PostgreSQL stores records which were discarded from the foreign table during select? I have following table:

CREATE FOREIGN TABLE ext.alternatenamesext (
  altid BIGINT,
  geoid BIGINT,
  isolanguage VARCHAR(7),
  alternatename TEXT,
  ispreferredname INTEGER,
  isshortname INTEGER,
  iscolloquial INTEGER,
  ishistoric INTEGER
) 
SERVER edrive_server
OPTIONS (
  delimiter E'\t',
  encoding 'UTF-8',
  filename '/mnt/storage/edrive/data/alternateNames.txt',
  format 'csv');

alternateNames.txt contains ~11 mln records. But when I do "SELECT * FROM ext.alternatenamesext" it returns only ~9.5mln records. Where the rest of 2mln are? Is there a way to put them into the separate file, like Oracle's sql*ldr?

I don't think it "supresses" rows - probably parsing csv wrong - check rows where it starts skipping I'd say — Vao Tsun, Jun 11 '16 at 12:43
note: some CSV records can be several line long. if your data is corrupt this might happen where it is not desired. — Jasen, Jun 11 '16 at 13:15

score 0 · Accepted Answer · answered Jun 15 '16 at 19:20

Problem has been solved by the following syntax of CREATE FOREIGN TABLE...:

CREATE FOREIGN TABLE ext.alternatenamesext (
  altid BIGINT,
  geoid BIGINT,
  isolanguage VARCHAR(7),
  alternatename VARCHAR(400),
  isPreferredName INT,
  isShortName INT,
  isColloquial INT,
  isHistoric INT
)
SERVER edrive_server
OPTIONS (
  delimiter E'\t',
  encoding 'UTF-8',
  filename '/mnt/storage/edrive/data/alternateNames.txt',
  format 'text',  -- not 'csv'!
  null '');  -- eliminate null values (some kind of TRAILING NULLCOLLS in Oracle I guess)

PostgreSQL: foreign table and discarded records

1 Answers1