Re: From SQLite to Postgres

On Wed, 16 Feb 2011, Joshua D. Drake wrote:

> Well Python DBAPI is going to have functions to call that will tell you
> what the data types are so you don't actually have to figure it out. It
> depends on what you are trying to accomplish. If this is a one off thing
> then I would do it all by hand with vi/emacs (I would use joe, but I think
> I am the last user on earth).

Josh,

Yes, this should be a one-off job. I also use joe, but for small files.
For this 88M file I use emacs.

I have the schema at the top of the .sql file:

CREATE TABLE 'welllog' ('sequence_no' INTEGER, 'well_log' INTEGER, 'app'
TEXT, 'notice_of_intent' INTEGER, 'waiver_no' TEXT, 'date_log_rcvd'
DATETIME, 'date_log_rcvd_acc' TEXT, 'site_type' TEXT, 'work_type' TEXT,
'work_type_rmks' TEXT, 'proposed_use' TEXT, 'drilling_method' TEXT, 'sc'
TEXT, 'ha' TEXT, 'twn' TEXT, 'legal_twn' TEXT, 'rng' TEXT, 'legal_rng' TEXT,
'sec' TEXT, 'sec_quarters' TEXT, 'legal_quarters' TEXT, 'quarters_seq' TEXT,
'ref' TEXT, 'latitude' DOUBLE, 'longitude' DOUBLE, 'lat_long_src' TEXT,
'lat_long_acc' TEXT, 'owner_current' TEXT, 'owner_address' TEXT, 'owner_no'
TEXT, 'parcel_no' TEXT, 'subdivision_name' TEXT, 'lot_no' TEXT, 'block_no'
TEXT, 'well_finish_date' DATETIME, 'date_cmplt_acc' TEXT, 'gravel_packed'
TEXT, 'depth_seal' INTEGER, 'depth_drilled' INTEGER, 'depth_bedrock'
INTEGER, 'aquifer_desc' TEXT, 'depth_cased' INTEGER, 'csng_diameter' DOUBLE,
'csng_reductions' INTEGER, 'top_perf' INTEGER, 'bottom_perf' INTEGER,
'perf_intervals' INTEGER, 'static_wl' DOUBLE, 'temperature' DOUBLE, 'yield'
DOUBLE, 'drawdown' DOUBLE, 'hours_pumped' DOUBLE, 'test_method' TEXT,
'qual_const_data' TEXT, 'qual_lith_data' TEXT, 'remarks' TEXT,
'remarks_additional' TEXT, 'contractor_lic_no' TEXT, 'contractor_name' TEXT,
'contractor_address' TEXT, 'contractor_drlr_no' INTEGER, 'driller_lic_no'
INTEGER, 'source_agency' TEXT, 'user_id' TEXT, 'date_entry' DATETIME,
'update_user_id' TEXT, 'date_update' DATETIME, 'edit_status' TEXT,
'well_start_date' DATETIME, 'gravel_pack_top' INTEGER, 'gravel_pack_bot'
INTEGER, 'utm_x' DOUBLE, 'utm_y' DOUBLE);

I suppose I'll just change the delimiter to ':', remove all the single
quotes, and close up empty spaces. Do explicit NULLs cause any problems?

Thanks,

Rich