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