Import and export

Alcuin imports CSV files and exports to CSV, JSON and INSERT statements. Every export reads only, and every import loads its rows in one transaction: a file that fails part way leaves the table as it was.

Import a CSV into a table

The connection must be unlocked. On a read-only connection the command is not offered; see Work on production without breaking it.

1. Open the table.
2. Choose Import CSV into this table… in the command palette (⌘⇧P on macOS, Ctrl+Shift+P on Windows and Linux), and pick a .csv, .tsv or .txt file.
3. The import screen opens on the table’s tab, with the file’s first rows.
4. Check the file settings, against the first 20 rows shown: Delimiter (comma, semicolon, tab or pipe), First row is a header, and NULL when the text is.
5. Map each file column to a table column, in the menu at the head of the file column. A file column whose name matches a table column starts mapped; the rest start as skip.
6. Choose a mode, read the SQL that will run on the right, and click Import.

1234

The import screen.

1. 1File settings.
2. 2The table column each file column goes to.
3. 3The mode.
4. 4The SQL that will run.

When it is done, a message says Imported 2 rows into orders (or, for the other modes, Upserted into orders: 2 new, 3 updated and Replaced the rows in orders: 1,204 deleted, 2 imported), and the grid shows the table again. Alcuin reads the file as UTF-8.

The three modes

Mode	What it does
Append	Adds the file’s rows to the table.
Upsert on key	Matches rows by the table’s primary key or a unique index over NOT NULL columns. A matched row takes the file’s values in the mapped columns; the rest are added. Off for a table without such a key, with the reason.
Replace	Empties the table first, in the same transaction, then adds the file’s rows. The button says Replace rows.

The screen says what the mode does to this table. For an upsert, it counts how many of the first rows would update rows already there. For a replace, it says how many rows it deletes.

The SQL that will run is one INSERT with bound parameters, sent in batches inside one transaction. For a three-column append:

INSERT INTO public.orders (customer_id, status, total)
VALUES ($1, $2, $3)

Upsert adds an ON CONFLICT clause on the key, and Replace starts with TRUNCATE:

INSERT INTO public.orders (id, status, total)
VALUES ($1, $2, $3)
ON CONFLICT (id) DO UPDATE SET status = EXCLUDED.status, total = EXCLUDED.total

The file’s text never becomes SQL text: each value is a parameter, and Postgres casts it to the column’s type.

NULL and empty fields

NULL when the text is starts empty. Then an empty unquoted field imports as NULL, and a quoted empty field, "", imports as an empty string. Type another marker, such as \N or NULL, if your file uses one. A quoted field is always text, even when it matches the marker.

Your Safety rules still apply

An import is a write. Where your rules ask before every write, Alcuin shows the INSERT and waits for a yes. A replace asks where your rules ask before destructive statements, which every environment does at first. The dialog, Run this anyway? (Run this on production? on production), reads Replace every row in orders: this deletes its 1,204 rows before the import. It cannot be undone from here., and Run it goes ahead.

Import a CSV as a new table

1. In the Explorer, open a schema’s menu and choose Import CSV as new table…, then pick the file.
2. A new table’s tab opens, named after the file.
3. Alcuin reads up to the first 1,000 rows, and no more than 1 MB of the file, and proposes a column for each: a name made into an identifier, the narrowest type every value fits (bigint, numeric, boolean, date, timestamp, timestamptz, uuid, jsonb) or text, and Not null where no value is empty. Change any of them, or clear Import to leave a column out.
4. If a column looks like a key, a unique bigint, uuid or id-like text column, Make it the key offers it as the primary key. You can pick any column as the key yourself. No key is added unless you choose one.
5. Click Create and import. Alcuin shows the CREATE TABLE, then the INSERT, applies the table through your rules, then imports the rows in a transaction of their own.

When it is done, a message says Created orders and imported 1,204 rows.

Export a table or a result

For a table, click the export button on the table’s toolbar, Copy or save the rows. For a query’s result, click the export button in the grid’s top-left corner, above the row numbers, Copy or save the result. Then choose:

• Copy as CSV, Copy as JSON or Copy as INSERT statements to put every row on the clipboard.
• Save as CSV…, Save as JSON… or Save as INSERT statements… to write a file.

For a query’s result, the INSERT items show only when Alcuin can write the result as INSERT statements, and the Save as items only when the result can be saved to a file.

The command palette has the same commands, such as Export table as CSV… and Copy results as JSON.

A table exports the rows its filters keep, in the grid’s order. A result’s Save as runs the query again and streams its rows to the file. It must be one statement that only reads, and it runs in a read-only transaction. The file appears only once it is complete: an export that fails part way leaves no file behind.

Saving a result as INSERT statements asks Insert into which table?, with the table the query reads from filled in.

When it is done, a message says Exported 1,204 rows to orders.csv.

Copying many rows

Copy reads every row, not only the ones on screen. A large copy asks first, and offers Export to file… instead. Results and memory gives the limits.

Copy a selection

Select cells or rows in any grid and right-click. The first item, such as Copy 6 cells, copies them as tab-separated text. Copy as offers these formats: Tab-separated, with headers; CSV; JSON; Markdown table; SQL values list; and on a table, SQL INSERT. A cell’s menu also has Copy row as JSON, and on a table, Copy row as INSERT.

Copying one cell gives its value exactly as Postgres sent it. See NULL, empty strings, time zones and big numbers.

What each format keeps

• CSV keeps NULL apart from an empty string: NULL is an empty field, and an empty string is written "". A value with a comma, a quote or a line break is quoted. The first line is the column names.
• JSON writes each row as an object, with each value as the server’s text. A json or jsonb value goes in exactly as the server wrote it, so large numbers and repeated keys inside it are kept. When two columns share a name, as SELECT * over a join often gives, the later ones are numbered (id, id_2) so no value is lost, and the copy says so.
• JSON cannot tell SQL NULL from JSON null. A NULL cell and a json value that is null both come out as null. Use CSV or INSERT when that difference matters.
• INSERT from a table follows the table’s columns: generated columns are left out (the server computes them again), an ALWAYS identity column’s values are kept with OVERRIDING SYSTEM VALUE, a row of a table whose every column is generated is written as DEFAULT VALUES, and the script ends by moving identity and serial sequences past the copied values (below the smallest for a sequence that counts down), so the next ordinary insert gets a new one.
• INSERT from a query names every column the query returned. Alcuin doesn’t know which columns of the target the server owns, so a replay into a table with generated or ALWAYS identity columns fails until you remove those columns, and no sequence is moved.

If it did not work

• An import that fails imports nothing. The message ends (nothing was imported) and names the row of the file’s data it failed on, with Postgres’ own reason, such as a value that does not fit the column’s type.
• A file that is not well-formed CSV is refused with its line and row: a quote never closed, text after a closing quote, or a row wider or narrower than the first.
• A table column is mapped twice.: two file columns point at the same table column.
• A new table whose rows failed to import is left there, empty. The message says so and offers Drop it….
• If the connection drops while an import commits, Alcuin asks the server how it ended before you import into that table again, so the same rows are not added twice. See ALC-1005: Transaction outcome unknown and ALC-3003: Changes already applied.
• No Import CSV into this table… in the palette means the connection is read-only. See ALC-3001: Connection is read-only.
• Export a query that only reads.: Save as from a result refuses a statement that writes, such as DELETE … RETURNING. Copy the rows instead, or export the table.