Postgresql binary copy file format 
Author Message
 Postgresql binary copy file format

We're starting to use Postgresql 7.0.3 on Linux.  We are trying
to decipher the format of the file produced by COPY BINARY.
(I know the format changed in version 7.1; it's not convenient
to upgrade right now.)  We would like to produce binary data files
with another program and let Postgresql ingest them.

So we made a tiny table and dumped it.  We don't know how to
reconcile this with what the document says.

Here's the table:

pln=# \d testing
        Table "testing"
 Attribute |  Type   | Modifier
-----------+---------+----------
 text      | char(8) |
 float1    | float4  |
 integer   | integer |
 float2    | float4  |

pln=# select * from testing;
   text   | float1 | integer | float2
----------+--------+---------+--------
 first    |      1 |       1 |      2
 second   |   3.14 |      -1 |   3.14
 third    |    123 |       5 |    123
(3 rows)

Here's a hex dump of the COPY BINARY output file:
[pln]$ od -x /tmp/testing.dump
0000000 0003 0000 0018 0000 0000 0000 000c 0000
0000020 6966 7372 2074 2020 0000 3f80 0001 0000
0000040 0000 4000 0018 0000 0000 0000 000c 0000
0000060 6573 6f63 646e 2020 f5c3 4048 ffff ffff
0000100 f5c3 4048 0018 0000 0000 0000 000c 0000
0000120 6874 7269 2064 2020 0000 42f6 0005 0000
0000140 0000 42f6
0000144

The first 4 bytes looks OK.  There are 3 rows in the table,
and an int32 value says so.  Then there is a pattern of
32 bytes, repeated three times for the three rows.  The
20 bytes of data can be seen in the last 20 bytes of each
32.

The trouble comes in interpreting the 12 bytes before
the row data.  According to the document this should be
three int32's:
First, the total length of the tuple data.  This seems to be
24 (18 hex).  Where does this come from?  We have 20 bytes
of data.
Second, the OID.  This is always 0, which is OK.  I didn't
ask for it.
Third, the number of null attributes.  This is 12 (c hex).
I don't understand this at all.  What's going on?

The document also says that after these three integers comes
a list of attribute numbers of attributes.  That also has us
stumped.  What is it?  It doesn't seem to be here in this
little example.  What would make it appear?

Note:  Our Borders bookstore has 5 books on Postgresql.  Only 2
mention this file format at all.  Neither of them is any more
comprehensible than the electronic document.  I see a niche for
a HOWTO document.

--
*   Patrick L. Nolan                                          *



Sat, 31 Jul 2004 06:17:40 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. COPY BINARY file format proposal

2. COPY BINARY file format proposal

3. COPY BINARY file format proposal

4. Decoding the binary format of Ingres copy-out data

5. DLL to copy binary files into SQL Server as a stored procedure

6. help info for binary file format for sql micrisoft

7. VB Binary File Format

8. HLP: Copying binary file to field

9. Oracle export file binary format

10. Informix v2.x binary file format

11. store chr(0) in database or microsoft binary file format

12. bulk copy using format file


 
Powered by phpBB® Forum Software