0

I think it's better to ask before do. need to insert data into a table I need to inset about 800 rows. I tried to performed the task manually but it's so error prone. I need to visualize the data and make some sql to insert it.

my spreedsheet would be like so:

  SP SC RP ..
SP 1 4  5
SC 2 4  4
RP 4 3  4

I would create something like this:

destiny source  value
SP       SP     1
SC       SP     4

Is it pratical to do this in Postgres? Which tools can I use?

Diego Alves
  • 2,462
  • 3
  • 32
  • 65
  • I think if you want to do this, first export to CSV, and then you import into Postgres. Depending on your requirements, you could either write a `plpgsql` function that processed the data to give you the table format you want, or you could use any number of scripting languages (perl, python, etc.) to achieve the table structure. Also take a look at https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table – richyen Nov 06 '19 at 21:13
  • Another option is to, after having exported as CSV, use the [file_fdw](https://www.postgresql.org/docs/current/file-fdw.html) to query/load the data. – gsiems Nov 06 '19 at 21:30
  • I would first unpivot that data so it matches how you want to store it (good decision on that second schema) and then work on importing it. I agree too that it should be imported as a CSV. – JNevill Nov 06 '19 at 22:17
  • If I was going to do this i'd use Ruby because that's what I know. I'd think most front end tools would be able to read the spreadsheet and interface with PostgreSQL. Now that I think about it, a macro in Excel and and ODBC connection would do this without even leaving your spreadsheet. – Ben Trewern Nov 07 '19 at 00:35

0 Answers0