Thread: COPY Question
I'm trying to load a table from a flat file using the copy command from
within PG.
Table:
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| dup_id | int4 not null default nextval('"
| 4 |
| dup_group | int4
| 4 |
| company | varchar()
| 0 |
| matchcode | varchar()
| 0 |
| new_matchcode | varchar()
| 0 |
| gender | char()
| 1 |
| lastnm | varchar()
| 0 |
| firstnm | varchar()
| 0 |
| address | varchar()
| 0 |
| state | char()
| 2 |
| postal_code | varchar()
| 0 |
| life_paid | float4
| 4 |
| life_refund | float4
| 4 |
| ytd_paid | float4
| 4 |
+----------------------------------+----------------------------------+-------+
Index: duplicates_pkey
Copy Command:
copy duplicates from '$SOME_DIRECTORY/cap_dups' using delimiters '|';
Example Record to be inserted:
|7984|cap|99830MTIN557|99830.PBKS557.MRMNT.MBRRST|M|ROBERTS|MATIN|PO BOX
557|AK|99830|0|0|0
Error:
ERROR: Cannot insert a duplicate key into a unique index
As you can see I'm delimiting the fields with a '|' character. You will
also notice that there is no field 1. I was hoping that I could leave
that blank and the sequence automatically fill in the PKEY. Does this
not work?
Any help to why this fail is appreciated.
Samuel Mullen
--
_____________________
/ Samuel A. Mullen \
| Programmer Analyst |_______________________________________________
| Opportunities Unl. | They also do no iniquity: they walk in his \
\_____________________/| ways. |
| Psalms 119:2 |
\_______________________________________________/
> As you can see I'm delimiting the fields with a '|' character. You will > also notice that there is no field 1. I was hoping that I could leave > that blank and the sequence automatically fill in the PKEY. Does this > not work? DEFAULTS are not activated by COPY. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Disregard this. I went with a temp table solution. Thanks, Sam Bruce Momjian wrote: > > > As you can see I'm delimiting the fields with a '|' character. You will > > also notice that there is no field 1. I was hoping that I could leave > > that blank and the sequence automatically fill in the PKEY. Does this > > not work? > > DEFAULTS are not activated by COPY. > -- > Bruce Momjian | http://www.op.net/~candle > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 -- _____________________ / Samuel A. Mullen \ | Programmer Analyst |_______________________________________________ | Opportunities Unl. | My soul shall be satisfied as [with] marrow \ \_____________________/| and fatness; and my mouth shall praise [thee] | | with joyful lips: | | Psalms 63:4 | \_______________________________________________/
Since triggers ARE fired by the COPY command, another possibility is the use
of a temporary trigger to simulate the functionality of DEFAULT.
Just change the name of the sequence and the table in the example below.
Once you are done with your copy, simply DROP TRIGGER fake_sequence ON foo;
Caveat Emptor: I have not exhaustively tested this, although it did work
with a small data file on my test system... I'm sure you will want to run
this when NO ONE else is using the database, if such a time exists ;^)
CREATE FUNCTION fake_sequence() RETURNS OPAQUE AS '
BEGIN
NEW.dup_id = NEXTVAL(''foo_dup_id_seq'');
RETURN NEW;
END;' LANGUAGE 'plpgsql';
CREATE TRIGGER fake_sequence
BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE fake_sequence();
-----Original Message-----
From: Samuel A. Mullen [mailto:smullen@oppunl.com]
Sent: Friday, April 21, 2000 8:37 AM
To: pgsql-general
Subject: Re: [GENERAL] COPY Question
Disregard this. I went with a temp table solution.
Thanks,
Sam
Bruce Momjian wrote:
>
> > As you can see I'm delimiting the fields with a '|' character. You will
> > also notice that there is no field 1. I was hoping that I could leave
> > that blank and the sequence automatically fill in the PKEY. Does this
> > not work?
>
> DEFAULTS are not activated by COPY.
> --
> Bruce Momjian | http://www.op.net/~candle
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
--
_____________________
/ Samuel A. Mullen \
| Programmer Analyst |_______________________________________________
| Opportunities Unl. | My soul shall be satisfied as [with] marrow \
\_____________________/| and fatness; and my mouth shall praise [thee] |
| with joyful lips: |
| Psalms 63:4 |
\_______________________________________________/