By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

COPY - Ignore Problems

P: n/a
I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignore the case where the unique constraint exists
(lName,fName,workDate), and move on to the next record?

ps 7.4, debian stable
TIA
Ron
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Mon, 2003-12-22 at 23:47, Ron St-Pierre wrote:
I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignore the case where the unique constraint exists
(lName,fName,workDate), and move on to the next record?


CREATE TEMPORARY TABLE mytemp (
LIKE real_table, PRIMARY KEY (lname, fname, workdate)
);
COPY mytemp FROM '/my/input/file';
INSERT INTO real_table (
SELECT * FROM mytemp AS t
WHERE NOT EXISTS (
SELECT * FROM real_table AS r
WHERE r.lname = t.lname AND
r.fname = t.fname AND
r.workdate = f.workdate
)
);

NB: your mixed case names are automatically converted to lower case
unless you quote them.
--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And this shall be a sign unto you; Ye shall find the
babe wrapped in swaddling clothes, lying in a manger."
Luke 2:12
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

P: n/a
Thanks Dann, Michael and Oliver. I guess that a temporary table is what
I should use. I thought about doing it that way initially but thought
I'd see if perhaps I was missing a better way.
Ron
Ron St-Pierre wrote:
I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignore the case where the unique constraint exists
(lName,fName,workDate), and move on to the next record?

ps 7.4, debian stable
TIA
Ron
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

P: n/a
On Mon, 2003-12-22 at 23:47, Ron St-Pierre wrote:
I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignore the case where the unique constraint exists
(lName,fName,workDate), and move on to the next record?


CREATE TEMPORARY TABLE mytemp (
LIKE real_table, PRIMARY KEY (lname, fname, workdate)
);
COPY mytemp FROM '/my/input/file';
INSERT INTO real_table (
SELECT * FROM mytemp AS t
WHERE NOT EXISTS (
SELECT * FROM real_table AS r
WHERE r.lname = t.lname AND
r.fname = t.fname AND
r.workdate = f.workdate
)
);

NB: your mixed case names are automatically converted to lower case
unless you quote them.
--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And this shall be a sign unto you; Ye shall find the
babe wrapped in swaddling clothes, lying in a manger."
Luke 2:12
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #4

P: n/a
Thanks Dann, Michael and Oliver. I guess that a temporary table is what
I should use. I thought about doing it that way initially but thought
I'd see if perhaps I was missing a better way.
Ron
Ron St-Pierre wrote:
I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignore the case where the unique constraint exists
(lName,fName,workDate), and move on to the next record?

ps 7.4, debian stable
TIA
Ron
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.