Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Unanswered: I'm having problems with casting from text to int...

    Here's the preamble...

    I have a load of data in about 100 CSV files. My table has 16 columns of various types, each column suffers from an inconsistency of type caused by the fact that when there's a null value instead of a blank the data offers single quotation marks. So my goal is to copy the data to a table on my database with the inconsistency dealt with.

    To that end I've created a table, "table_b" in which every column is of a type text. I then copied my CSVs into this table, I've then created another table, "table_a", with the types as I want them to be. I then SELECT and CAST the data from "table_b" and INSERT INTO "table_a"

    So far so good as a concept, but I'm having a problem with the execution. This is the code I've used:

    Code:
    INSERT INTO table_a (Settled_date, Scheduled_Off, DT_Actual_Off, Latest_taken, First_taken, Win_Flag, Sports_ID, Event_ID, Full_Description, Event, Selection_ID, Selection, Odds, Number_bets, Volume_Matched, In_play)
    SELECT 
    (CASE WHEN Sports_ID = '' THEN NULL ELSE Sports_ID)::int,
    Sports_ID, Event_ID, Full_Description, Event, Selection_ID, Selection, Odds, Number_bets, Volume_Matched, In_play FROM table_B;
    But i get the error message:

    ERROR: syntax error at or near ")"
    LINE 3: (CASE WHEN Sports_ID = '' THEN NULL ELSE Sports_ID)::int,

    As you've probably worked out I'm extremely new to this and trying to cobble together bits of code people have given to me and so I'm sure my error is basic. Any help will be gratefully received.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    This should work
    Code:
    SELECT CASE WHEN sports_id = '' THEN NULL ELSE sports_id::int, ...

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    Quote Originally Posted by shammat View Post
    This should work
    Code:
    SELECT CASE WHEN sports_id = '' THEN NULL ELSE sports_id::int, ...
    Thanks for replying. This is what I did:

    Code:
    INSERT INTO table_a (Settled_date, Scheduled_Off, DT_Actual_Off, Latest_taken, First_taken, Win_Flag, Sports_ID, Event_ID, Full_Description, Event, Selection_ID, Selection, Odds, Number_bets, Volume_Matched, In_play)
    SELECT CASE WHEN Sports_ID = '' THEN NULL ELSE Sports_ID::int,
    Sports_ID, Event_ID, Full_Description, Event, Selection_ID, Selection, Odds, Number_bets, Volume_Matched, In_play FROM table_B;
    But now it's giving me:

    ERROR: syntax error at or near ","
    LINE 2: ...LECT CASE WHEN Sports_ID = '' THEN NULL ELSE Sports_ID::int,
    Any thoughts as to why?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Tartaruga View Post
    Any thoughts as to why?
    The END for the CASE is missing (sorry, copy & paste error on my side)

  5. #5
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    Quote Originally Posted by shammat View Post
    This should work
    Code:
    SELECT CASE WHEN sports_id = '' THEN NULL ELSE sports_id::int, ...
    I am very, very new to PostgreSQL. I see a couple colons in the line above. Is that a shorthand for something? And if so, for what?

    Hmmm... Found it in the docs.. Which raises even more questions:

    1. Doesn't postgres know sports_id is of type integer already?
    2. For readability shouldn't the casting be performed? IE ELSE CAST( sports_id AS int )
    3. Is there a test for NULL? IE. CASE WHEN sports_id IS NULL THEN ...
    4. Is there a function like COALESCE in SQL Server? IE COALESCE( sports_id, 0 ). That essentially says if the field is NULL then 0

    Not meaning to hijack the thread - just trying to learn by it...
    Last edited by caracadon; 10-31-10 at 10:56.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    1. Doesn't postgres know sports_id is of type integer already?
    Yes PostgreSQL does know it's an integer.

    But as this is part of CASE statement that returns a NULL in the first WHEN branch (which does not "have" a data type) this cast is required.
    It might be that this is not needed if the WHEN part that returns the integer is the first in the sequence, but I haven't tested that.

    2. For readability shouldn't the casting be performed? IE ELSE CAST( sports_id AS int )
    That is probably a matter of personal taste. Some people think that more compact expressions are "easier" to read.
    But for portability issues I totally agree and I tend to use CAST() in favour of the :: operator as well (as I work with different DBMS, and CAST() works on almost all of them)

    3. Is there a test for NULL? IE. CASE WHEN sports_id IS NULL THEN ...
    That should be included, yes. (Unless sports_id is a NOT NULL column)

    4. Is there a function like COALESCE in SQL Server? IE COALESCE( sports_id, 0 ). That essentially says if the field is NULL then 0
    Yes, it's called COALESCE()
    http://www.postgresql.org/docs/current/static/functions-conditional.html#AEN15540

  7. #7
    Join Date
    Aug 2010
    Posts
    5
    Thanks for the advice so far, however I need some more...

    Basically, I've cobbled together some bits of code but I don't understand all the parts of what I'm writing. I'm like a tourist reading from a foreign language phrase book without knowing anything deeper about the grammar or other language rules! What this means is that every time I return an error it's bloody hard to work out why it's happened.

    For that reason I'll break this request into two parts. The first concerns the grammar (or should that be the syntax?) of the code below:


    Code:
    INSERT INTO bf_historic_data (Sports_ID, Event_ID, Settled_date, Full_Description, Scheduled_Off, Event, DT_actual_Off, Selection_ID, Selection, Odds, Number_bets, Volume_Matched, Latest_taken, First_taken, Win_Flag, In_play)
    SELECT 
    (CASE WHEN Sports_ID = '' THEN NULL ELSE Sports_ID END)::int,
    (CASE WHEN Event_ID = '' THEN NULL ELSE Event_ID END)::int,
    (CASE WHEN Settled_date = '' THEN NULL ELSE Settled_date END)::timestamp,
    (CASE WHEN Scheduled_Off = '' THEN NULL ELSE Scheduled_Off END)::timestamp,
    (CASE WHEN DT_actual_Off = '' THEN NULL ELSE DT_Actual_Off END)::timestamp,
    (CASE WHEN Selection_ID = '' THEN NULL ELSE Selection_ID END)::int,
    (CASE WHEN Odds = '' THEN NULL ELSE Odds END)::real,
    (CASE WHEN Number_bets = '' THEN NULL ELSE Number_bets END)::int,
    (CASE WHEN Latest_taken = '' THEN NULL ELSE Latest_taken END)::timestamp,
    (CASE WHEN First_taken = '' THEN NULL ELSE First_taken END)::timestamp,
    (CASE WHEN Win_Flag = '' THEN NULL ELSE Win_Flag END)::int,
    Sports_ID, Event_ID, Settled_date, Scheduled_Off, DT_actual_Off, Selection_ID, Odds, Number_bets, Latest_taken, First_taken, Win_Flag
    FROM bf_historic_data_B;
    For the section written in red ink: What am I trying to do by listing the columns here? Is the idea to explicitly say where to SELECT the data from for the SELECT command? Should I therefore be listing all the columns or just those concerned with my "CASE WHENs"? Depending on the answer to these questions I'll no doubt have more.

  8. #8
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    Without knowing much else my first response is does the script work without error? Does it create and populate the bf_historic_data table as desired.

    My suspicion is the script is asking for sports_id, event_id, settled_date, etc twice: once in the case when then else statements and then again in a straight up select list. You should have one or the other not both. But then I am new to PostgreSQL too. But I know this would crash in SQL Server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •