Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let me preface this question by stating that my experience with DBA is limited to an introductory undergraduate university course, and that I'm just getting started with PostgreSQL.

I would like to programmatically import data from an incredibly large (~4GB) flat CSV (I know, not the best format, but it's what I have to work with) into a PostgreSQL database on a regular basis. I believe I can use COPY FROM to accomplish this task. What occurs when a CSV line that matches or conflicts with an existing record is encountered? Does it update the record, ignore it, or throw an error? Is this behavior changeable?

The documentation states, in part:

COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules.

My interpretation of this statement is that rather than updating an existing record, this statement would treat the CSV entry as a new record, which would in turn violate constraints that may be placed on the columns. Is this correct? If so, it seems like my only course of action would be to issue DELETE on the tables before importing the data each time. That strikes me as highly inefficient.

UPDATE

A sample of the data-set that I'm working with may be found here. I would like to import and normalize the data from this highly de-normalized source. As you can see, each license record may be duplicated for each related antenna that may exist.

share|improve this question
    
It's not clear whether each COPY will be a full set of the data, replacing what was there before, or whether you want to merge the new set with what's already in the database. –  kgrittn May 13 '12 at 2:43
    
@kgrittn See update –  Sean W. May 13 '12 at 12:58
add comment

1 Answer 1

up vote 3 down vote accepted

The COPY FROM will not violate integrity of database.

When a constraint fails, an error is throw, and none of data are copied (it is rolled back).

This behavior is unchangeable. You can do a script in a programming language to help you in a copy.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.