Re: find out data types using sql or php

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