From: | Tom Hart <tomhart(at)coopfed(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: find out data types using sql or php |
Date: | 2007-10-26 21:55:08 |
Message-ID: | 4722623C.3040904@coopfed.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Webb Sprague | 2007-10-26 21:59:28 | Re: find out data types using sql or php |
Previous Message | Jeff Davis | 2007-10-26 21:46:59 | Re: WAL archiving idle database |