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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
