Skip site navigation (1) Skip section navigation (2)

find out data types using sql or php

From: Tom Hart <tomhart(at)coopfed(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: find out data types using sql or php
Date: 2007-10-26 21:06:47
Message-ID: 472256E7.1000702@coopfed.org (view raw or flat)
Thread:
Lists: pgsql-general
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?

Responses

pgsql-general by date

Next:From: Brian WipfDate: 2007-10-26 21:08:12
Subject: WAL archiving idle database
Previous:From: Chuck D.Date: 2007-10-26 20:17:07
Subject: Re: Geographic data sources, queries and questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group