Thread: Exception handling in Foreign table

On 07/06/2016 12:39 AM, Gaurav Tomar wrote:
> 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.

I don't think that is possible. Just like COPY it is all or nothing.

>
> Regards,
>
> Gaurav
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com