Loading a NATIVE file into a table: example

The example below demonstrates creating a table and loading a NATIVE file that contains a single row of data.

The example below demonstrates creating a table and loading a NATIVE file that contains a single row of data. The table contains all possible data types.

=> CREATE TABLE allTypes (INTCOL INTEGER,
                          FLOATCOL FLOAT,
                          CHARCOL CHAR(10),
                          VARCHARCOL VARCHAR,
                          BOOLCOL BOOLEAN,
                          DATECOL DATE,
                          TIMESTAMPCOL TIMESTAMP,
                          TIMESTAMPTZCOL TIMESTAMPTZ,
                          TIMECOL TIME,
                          TIMETZCOL TIMETZ,
                          VARBINCOL VARBINARY,
                          BINCOL BINARY,
                          NUMCOL NUMERIC(38,0),
                          INTERVALCOL INTERVAL
                         );
=> COPY allTypes FROM '/home/dbadmin/allTypes.bin' NATIVE;
=> \pset expanded
Expanded display is on.
=> SELECT * from allTypes;
-[ RECORD 1 ]--+------------------------
INTCOL         | 1
FLOATCOL       | -1.11
CHARCOL        | one
VARCHARCOL     | ONE
BOOLCOL        | t
DATECOL        | 1999-01-08
TIMESTAMPCOL   | 1999-02-23 03:11:52.35
TIMESTAMPTZCOL | 1999-01-08 07:04:37-05
TIMECOL        | 07:09:23
TIMETZCOL      | 15:12:34-04
VARBINCOL      | \253\315
BINCOL         | \253
NUMCOL         | 1234532
INTERVALCOL    | 03:03:03

The content of the allTypes.bin file appears below as a raw hex dump:

4E 41 54 49 56 45 0A FF 0D 0A 00 3D 00 00 00 01 00 00 0E 00
08 00 00 00 08 00 00 00 0A 00 00 00 FF FF FF FF 01 00 00 00
08 00 00 00 08 00 00 00 08 00 00 00 08 00 00 00 08 00 00 00
FF FF FF FF 03 00 00 00 18 00 00 00 08 00 00 00 73 00 00 00
00 00 01 00 00 00 00 00 00 00 C3 F5 28 5C 8F C2 F1 BF 6F 6E
65 20 20 20 20 20 20 20 03 00 00 00 4F 4E 45 01 9A FE FF FF
FF FF FF FF 30 85 B3 4F 7E E7 FF FF 40 1F 3E 64 E8 E3 FF FF
C0 2E 98 FF 05 00 00 00 D0 97 01 80 F0 79 F0 10 02 00 00 00
AB CD AB CD 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 64 D6 12 00 00 00 00 00 C0 47 A3 8E 02 00 00 00

The following table breaks this file down into each of its components, and describes the values it contains.

Hex Values Description Value
4E 41 54 49 56 45 0A FF 0D 0A 00 Signature NATIVE\n\317\r\n\000
3D 00 00 00 Header area length 61 bytes
01 00 Native file format version Version 1
00 Filler value 0
0E 00 Number of columns 14 columns
08 00 00 00 Width of column 1 (INTEGER) 8 bytes
08 00 00 00 Width of column 2 (FLOAT) 8 bytes
0A 00 00 00 Width of column 3 (CHAR(10)) 10 bytes
FF FF FF FF Width of column 4 (VARCHAR) -1 (variable width column)
01 00 00 00 Width of column 5 (BOOLEAN) 1 bytes
08 00 00 00 Width of column 6 (DATE) 8 bytes
08 00 00 00 Width of column 7 (TIMESTAMP) 8 bytes
08 00 00 00 Width of column 8 (TIMESTAMPTZ) 8 bytes
08 00 00 00 Width of column 9 (TIME) 8 bytes
08 00 00 00 Width of column 10 (TIMETZ) 8 bytes
FF FF FF FF Width of column 11 (VARBINARY) -1 (variable width column)
03 00 00 00 Width of column 12 (BINARY) 3 bytes
18 00 00 00 Width of column 13 (NUMERIC) 24 bytes. The size is calculated by dividing 38 (the precision specified for the numeric column) by 19 (the number of digits each 64-bit chunk can represent) and adding 1. 38 ¸ 19 + 1 = 3. then multiply by eight to get the number of bytes needed. 3 ´ 8 = 24 bytes.
08 00 00 00 Width of column 14 (INTERVAL). last portion of the header section. 8 bytes
73 00 00 00 Number of bytes of data for the first row. this is the start of the first row of data. 115 bytes
00 00 Bit field for the null values contained in the first row of data The row contains no null values.
01 00 00 00 00 00 00 00 Value for 64-bit INTEGER column 1
C3 F5 28 5C 8F C2 F1 BF Value for the FLOAT column -1.11
6F 6E 65 20 20 20 20 20 20 20 Value for the CHAR(10) column "one " (padded With 7 spaces to fill the full 10 characters for the column)
03 00 00 00 The number of bytes in the following VARCHAR value. 3 bytes
4F 4E 45 The value for the VARCHAR column "ONE"
01 The value for the BOOLEAN column True
9A FE FF FF FF FF FF FF The value for the DATE column 1999-01-08
30 85 B3 4F 7E E7 FF FF The value for the TIMESTAMP column 1999-02-23 03:11:52.35
40 1F 3E 64 E8 E3 FF FF The value for the TIMESTAMPTZ column 1999-01-08 07:04:37-05
C0 2E 98 FF 05 00 00 00 The value for the TIME column 07:09:23
D0 97 01 80 F0 79 F0 10 The value for the TIMETZ column 15:12:34-05
02 00 00 00 The number of bytes in the following VARBINARY value 2 bytes
AB CD The value for the VARBINARY column Binary data (\253\315 as octal values)
AB CD The value for the BINARY column Binary data (\253\315 as octal values)
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 64 D6 12 00 00 00 00 00 The value for the NUMERIC column 1234532
C0 47 A3 8E 02 00 00 00 The value for the INTERVAL column 03:03:03