Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016

    Unanswered: Need help in comparing the datatypes by using CAST


    We have a requirement to check if supplied data(.csv files) fits in to our table structure.

    How to achieve this by casting individual fields to table datatype.

    Any insights would be helpful.

    Thanks in advance.


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    You can't operate on a CSV file without making it available in the database server.

    You can do this in two ways: either by using a foreign data wrapper (through file_fdw) or by simply importing the file into a table where all columns are defined as text.

    Once you can access the data through SQL you can use e.g. regular expressions to validate the data format. E.g. for a column that is supposed to be an integer you could do something like this:

    select *
    from staging_table
    where trim(the_potential_integer_column) !~ '^[0-9]+$'
    Similar expressions can be found for numeric columns. For DATE or timestamp columns this is a bit trickier. If you just want to validate the format this can be done using a regular expression, e.g. for the ISO date format, you could use:

    select *
    from staging_table
    where the_potential_date_column !~ '^[12][0-9]{3}-[0-9]{2}-[0-9]{2}$'
    Note that this only validates the format of the data, not the content. e.g. '2017-02-31' would be accepted. If you also need to validate the actual values, you need to write a function that tries to cast the value and catches the exception.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Tags for this Thread

Posting Permissions

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