Postgres Pro
Downloads
Home   >   mailing lists

Exception handling in Foreign table - Mailing list pgsql-sql

From Gaurav Tomar
Subject Exception handling in Foreign table
Date
Msg-id CAByYU3QW=LfWHy4B8LAuFqomnO_WB0vrHx0Cn3u05PQrOaTpSQ@mail.gmail.com
Whole thread Raw
Responses Re: Exception handling in Foreign table  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-sql
Tree view
HI,

I have created foreign table to read the data from the CSV file which is | separated.
while reading the data from foreign table if there is any error in any row of the file the data is not getting loaded. I want to avoid that by skiping that row and continue with the next row.
How can i achieve this.

The table structure is as follows:

CREATE FOREIGN TABLE location_ext
(
id                     VARCHAR(80),
name                   VARCHAR(200),
description               VARCHAR(400)
) SERVER file_server
OPTIONS ( filename '/home/ubuntu/location.txt', FORMAT 'csv', delimiter '|', header 'true' );

and sample data is

id|name|description
1|South Wing, second floor|Second floor of the Old South Wing, formerly in use by Psychiatry
2|South Wing Neuro OR 1 Old South Wing, Neuro Radiology Operation Room 1 on second floor
3|Patient's Home|Patient's Home

if there is an error in 2 row than while reading the data it should skip it and load the 1 and 3 row.
I also want to capture the error row (2 row) as an exception. 

Regards,

Gaurav

pgsql-sql by date:

Previous
From: Steve Midgley
Date:
Subject: Re: missing data for column
Next
From: Adrian Klaver
Date:
Subject: Re: Exception handling in Foreign table