Обсуждение: find out data types using sql or php
Hey guys. This is probably a n00b question, but here goes anyway. I have a set of csv files that I COPY t o a number of import tables (same field layout as live tables, but with all datatypes 'text') then use an INSERT INTO ... SELECT FROM statement to transfer the rows over to the live table (at times filtering out, at times not). Unfortunately if any of the data is not type perfect (letters in number fields, etc.) then the entire query bombs and nothing gets loaded. What I'd like to do is add a field is_ok and then use sql or php (or whatever else, if there's an easier way) to determine the field datatype (text, numeric, bool, etc.) and then use some regex or something along those lines to attempt to verify that the data is good, and then mark the is_ok field (obviously a bool) as true, and use is_ok = TRUE in the insert/select statement. Can somebody give me a push in the right direction?
Replying to yourself is so depressing... Anyway, I managed to google myself into a solution, I just wanted to share it with the list in case anybody else was interested. Using the INFORMATION SCHEMA and a query like SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'table'; I get results similar to column_name | data_type -------------------+----------- atm_acct_mess | text atm_acct_num | numeric atm_acct_tp1 | text atm_acct_tp2 | text atm_acct_tp3 | text atm_acct_tp4 | text atm_acct_tp5 | text atm_acct_tp6 | text atm_acct1_stcd | text atm_acct2_stcd | text atm_acct3_stcd | text atm_acct4_stcd | text atm_acct5_stcd | text atm_acct6_stcd | text atm_atm/ach_cd | integer atm_atm/ach_id | numeric atm_atm/ach_tp | integer atm_cn_num | integer atm_date_opened | date atm_id1 | text atm_id2 | text atm_id3 | text atm_id4 | text atm_id5 | text atm_id6 | text atm_last_act_date | date atm_next_rec | integer atm_stat_cd | integer atm_trn_acct_id | text atm_trn_acct_num | numeric atm_trn_acct_tp | text atm_trn_cn_num | integer atm_trn_date | date atm_trn_reg_e | integer atm_trn_term_id | text atm_trn_trace | text atm_trn_trn_num | integer (37 rows) Which I can then of course parse with php and do some testing from there. I hope this helps somebody, I know I could have used this information about 20 minutes ago :-) Tom Hart wrote: > Hey guys. This is probably a n00b question, but here goes anyway. > > I have a set of csv files that I COPY t o a number of import tables > (same field layout as live tables, but with all datatypes 'text') then > use an INSERT INTO ... SELECT FROM statement to transfer the rows over > to the live table (at times filtering out, at times not). > Unfortunately if any of the data is not type perfect (letters in > number fields, etc.) then the entire query bombs and nothing gets > loaded. What I'd like to do is add a field is_ok and then use sql or > php (or whatever else, if there's an easier way) to determine the > field datatype (text, numeric, bool, etc.) and then use some regex or > something along those lines to attempt to verify that the data is > good, and then mark the is_ok field (obviously a bool) as true, and > use is_ok = TRUE in the insert/select statement. Can somebody give me > a push in the right direction? > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
> ... to determine the field datatype (text, numeric, > bool, etc.) I am not sure if this helps, but you can dig around in the system files (pg_catalog.*), and probably write a query that gets the types of every column in the data table you want to insert to. Not a big push, but maybe it will get you started. I don't have links at hand -- sorry. > and then use some regex or something along those lines to > attempt to verify that the data is good, and then mark the is_ok field > (obviously a bool) as true, and use is_ok = TRUE in the insert/select > statement. Can somebody give me a push in the right direction? This sounds like a lot of programming, but that you have the right idea. I am not sure how you would use the "is_ok" within SQL; I would probably only try to insert data that is ok, and filter that in the application. I might also think in terms of wrapping everything in a transaction, assuming it is all good, and then rolling back and catching your exception and giving the user a decent error -- such as "reformat your spreadsheet, doofus, and try uploading again"... Very doable with psycopg2 and python. -W > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On Oct 26, 2007, at 16:06 , Tom Hart wrote: > What I'd like to do is add a field is_ok and then use sql or php > (or whatever else, if there's an easier way) to determine the field > datatype (text, numeric, bool, etc.) and then use some regex or > something along those lines to attempt to verify that the data is > good, and then mark the is_ok field (obviously a bool) as true, and > use is_ok = TRUE in the insert/select statement. This is one way to do it. Another option would be to process the csv file beforehand using your favorite scripting language and COPY into tables with the appropriate, expected datatypes. You know you haven't processed the text file adequately if the COPY fails. If you're going to do the cleaning inside the database (i.e., all text fields), I'd stay inside the database using SQL and PL functions rather than connecting to the database via some external script, process the data, and stick it back in: that's just added overhead, and if the process is automated enough to the point you can write a script to handle it, pretty much anything you can do in an external script you can do inside the database without the overhead of round- tripping out and back. However, if the cleaning is going to take significant user interaction, you might consider using a simple web app that would connect to the database so a user could view and clean the data. I did this with some success for manually checking if names in an imported file were properly split along given and family name lines. Michael Glaesemann grzm seespotcode net