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 |