上QQ阅读APP看书，第一时间看更新

Importing data using pgAdmin

In this section we'll import some new data we have not interacted with before - this time we'll have a look at the Ordnance Survey's address data we obtained in the CSV format.

Depending on the pgAdmin version, the UI may differ a bit. The described functionality should always be present though.
For the examples involving pgAdmin, screenshots were taken using pgAdmin III (1.22.2).

PgAdmin's import functionality is basically a wrapper around the \COPY so it does require a data model in order to work. Because of that, let's quickly create a table that will be populated with the imported data. You can do it with the GUI by simply right-clicking a schema node you want to create the table in and choosing New Object | New Table and then providing all the necessary model definitions in the displayed window:

You can also type some SQL which in many cases is a bit quicker:

drop table if exists data_import.osgb_addresses; 
create table data_import.osgb_addresses( 
   uprn bigint, 
   os_address_toid varchar, 
   udprn integer, 
   organisation_name varchar, 
   department_name varchar, 
   po_box varchar, 
   sub_building_name varchar, 
   building_name varchar, 
   building_number varchar, 
   dependent_thoroughfare varchar, 
   thoroughfare varchar, 
   post_town varchar, 
   dbl_dependent_locality varchar, 
   dependent_locality varchar, 
   postcode varchar, 
   postcode_type varchar, 
   x numeric, 
   y numeric, 
   lat numeric, 
   lon numeric, 
   rpc numeric, 
   country varchar, 
   change_type varchar, 
   la_start_date date, 
   rm_start_date date, 
   last_update_date date, 
   class varchar 
);

Once our table is ready, importing data is just a matter of right clicking the table node in PgAdmin and choosing Import. An import wizard that assists with the import process will be displayed:

All the earlier could obviously be achieved with pure SQL and in fact we have done this already in the previous section on importing data in psql in non-interactive mode. You can review the SQL code available in Chapter02/code for details.