Postgresql – Importing Data Dump Using pgAdmin

database-administrationpostgresqlschemasql

I looked around and found the following code on how to import a data dump using pgadmin:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
  tables RECORD;
gmdeclare
  statement TEXT;
begin
  FOR tables IN 
    SELECT (table_schema || '.' || table_name) AS schema_table
    FROM information_schema.tables t INNER JOIN information_schema.schemata s 
    ON s.schema_name = t.table_schema 
    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
    ORDER BY schema_table
  LOOP
    statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
    EXECUTE statement;
  END LOOP;
  return;  
end;
$$ LANGUAGE plpgsql;

This looks like exactly what I need since I am pretty new to databases but know my way around .csv files. The problem is, I don't actually know how to set up this function properly. In the definition I need to specify a language (stated to be plpgsql in the answer) and a return type. I'm not sure what the return type should be. Moreover, and this probably sounds pretty silly, I'd like to confirm that I should replace TEXT with the path to my .sql dump file. Thanks for any and all help!

Best Answer

This function creates a data dump, not imports it, which is the opposite of what you want to do. The TEXT you are referring to is the path to the CSV file it will generate.

PgAdmin 1.4 seems to have a restore facility: http://www.pgadmin.org/docs/1.4/restore.html

If you are trying to import data into a single table, you can right click on the table in PgAdmin and select import.

If you are trying to import data for the whole database and you are open to other options besides PgAdmin, you can use psql:

psql -h host -U username dbname < data.dmp
Related Topic