Re: find out data types using sql or php - Mailing list 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