Thread: COPY command & binary format

<div class="moz-cite-prefix">Il 10/05/2016 12:56, Nicolas Paris ha scritto:<br /></div><blockquote
cite="mid:CA+ssMORTVs9cnvaS6wOOuPcmmsT5MJ6sfDYs9h3peXqW4zOA5A@mail.gmail.com"type="cite"><div dir="ltr"><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">Hello,<br /><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">What is the way to build a binary format
(insteadof a csv) ? Is there specification for this file ?<br /><a
href="http://www.postgresql.org/docs/9.5/static/sql-copy.html"
moz-do-not-send="true">http://www.postgresql.org/docs/9.5/static/sql-copy.html</a><br/></div></div></blockquote> I
alwayscreate binary files with<br /> COPY table TO 'path/to/file' WITH BINARY<br /><br /> Cheers<br /> Moreno.-<br /> 

Hello,

What is the way to build a binary format (instead of a csv) ? Is there specification for this file ?
http://www.postgresql.org/docs/9.5/static/sql-copy.html

Could I create such format from java ?

I guess this would be far faster, and maybe safer than CSVs

Thanks by advance,

Il 10/05/2016 13:38, Nicolas Paris ha
      scritto:

    <blockquote
cite="mid:CA+ssMOSVC-Yu+yQXeLHL3c0+iz4g1PEtRM49cVvQxyNFN3d4sA@mail.gmail.com"
      type="cite">

        2016-05-10 13:04 GMT+02:00 Moreno
          Andreo <<a moz-do-not-send="true"
              href="mailto:moreno.andreo@evolu-s.it" target="_blank">moreno.andreo@evolu-s.it>:

            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">

                  Il 10/05/2016 12:56, Nicolas Paris ha scritto:



                      <div
                        style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">Hello,


                      <div
                        style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">What

                        is the way to build a binary format (instead of
                        a csv) ? Is there specification for this file ?
                        <a moz-do-not-send="true"
                          href="http://www.postgresql.org/docs/9.5/static/sql-copy.html"
                          target="_blank">http://www.postgresql.org/docs/9.5/static/sql-copy.html



                 I always create binary files with
                COPY table TO 'path/to/file' WITH BINARY



             

          <div class="gmail_default"
            style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">​
            Fine, this works in this way :
            postgresql -> binary

          <div class="gmail_default"
            style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">binary
            -> postgresql


          <div class="gmail_default"
            style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">The
            way I want is :

          <div class="gmail_default"
            style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">csv
            -> binary -> postgresql




    Is this just to be quicker or are you going to add some business
    logic while converting CSV data?
    As you mentioned ETL, I assume the second, as I don't think that
    converting CSV to binary and then loading it to PostgreSQL will be
    more convenient than loading directly from CSV... as quicker as it
    can be, you have anyway to load data from CSV.

    Binary file format is briefly described in the last part of the doc
    you linked, under "Binary format", and there's also reference to
    source files.
    <blockquote
cite="mid:CA+ssMOSVC-Yu+yQXeLHL3c0+iz4g1PEtRM49cVvQxyNFN3d4sA@mail.gmail.com"
      type="cite">


          <div class="gmail_default"
            style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">

          <div class="gmail_default"
            style="font-family:tahoma,sans-serif;color:rgb(0,51,51)">And
            if possible, transforming csv to binary throught java​.




    This is beyond my knowledge, ATM. I'm just starting with Java and
    JDBC is still in the TODO list, sorry... :-)

    Cheers
    Moreno.-

On Tue, May 10, 2016 at 01:38:12PM +0200, Nicolas Paris wrote:
> The way I want is :
> csv -> binary -> postgresql
>
> And if possible, transforming csv to binary throught java​.
>
> Use case is ETL process.

Not sure what the point would be tbh if the data is already in CSV.
You might aswell submit the CSV to postgres and let it deal with it.
It'll probably be faster. It'll also be more portable. The BINARY
format is what Postgres uses internally (more or less). I had to
look at the source code to figure out how to insert a timestamp
(FYI: Postgres stores timestamps as epoch based off the year 2000 not
1970 amongst other fun things).


--
  "A search of his car uncovered pornography, a homemade sex aid, women's
  stockings and a Jack Russell terrier."
    - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480

On Tue, May 10, 2016 at 03:00:55PM +0200, Nicolas Paris wrote:
> > The way I want is :
> > csv -> binary -> postgresql
> >
> > Is this just to be quicker or are you going to add some business logic
> > while converting CSV data?
> > As you mentioned ETL, I assume the second, as I don't think that
> > converting CSV to binary and then loading it to PostgreSQL will be more
> > convenient than loading directly from CSV... as quicker as it can be, you
> > have anyway to load data from CSV.
> >
> ​
> Right, ETL process means huge business logic.
> get the data (csv or other) -> transform it -> produce a binary -> copy
> from binary from stdin ​
>
> Producing 100GO CSVs, is a waste of time.

Ah. You need to fiddle with the data. Then you need to weigh the pros of
something agnostic to Postgres's internals to something that needs to be
aware of them.

You will need to delve into the source code for data types more complex
than INTEGER, TEXT and BYTEA (which was the majority of my data when I
was just looking into it).

--
  "A search of his car uncovered pornography, a homemade sex aid, women's
  stockings and a Jack Russell terrier."
    - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1111118083480