Re: find out data types using sql or php
От | Tom Hart |
---|---|
Тема | Re: find out data types using sql or php |
Дата | |
Msg-id | 4722623C.3040904@coopfed.org обсуждение исходный текст |
Ответ на | find out data types using sql or php (Tom Hart <tomhart@coopfed.org>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: