Find rows which contain non-numeric values in a postgresql database

Question

I have a table which contains data gathered from meteorological observations. The data where ill prepared so they are mistakes when they were inserted to the database. In a recent browse i've found non-numerical data where they should't be any.

How I can select the rows where non-numerical values exist?

The table's layout is as follows:

#\d data.tempe
               Table "data.tempe"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 id-station | integer               | 
 year       | integer               | 
 t1         | character varying(25) | 
 t2         | character varying(25) | 
 t3         | character varying(25) | 
 t4         | character varying(25) | 
 t5         | character varying(25) | 
 t6         | character varying(25) | 
 t7         | character varying(25) | 
 t8         | character varying(25) | 
 t9         | character varying(25) | 
 t10        | character varying(25) | 
 t11        | character varying(25) | 
 t12        | character varying(25) |

or another solution would be to change non-numeric values to a predefined value — user528025
– user528025, Commented Jul 18, 2012 at 22:22

Dmitry Buslaev · Accepted Answer · 2016-09-16 05:38:16Z

13

WHERE field !~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'

answered Sep 16, 2016 at 5:38

Dmitry Buslaev

3003 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bruno · Accepted Answer · 2012-07-18 22:50:45Z

6

From this message on the PostgreSQL mailing list, you should be able to use a regular expression.

For example, something like this should nullify the invalid values in t1:

UPDATE data.tempe SET t1=NULL
    WHERE t1 !~ E'^[+-]?[0-9]+(\\\\.[0-9]*)?([Ee][+-]?[0-9]+)?\$';

(The regular expression may depend on the data format you expect.)

answered Jul 18, 2012 at 22:50

Bruno

123k33 gold badges278 silver badges389 bronze badges

3 Comments

user528025 Over a year ago

hmm regedit? how about then for UPDATE data.tempe SET t1=NULL WHERE t1 !~ E'[^0-9]';

Bruno Over a year ago

What do you mean "regedit"? No, E'[^0-9] would exclude anything that contains a number. E'^[0-9]+\$' perhaps for positive integers.

user528025 Over a year ago

heh sorry a bit tired, I meant "regexp". Your original suggestion along with a bit of tinkering should do the trick. I'll try it tomorrow and I'll post the results!

Collectives™ on Stack Overflow

Find rows which contain non-numeric values in a postgresql database

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related