Reading Parquet Files
CREATE EXTERNAL TABLE ... STORED AS PARQUET queries a Parquet file in place, with the same SQL as any table. The reader reads the file's footer and only the column chunks of the row groups a scan asks for, and decodes a row group at a time.
CREATE EXTERNAL TABLE events (id BIGINT, tags BIGINT[], addr JSONB)
STORED AS PARQUET LOCATION 'file:///data/events.parquet'
TBLPROPERTIES ('location_type' = 'FILE')
Each declared column is a top-level field of the file, in file order, declared with the type the tables below give for that field.
Making an external table needs CREATE on the schema, as for any table, and a superuser or a member of pg_read_server_files, the right COPY ... FROM a server file asks: the server opens the location with its own rights, whether it names a file, a directory, a glob, an http or https address or an object store. Any other role is refused with 42501, permission denied to create an external table over a file or URL, and nothing is registered. The role that makes an external table owns it, as for any table: it reads the table, hands it to another role with ALTER TABLE ... OWNER TO and drops it without a grant. Reading the table afterwards is a plain SELECT privilege on it.
Primitive fields
| Parquet type | Column type | Notes |
|---|---|---|
BOOLEAN | BOOLEAN | |
INT32, INT64 | INTEGER, BIGINT | |
UINT_32, UINT_64 | unsigned 32-bit and 64-bit integer | Values above the signed range stay positive |
FLOAT, DOUBLE | REAL, DOUBLE PRECISION | |
FLOAT16 | REAL | Widened exactly |
STRING, JSON, ENUM | TEXT | |
BINARY, FIXED_LEN_BYTE_ARRAY | BYTEA | |
UUID | UUID | |
DATE | DATE | |
TIME | TIME | Milliseconds and nanoseconds read as microseconds |
TIMESTAMP | TIMESTAMP | Milliseconds and nanoseconds read as microseconds, so the value is the instant the file holds, whatever its unit |
INT96 | TIMESTAMP | The legacy timestamp of older writers |
DECIMAL on INT32, INT64, BINARY or FIXED_LEN_BYTE_ARRAY | NUMERIC(p,s) | The file's own precision and scale |
A timestamp outside the range of a TIMESTAMP, and a decimal of more than 38 digits, are an error that names the column (the engine holds a decimal in at most 38 digits).
Nested fields
| Parquet shape | Column type | Value |
|---|---|---|
LIST of one primitive, or a repeated primitive | array of that primitive (BIGINT[], TEXT[], ...) | A NULL list is NULL, an empty list is {}, a NULL element is NULL; a repeated primitive is never NULL |
STRUCT | JSONB | An object of the fields; an absent struct is NULL |
MAP | JSONB | An object of the entries, each key as text; a map with a NULL key is an error that names the column |
LIST of structs, lists or maps, and any deeper nesting | JSONB | The arrays and objects, nested as the file nests them |
A NULL inside a JSONB value is JSON null, and a NULL field of a row is SQL NULL. Read a field with the JSONB operators, as in addr->>'city', and an array with cardinality(tags) or tags[1].
Inside a JSONB value each leaf reads by the type table above, written the way to_jsonb writes it:
| Leaf | JSON form |
|---|---|
| integer, unsigned integer | number |
| decimal | the exact number, at the file's scale |
REAL, DOUBLE PRECISION | number; NaN, Infinity and -Infinity are the strings PostgreSQL writes |
DATE, TIME, TIMESTAMP, INT96 | the ISO string to_jsonb makes, in microseconds |
UUID | the text of the uuid |
STRING, JSON, ENUM | string |
BINARY, FIXED_LEN_BYTE_ARRAY | the \x hex string of a BYTEA |
A file whose definition and repetition levels do not fit its schema is an error that names the column, never a wrong value.
A file whose content does not fit the format (levels that disagree with the schema, a map with a NULL key, a footer that is not one) is 22P04 (bad_copy_file_format), the code COPY answers for such content. A file that cannot be read at all (removed, unreadable, a remote read that fails) is 58030 (io_error). Neither is an internal error.
CSV files
STORED AS TEXTFILE reads CSV files the same way, with the table properties below. The columns are declared as for any table.
CREATE EXTERNAL TABLE sales_csv (id INT, region TEXT)
STORED AS TEXTFILE LOCATION 'file:///data/sales/'
TBLPROPERTIES ('location_type' = 'DIRECTORY', 'has_header' = 'true')
| Property | Values | Default |
|---|---|---|
location_type | FILE, DIRECTORY, GLOB or LIST: what LOCATION names | DIRECTORY when the location ends in /, otherwise FILE |
recursive | true or false: whether a directory or glob reads its subdirectories too; not applicable to FILE or LIST | true for DIRECTORY and GLOB |
has_header | true or false: whether the first line of each file is a header line, which is skipped | false |
delimiter | One character | , |
A location that names no file is refused when the table is made, rather than creating a table that scans nothing.
Writes
An external table is read-only, as a PostgreSQL foreign table is, and a statement that would write it is refused in PostgreSQL's words before anything is written, whether the table is named or reached through the table it inherits from or that inherits from it (ALTER TABLE ... INHERIT makes an external table a child or a parent of a table of the database).
INSERT INTO events VALUES (1, NULL, NULL);
ERROR: cannot insert into foreign table "events"
UPDATE events SET id = 2;
ERROR: cannot update foreign table "events"
DELETE FROM events;
ERROR: cannot delete from foreign table "events"
TRUNCATE events;
ERROR: cannot truncate foreign table "events"
MERGE INTO events AS t USING (VALUES (1)) AS s(id) ON t.id = s.id WHEN MATCHED THEN DELETE;
ERROR: cannot execute MERGE on relation "events"
DETAIL: This operation is not supported for foreign tables.
COPY events TO STDOUT;
ERROR: cannot copy from foreign table "events"
HINT: Try the COPY (SELECT ...) TO variant.
All of those are 0A000 except COPY events TO, which is 42809; COPY (SELECT * FROM events) TO writes the rows. The statement is refused even when no row would be touched (WHERE false), except through a parent: a write through a table that has an external child leaves the child alone when the predicate is a constant that no row can satisfy, and ONLY keeps the write to the parent. A write through an external parent is refused whether or not ONLY is written, and the regular children below it are written by their own names. Reads work through every family, and SELECT ... FROM ONLY parent reads the parent's own rows.