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

    Unanswered: Help to Postgres 8.4

    Hallo

    I have at table containing several columns. One column (A) is of type (character varying, 18) containing numbers as for instance "7813406". All are containing 7 numbers and no decimals!

    I have in the same table a column of type ‘Integer’ also containing numbers for instance 7813999.

    Question:

    How do I convert column (A) to type ‘Integer’. I want to compare columns (A) and (B).

    In the end I want to update the table when column (A) contains a value not found in column (B)

    Column (B) is more often known!!

    Regards
    Korsgaard – Denmark.

    Sorry for my poor English

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    SELECT *
    FROM the_table_with_no_name
    WHERE column_a = column_b::varchar
    Will display all rows where both columns have the same value.

    More details about type conversion can be found in the manual:
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Type Conversion

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    Shammat. Thanks a lot.
    That was the solution of my converting problem.
    How do you go the opposite way (from varchar to Integer)?

    Regards Jens

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by korsgaard View Post
    Shammat. Thanks a lot.
    That was the solution of my converting problem.
    How do you go the opposite way (from varchar to Integer)?
    Same technique:
    Code:
    varchar_column::integer
    or if you want to be more portable (i.e. compatible with other DBMS)
    Code:
    cast(varchar_column AS integer)
    Here is a more detailed description of this: PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Value Expressions

    But the other direction (varchar -> integer) might fail if there are non-numeric characters...

    Why do you store numbers in character columns anyway?
    It is almost always a very bad idea!

  5. #5
    Join Date
    Dec 2010
    Posts
    3

    Postgres 8.4 - converting

    Hallo again.

    Database has been designed in this way. So sorry about that!!

    Thanks for your help. I can now compare the two columns.

    Regards Jens.

Posting Permissions

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