The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> PostgreSQL Help
|
Insert Fails When multi-row, fine otherwise
Discuss Insert Fails When multi-row, fine otherwise in the PostgreSQL Help forum on Dev Shed.
Insert Fails When multi-row, fine otherwise PostgreSQL Help forum discussing administration, SQL syntax, or other PostgreSQL-related topics. PostgreSQL provides enterprise level database functionality at open source prices.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
July 5th, 2012, 08:24 PM
|
|
Registered User
|
|
Join Date: Jul 2012
Posts: 2
Time spent in forums: 22 m 40 sec
Reputation Power: 0
|
|
|
Insert Fails When multi-row, fine otherwise
I have a lot of records to stuff into the DB (4.5 million). Instead of doing so individual queries, I wrote some Java code that creates batches of inserts out of my flat file. So, my query looks as follows:
Code:
INSERT INTO tempwords(wordID, wordSpelling, soundLocation, level) SELECT '9341eb48-0b3d-4c7f-8de6-afa1e4843a60', 'fire', 'NOT_CONFIGURED', 0 UNION ALL SELECT '138fe8ec-181d-4ba0-b173-a779b22edb04', 'root', 'NOT_CONFIGURED', 0 UNION ALL SELECT 'f1fe4f78-b41a-44b9-b3f2-b35e9e533a5e', 'woman', 'NOT_CONFIGURED', 0 UNION ALL SELECT 'ca1d2317-e6a8-434c-9d2a-a8affba0b1e0', 'all', 'NOT_CONFIGURED', 0 UNION ALL SELECT '3d6bc860-6a43-4ee4-bb53-7057033f8149', 'drink', 'NOT_CONFIGURED', 0 UNION ALL SELECT 'ee18c4a5-3301-49f8-abd0-fe816a94bf76', 'eat', 'NOT_CONFIGURED', 0 UNION ALL SELECT '6dba8770-9b66-4e45-8baf-7741b86daa0e', 'hear', 'NOT_CONFIGURED', 0 UNION ALL SELECT '88e53f3a-1176-497e-aa5a-0351b9d32312', 'see', 'NOT_CONFIGURED', 0 UNION ALL SELECT 'b8e64614-4a60-416d-8f52-d63478d16aa3', 'bite', 'NOT_CONFIGURED', 0 UNION ALL SELECT '7b6db544-54cf-4dc1-9516-8d75c2f0444a', 'what', 'NOT_CONFIGURED', 0
When I run it that way, PostgreSQL complains saying:
ERROR: column "wordid" is of type uuid but expression is of type text
It also tells me I'll need to re-write or cast the expression. I don't know why or what to do to fix this. However, if I run the same inserts one at a time, I get no complaints.
The following code is the same data as one row of the multi insert query and I insert it with success.
Code:
INSERT INTO tempwords(wordID, wordSpelling, soundLocation, level) VALUES('9341eb48-0b3d-4c7f-8de6-afa1e4843a60', 'fire', 'NOT_CONFIGURED', 0)
Can someone help me understand what's wrong and how to fix it, please?
|
July 5th, 2012, 08:33 PM
|
|
Registered User
|
|
Join Date: Jul 2012
Posts: 2
Time spent in forums: 22 m 40 sec
Reputation Power: 0
|
|
|
I found documentation that shows PostgreSQL does it differently. I've fixed this problem now. SQL syntax is different in PostgreSQL on this one. It is like so, instead:
INSERT INTO tempwords(wordID, wordSpelling, soundLocation, level)
VALUES('9341eb48-0b3d-4c7f-8de6-afa1e4843a60', 'fire', 'NOT_CONFIGURED', 0),
('138fe8ec-181d-4ba0-b173-a779b22edb04', 'root', 'NOT_CONFIGURED', 0),
('f1fe4f78-b41a-44b9-b3f2-b35e9e533a5e', 'woman', 'NOT_CONFIGURED', 0)
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
