As a minor note,
COPY .. (WITH BINARY) doesn't have brackets. It's the flags (which represent amongst other things the brackets).
COPY ... (WITH BINARY)
From the docs on
To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the
*recv functions for each column's data type (typically these functions are found in the
src/backend/utils/adt/ directory of the source distribution).
Further the docs say binary format (currently) has
- 11 bytes signature
- 4 bytes for flags
- 4 byte potential variable-width field, currently not in use so we skip the size (4 byte
\0\0\0\0) this technically not nice. If these four bytes had 15, we'd have to skip over not just the four, but an addition 15.
Then the tuple has
Then the fields have
- 4 byte length qualifier followed by that many bytes of field data. (Which we already in the case of
So essentially we skip 25 bytes to get to the first column
So it's in the format specified by
range_send You can see that explained a bit below in the comments above
Binary representation: The first byte is the flags, then the lower bound (if present), then the upper bound (if present). Each bound is represented by a 4-byte length header and the binary representation of that bound (as returned by a call to the send function for the subtype).
In your case, that subtype is timestamp and the send is
You can see a timestamp is stored as 8 bytes, and that's just sent with a simple
pq_sendint64 (a 64 bit/8 byte int). You'll have to read how
timestamp_recv works to see how you should handle a binary representation of a timestamp. Hint: it gets into a struct for in-memory representation in
* Convert timestamp data type to POSIX time structure.
* Note that year is _not_ 1900-based, but is an explicit full value.
* Also, month is one-based, _not_ zero-based.
* 0 on success
* -1 on out of range
I'm not going to more-entertain this here, but maybe next go around.
We try first
DEADBEEFin it which an isolation to track the marker 8 byte marker.
psql -d test -c 'COPY ( SELECT E'\''DEADBEEF'\'' ) TO STDOUT WITH ( FORMAT BINARY );' |
od --skip-bytes=25 --endian big --read-bytes=8 -c
Now we swap that out..
psql -d test -c 'COPY ( SELECT $$2010-01-01 14:30$$::timestamp without time zone ) TO STDOUT WITH ( FORMAT BINARY );' |
od --skip-bytes=25 --endian big --read-bytes=8 --format=d8 -x
Result: paren-comments added.
0000031 315671400000000 (timestamp in int8)
0001 1f19 f9a9 aa00 (hex representation)
And that's your number for the first time zone. For
tsrange as per the above section we have
- one byte for on the interval
- upper: 4 bytes (header) + 8 bytes (timestamp)
- lower: 4 bytes (header) + 8 bytes (timestamp)
So to access the first internal timestamp, we skip an addition 5 bytes, on top of already 25 bytes of skipage for 30 bytes total.
psql -d test -c 'COPY ( SELECT $$[2010-01-01 14:30, 2010-01-01 15:30)$$::tsrange ) TO STDOUT WITH ( FORMAT BINARY );' |
od --skip-bytes=30 --endian big --read-bytes=8 --format=d8 -x
This gives us the same result as above..
0001 1f19 f9a9 aa00
--skip-bytes to 42 to skip over that 8 byte time stamp, and the next 4 bytes of header for the
lower and you'll get another time stamp.