56

Is there any plugin or library which I need to use for this? I want to try this on my local system first and then do the same on Heroku Postgresql

Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
abhim
  • 1,126
  • 1
  • 9
  • 19

3 Answers3

78

pgAdmin has GUI for data import since 1.16. You have to create your table first and then you can import data easily - just right-click on the table name and click on Import.

enter image description here

enter image description here

Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
  • 1
    How should I map columns from CSV to the table columns? – abhim Oct 16 '13 at 11:21
  • 3
    This expects that columns in your table match 1:1 to your CSV file, including order of columns. There is no need for mapping. – Tomas Greif Oct 16 '13 at 11:36
  • I am having issues with unique constraint, how can I omit the duplicate data? – abhim Oct 16 '13 at 11:46
  • There is no way you can handle this during import. Remove constraint from table, import data, delete duplicate data and then add your constraint back. – Tomas Greif Oct 16 '13 at 12:03
  • 1
    RE: mapping columns in CSV to table… pgAdmin expects the CSV to match the natural order of the columns (order in which columns were added to table). This is just a limitation of pgAdmin. Underneath, pgAdmin is actually just using the [`COPY`](http://www.postgresql.org/docs/current/static/sql-copy.html) command. If you were to write the SQL for `COPY` yourself you could specify a different ordering of columns being imported. Also, notice the "Columns" tab where you can tell pgAdmin that some columns are omitted in the CSV so a default value should be generated. – Basil Bourque Apr 02 '16 at 04:40
  • This tool NEVER works for me. :/ I cannot comprehend why pgAdmin can not import it's own export format. Seems so simple. – Meekohi Nov 03 '16 at 16:08
  • Note that if your columns do not match, you can use an updatable view to present a different order. Provided you are only mapping to a single table PostgreSQL can work out how to perform the mapping without the need for an `INSTEAD OF` trigger. – beldaz May 13 '17 at 22:08
23

assuming you have a SQL table called mydata - you can load data from a csv file as follows:

COPY MYDATA FROM '<PATH>/MYDATA.CSV' CSV HEADER;

For more details refer to: http://www.postgresql.org/docs/9.2/static/sql-copy.html

6

You may have a table called 'test'

COPY test(gid, "name", the_geom)
FROM '/home/data/sample.csv'
WITH DELIMITER ','
CSV HEADER
Rajitha Bandara
  • 743
  • 1
  • 10
  • 16