#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep 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?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep 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)

IMN logo majestic logo threadwatch logo seochat tools logo