Copy & Paste is the most convenient way to import and export data in Postico.
Postico, like most other Mac apps, copies tabular data in "Tab Separated Values" (TSV) format: columns are separated by tabs, and row are separated with newlines. The limitation of this format is that your data can't contain tabs or newlines. Postico replaces these special characters with spaces when you copy data to the clipboard.
You can also copy data in other formats using the "Copy Special" command. Postico offers the following alternative formats for copying: TSV with headers, CSV, CSV with headers, SQL Insert Statements.
Postico also allows pasting data in TSV format. This is great for importing data from spreadsheets in Excel or Numbers, or from MySQL databases using Sequel Pro, or even from tables on websites in Safari.
Here's how to paste data into Postico:
Since CSV files are not standardized, Postico is flexible and supports many configurations. Nevertheless, not all CSV files can be imported and in some cases you have to change the file's encoding. The default settings are such that a file exported from Postico with the "International" setting (see below) can be imported. This is also the same format that the PostgreSQL command COPY (...) CSV HEADER; uses. (Except for the handling of empty strings, see below.)
Postico can import UTF-8 files only. If your CSV file has a different encoding, use your favorite text editor to convert.
Postico supports CSV files with the most common column separator characters: comma (,), semicolon (;), tab, or pipe (|)
Supported line separators are: Unix (LF), Windows (CRLF), Mac (CR). You do not need to configure this, Postico automatically allows any of these separators.
Only double quotes are allowed. When a field contains the quote character, it can be escaped either by doubling (e.g. "") or by preceding it with a backslash (\").
Decimal numbers can use either period (.) or comma (,)
Postico has special handling for numbers and NULL values. All other types are sent to the server without modification.
Postico automatically validates numbers, replaces the decimal separator if necessary and removes optional grouping characters (eg. thousands separator).
All empty values in the CSV file are converted to NULL. Postico treats empty strings as NULL.
Since dates are sent to the server without modification, they will be imported according to the configuration of the database server. The date format YYYY-MM-DD is recommended for importing (should always work). Dates in other formats are interpreted according to the servers DATESTYLE. Make sure that the server and your import file have the same DATESTYLE. (PostgreSQL Documentation)
Import in Postico is transactional. Either the whole import completes successfully; or the database is not changed at all. You will never end up with a half-finished import.
Postico achieves this by wrapping INSERT statements in a transaction. This allows Postico to rollback (undo) the import in the event of an error.
Before the import, Postico executes "BEGIN" to start a transaction, and after a successful import "COMMIT". If a transaction is already running (e.g. because you manually issued a BEGIN statement), Postico creates a SAFEPOINT instead. After a successful import, the safepoint is released. This means that a failed import will not cause an already running transaction to fail.
This list contains common errors which can occur during the import.
Postico can export tables and query results as "Comma Separated Values" (CSV) files. You can export either a single table, or multiple tables at once.
When exporting the current table (by selecting "Export" from the File menu), all rows in the table will be exported, even though Postico only displays the first page. If filters are active, they will be used, and only filtered rows will be exported.
Make sure the keyboard focus is on the table view when selecting export; otherwise you may end up exporting the selected table in the sidebar.
You can export multiple tables by selecting them in the sidebar or in the grid view (hold down the shift or command keys). Then right-click and chose export from the context menu. When exporting multiple tables, Postico will create a folder containing one CSV file per table.
You can also export query results in the SQL Query view.
Unfortunately CSV is a very losely defined format. Applications implement CSV in very different ways. This fact is made even worse by the fact that apps change behavior depending on your system's language & region settings.
I've examined the CSV file support of popular applications, and I came to the conclusion that most apps understand one of two formats, which I call "International" and "Continental":
Most apps allow using double quotes (") around values that can contain special characters. This allows CSV to store data that contains special characters like the field separator or newlines. Postico also uses quotes:
This produces output compatible with the majority of applications I've tested. However, there are two more settings (mostly for compatibility with Microsoft Excel):