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.
-
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– kgrittn2012年05月13日 02:43:51 +00:00Commented May 13, 2012 at 2:43
-
@kgrittn See updateSean W.– Sean W.2012年05月13日 12:58:27 +00:00Commented May 13, 2012 at 12:58
1 Answer 1
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.