# Rails - Postgres - importing data

**URL:** <https://www.ruby-forum.com/t/rails-postgres-importing-data/61993>\
**Category:** Rails\
**Created:** [May 30, 2006, 5:16pm UTC](https://www.ruby-forum.com/t/rails-postgres-importing-data/61993 "2006-05-30T17:16:30Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\
**Post date:** [May 30, 2006, 5:16pm UTC](https://www.ruby-forum.com/t/rails-postgres-importing-data/61993/1 "2006-05-30T17:16:30Z")

</div>

I am writing a application where there is a lot of data in an old  
Filemaker program that I am going to need to do a 1 time import…1 main  
table and 2 related tables. I can get the data from Filemaker into a dbf  
or csv or Excel file without problem.

Is anyone aware of a utility for postgresql or a rails plug-in that  
might make this easier? I am worried about Rails import since I can’t  
see a way of importing the related tables while keeping the ‘id’ from  
the primary table.

Any suggestions would be appreciated

Craig

---

<div class="post-metadata">

**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\
**Post date:** [May 30, 2006, 5:28pm UTC](https://www.ruby-forum.com/t/rails-postgres-importing-data/61993/2 "2006-05-30T17:28:48Z")

</div>

How much is a lot? - The easiest way to go with pgsql is to try and  
export to tab separated format for each table and use COPY to squirt it  
into postgres. You may need to do some munging of the original data to  
clean it up, for which you can use Ruby or Perl but other than that you  
should have no problems. If it is a LOT of data then excel will puke at  
about 65,000 rows… which is at least 935,000 rows short of “a lot”  
IMHO 🙂

Matt.

---

<div class="post-metadata">

**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\
**Post date:** [May 30, 2006, 5:29pm UTC](https://www.ruby-forum.com/t/rails-postgres-importing-data/61993/3 "2006-05-30T17:29:56Z")

</div>

Craig W. wrote:

> I am writing a application where there is a lot of data in an old  
> Filemaker program that I am going to need to do a 1 time import…1 main  
> table and 2 related tables. I can get the data from Filemaker into a dbf  
> or csv or Excel file without problem.
> 
> Is anyone aware of a utility for postgresql or a rails plug-in that  
> might make this easier? I am worried about Rails import since I can’t  
> see a way of importing the related tables while keeping the ‘id’ from  
> the primary table.
> 
> Any suggestions would be appreciated
> 
> Craig

checkout “psql \copy”  
([PostgreSQL: Documentation: 8.1: psql](http://www.postgresql.org/docs/8.1/interactive/app-psql.html))

---

<div class="post-metadata">

**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\
**Post date:** [May 30, 2006, 5:34pm UTC](https://www.ruby-forum.com/t/rails-postgres-importing-data/61993/4 "2006-05-30T17:34:51Z")

</div>

OK - a little 😉

The main db has 150 or so columns but the number of records is small by  
that yardstick.

I was thinking that I would do it exactly as you stated but wanted to  
canvas for other possibilities.

Thanks

Craig

---

<div class="post-metadata">

**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\
**Post date:** [May 30, 2006, 5:39pm UTC](https://www.ruby-forum.com/t/rails-postgres-importing-data/61993/5 "2006-05-30T17:39:14Z")

</div>

also note there is a difference in the permissions required with  
postgres’s “COPY” command and psql’s “\copy” option.

The former requires server level privs (which, if you are in a shared  
hosting environment, not likely to have).

see the “notes” section here:

> **[COPY](https://www.postgresql.org/docs/8.1/sql-copy.html)**
>
> COPY COPY — copy data between a file and a table Synopsis COPY table\_name \[( column\_name \[, ...\] ) …