Re: COPY FROM wish list

Marc Mamin wrote:
> Hello,
>
> Looking at the TODO List, I feel that only some aspects of the COPY FROM
> command are adressed.
> Could a discussion trigger some activity on this topic :o) ?
>
> Best regards,
>
> Marc Mamin
>
>
> Here my wish list:
>
> COPY tablename [ ( column [, ...] ) ]
> FROM { 'filename' | STDIN }
> [ [ WITH ]
>
> [ CHECKONLY (DATA_TYPES,CONSTRAINTS) ] # do not import, just
> check the content
>
> [ SKIPLINES (n) ]
>
> [ HEADERLINE (n) ] # line conlaining the column names, must be
> within the line to skip.
> # must match the column list when both are
> given
> [ DEFERCONSTRAINTS ]
>
> [ SKIPINVALID [FILE file ] SKIPLIMIT (n)] # when set,
> invalid lines are skipped and possibly stored in file
> # an exception is
> triggered when more than SKIPLIMIT lines are found
>
>
>
> [ NULLREPLACEMENT (column =value[, ...] ) ] # allow a per column
> handling of null values.
> # cannot be set
> allong with NULL [ AS ]
> # not usable for
> columns that are missing in the input file ?
>
> [ FEEDBACK (n) ] # display progress every n rows .# for
> ORACLE users...
>
> [ COMMIT (n) ] # just an idea: commit each n rows....
>
> [
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ NULL [ AS ] 'null string' ]
> [ CSV [ HEADER ]
> [ QUOTE [ AS ] 'quote' ]
> [ ESCAPE [ AS ] 'escape' ]
> [ FORCE NOT NULL column [, ...] ]
> ]
>

Looks like you are requesting sql*loader. My opinion is that we should
keep COPY simple, uncluttered and fast. And instead have a preprocessor
that can do all of the transforms, skipping, checking and logging.

PgFoundry has http://pgfoundry.org/projects/pgloader/
It is a step in the right direction but definitely not as powerful as
sql*loader.

I've been writing a Postgres equivalent that does Oracle SQL*Loader/DB2
Load style import, and external tables using CSV, fixed with and XML
data sources. But its not production ready. I'm hoping SQL/MED makes it
in to the next release of Postgres so I can throw it all away :)

Scott