Subject: Re: Exception handling in Foreign table
To: Gaurav Tomar <gauravtomar14@gmail.com>, pgsql-sql@postgresql.org
References: 
 <CAByYU3QW=LfWHy4B8LAuFqomnO_WB0vrHx0Cn3u05PQrOaTpSQ@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <4c9e73a2-ab89-1d9b-5d51-bddbce04b9d3@aklaver.com>
Date: Wed, 6 Jul 2016 10:35:42 -0700
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:45.0) Gecko/20100101
 Thunderbird/45.1.1
MIME-Version: 1.0
In-Reply-To: 
 <CAByYU3QW=LfWHy4B8LAuFqomnO_WB0vrHx0Cn3u05PQrOaTpSQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql