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

    Unanswered: An error message reads that something cast as an int should be a timestamp. Why?

    First up let me apologise for the imprecise description of my problem. I am a complete postgres beginner and so far have just been doing things by rote as they appear in the Postgres tutorials, as a result it's difficult to describe my problem. Here goes anyway:

    I created two tables as follows:

    Table 1
    CREATE TABLE bf_historic_data (Sports_ID int, -- Betfair sports id Event_ID int, -- Betfair event id Settled_date timestamp,
    Full_Description varchar(256),
    Scheduled_Off timestamp,
    Event varchar(256), -- Market e.g. Mtch Odds
    DT_Actual_Off timestamp,
    Selection_ID int, -- Betfair Bet ID
    Selection varchar(256), -- who was bet on
    Odds real,
    Number_bets int,
    Volume_Matched real,
    Latest_taken timestamp,
    First_taken timestamp,
    Win_Flag int,
    In_play varchar(256)
    );

    Table 2
    CREATE TABLE bf_historic_data_B (Sports_ID int, -- Betfair sports id Event_ID int, -- Betfair event id Settled_date text,
    Full_Description varchar(256),
    Scheduled_Off text,
    Event varchar(256), -- Market e.g. Mtch Odds
    DT_Actual_Off text,
    Selection_ID int, -- Betfair Bet ID
    Selection varchar(256), -- who was bet on
    Odds real,
    Number_bets int,
    Volume_Matched real,
    Latest_taken text,
    First_taken text,
    Win_Flag int,
    In_play varchar(256)
    );

    The difference between tables 1 and 2 is that where in table 1 I gave a column the type timestamp in table 2 I gave it type text.

    I then copied my raw data successfully into table 2.

    Then I entered the following command:

    INSERT INTO bf_historic_data SELECT (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 Latest_taken = '""' THEN NULL ELSE Latest_taken END)::timestamp, (CASE WHEN First_taken = '""' THEN NULL ELSE First_taken END)::timestamp, Sports_ID, Event_ID, Full_Description, Event, Selection_ID, Selection, Odds, Number_bets, Volume_Matched, Win_Flag, In_play FROM bf_historic_data_B;

    The purpose being to recast my type texts as type timestamps allowing for the double quotes values which represent Null in the raw data. Here's a sample of my raw data:

    "SPORTS_ID","EVENT_ID","SETTLED_DATE","FULL_DESCRI PTION","SCHEDULED_OFF","EVENT","DT ACTUAL_OFF","SELECTION_ID","SELECTION","ODDS","NUM BER_BETS","VOLUME_MATCHED","LATEST_TAKEN","FIRST_T AKEN","WIN_FLAG","IN_PLAY"
    "1","100290000","","French Soccer/Coupe de France/Fixtures 04 January /Blagnac v Monaco/Total Goals","04-01-2009 13:45","","04-01-2009 13:47:34","285471","3 goals or more","2","2","13.44","04-01-2009 13:37:43","04-01-2009 13:37:43","0","PE"

    When I execute the command I am told:

    ERROR: column "sports_id" is of type integer but expression is of type timestamp without time zone
    LINE 1: INSERT INTO bf_historic_data SELECT (CASE WHEN Settled_date ...
    ^
    HINT: You will need to rewrite or cast the expression.

    But that isn't right as column sports_id is always a number string of maximum length 7 digits.

    Can anyone tell me what the issue is?

    Many thanks in advance!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The columns from the select do not match the columns in the table. You need to qualify the target columns in the INSERT statement:
    Code:
    INSERT INTO (col1, col2, col3) SELECT ...
    And please use [code] tags when posting SQL statements

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    Thanks very much for that

Posting Permissions

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