Thursday, April 7, 2011

(From MySQL to postgresql / sql in action) -> sed utility/regExp


  • You know I always speak my mind, so I asked professor Andrade today: who uses postgresql anyway? He named several public organizations and a couple of businesses here in México, so I was like: mmm, ok then, that sounds good to me . Here's the official list, containing the best known organizations using postgresql: http://www.postgresql.org/about/users, so postgresql is quite something for that matter. This link is  mind blowing: http://www.sun.com/bigadmin/features/articles/switching_to_postgres.jsp?cid=e4390

  • It's quite a coincidence but I happened to find the infamous iraq war logs in my disk drive (a mysql sqldump weighing about 360 MB), I have no idea of how did they get there (really!), but let's use it for didactical  purposes.

  • The first thing I wan't to do is to transform this mysql dump file  into a working postgresql database. So I'm using this guide: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL <-didn't work too many errors

  • Ok let's give this perl script a try:  http://www.pgsql.com/download/mysql2pgsql.tar.gz, by postgres.  <- didn't work either


I'm learning to use the sed utility (to fix those idiomatic errors) , found a marvelous tutorial here:

http://www.grymoire.com/Unix/Sed.html#uh-0

and on regular expressions:

http://www.grymoire.com/Unix/Regular.html

I just love wonderful people making wonderful tutorials!

  • To finish this post, I'll say that from all the things we learned today the most important were:



  1. The case clause. We used it to say: if this guy is over 21 years, give me the word : "adult", else give me the word "minor". i.e: SELECT * , CASE when age>21 then 'adult' else 'minor' end FROM friends;

  2. 1::integer -> sort of a cast

  3. ILIKE. for case insensitive regular expressions

  4. Coalesce. Returns the first not null field. <- need to review it, I didn't quite get it =-D.

No comments:

Post a Comment