samedi, février 25 2012, 00:47
An import tool for pgAdmin
With pgAdmin 1.14 and prior releases, there were only two ways to insert data in a table:
- use the restore tool (which uses pg_restore) ;
- use the query tool to execute INSERT queries (COPY queries are allowed if they use a file, but not stdin/stdout).
There was no way to ask it to import a CSV file for example. And a thread began on this matter a week ago. As working on the COPY API of libpq was quite motivating, I worked on this a few hours, and finished yesterday with this patch.
This patch adds a new window that allows a user to choose a file, and to select all options available in the COPY command (but only options available for import, not export).
This window is displayed when someone selects the "Import" item in the table's contextual menu, as shown in the following screenshot:
The import window has 5 tabs:
- "File options" tab makes possible to choose a file, sets its format (text, CSV, or binary), and optionaly its encoding.
- "Columns" tab allows the user to specify which columns are contained in the file. Right now, they must be in the same order than the table (because of the UI), but they don't have to be all present in the file.
- "Misc Options", "Quote Options", and "NULL options" tabs are not used usually, but it could be handy to set another delimiter than the usual comma, amongs other possibilities.
When the user clicks the "Import" button, pgAdmin launches a COPY query that matches the selected options, and sends the file by chunks of 8KB. The scrollbar shows the progress of the import. If the import ends with success, the "Import" button is renamed "Done", and a click on it closes the dialog.
That was a really interesting patch to write, for the UI, and the COPY API. And it is a quite nice feature for pgAdmin.