Re: [GENERAL] copy command - date
Thanks again guys =) I've managed to use temp table to load the data and create new table/s Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'? On 13/08/07, Tom Lane [EMAIL PROTECTED] wrote: Paul Lambert [EMAIL PROTECTED] writes: novice wrote: db5= \copy maintenance FROM test.txt I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. Well, it does, but you have to specify which ones are being provided, eg \copy tab(col1,col4,col7, ... But the long and the short of it is that COPY doesn't see any column delimiters at all in this file. We're guessing as to what the OP intends the columns to be, but whatever he wants, he needs something other than an uncertain number of spaces to separate them ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] copy command - date
On 8/12/07, novice [EMAIL PROTECTED] wrote: I resolved it by doing this - is there another more efficient method? And yes, the text file I am working with doesn't have any TABs 5162 OK SM 06/12/04 06:12 substr(data, 30, 2)||'-'||substr(data, 27, 2)||'-20'||substr(data, 24, 2)||substr(data, 32, 6) as inspection_date I didn't have to do anything special, just copied it in: create table g (ts timestamp); set datestyle=ISO, MDY; copy g (ts) from stdin; 06/12/04 12:00:00 \. select * from g; ts - 2004-06-12 12:00:00 delete from g; set datestyle=ISO, DMY; copy g (ts) from stdin; 06/12/04 12:00:00 \. select * from g; ts - 2004-12-06 12:00:00 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] copy command - date
What is the best method to load the following?
I'm having trouble loading the date field. Should I convert it first
or should I be using a text processor before loading the data in?
3665 OK SM 07/07/13 06:09
5162 OK SM 07/02/12 06:10
3665 OK SM 07/06/19 06:10
Table pm.maintenance
Column | Type |
Modifiers
-+--+--
maintenance_id | integer | not null default
nextval('maintenance_maintenance_id_seq'::regclass)
meter_id| integer |
status | character(11)|
inspection_date | timestamp with time zone |
Indexes:
maintenance_pkey PRIMARY KEY, btree (maintenance_id)
Thanks!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] copy command - date
novice [EMAIL PROTECTED] writes: I'm having trouble loading the date field. Should I convert it first or should I be using a text processor before loading the data in? 3665 OK SM 07/07/13 06:09 5162 OK SM 07/02/12 06:10 3665 OK SM 07/06/19 06:10 What sort of trouble, exactly? I'm guessing that you might need to set DateStyle to tell Postgres what the date field ordering is, but without seeing any error messages that's strictly a guess. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] copy command - date
I'm using pg version 8.2.4. What is the best method to load this data?
I have just a little over 55,000 entries.
db5= \copy maintenance FROM test.txt
ERROR: invalid input syntax for integer: 3665 OK SM
07/07/13 06:09
CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK
SM 07/07/13 06:09
Table pm.maintenance
Column | Type |
Modifiers
-+--+--
maintenance_id | integer | not null default
nextval('maintenance_maintenance_id_seq'::regclass)
meter_id| integer |
status | character(3) |
inspector | character(2) |
inspection_date | timestamp with time zone |
On 13/08/07, Tom Lane [EMAIL PROTECTED] wrote:
novice [EMAIL PROTECTED] writes:
I'm having trouble loading the date field. Should I convert it first
or should I be using a text processor before loading the data in?
3665 OK SM 07/07/13 06:09
5162 OK SM 07/02/12 06:10
3665 OK SM 07/06/19 06:10
What sort of trouble, exactly?
I'm guessing that you might need to set DateStyle to tell Postgres what
the date field ordering is, but without seeing any error messages that's
strictly a guess.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] copy command - date
novice [EMAIL PROTECTED] writes: db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 It looks to me like your problem is mostly that you don't have tabs between the fields. I don't think COPY can be taught to parse this input directly --- you need to preprocess the file to split the fields apart. BTW: after you get it split into fields, you're also going to find that OK is not valid input for the integer meter_id column. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] copy command - date
novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 That's not complaining about the date, that is complaining that your input file does not contain the maintenance_id column. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] copy command - date
On Aug 12, 2007, at 20:49 , novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 I'd say your tabs have been converted to spaces so the COPY command is not delimiting the fields as you expect. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] copy command - date
Paul Lambert wrote: novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 That's not complaining about the date, that is complaining that your input file does not contain the maintenance_id column. I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. You could try something like: -- Create a temp table with everything but the sequence column. CREATE TABLE maintenance_load AS SELECT meter_id,status,inspector,inspection_date FROM maintenance WHERE 1=0; -- Copy data from file into temp table. COPY maintenance_load FROM 'd:/temp/file.txt'; -- Insert data from temp table into main table, which will -- generate the value for the sequence field. INSERT INTO maintenance (meter_id,status,inspector,inspection_date) (SELECT * from maintenance_load); -- Drop temp table. DROP TABLE maintenance_load; Also, not sure if it was your mail client or not, but the data you have supplied was space-separated, you probably want to make sure the actual data file is tab-separated, otherwise it's going to think it's all part of one field. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] copy command - date
Thank you! That was exactly what I was looking for =) On 13/08/07, Paul Lambert [EMAIL PROTECTED] wrote: Paul Lambert wrote: novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 That's not complaining about the date, that is complaining that your input file does not contain the maintenance_id column. I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. You could try something like: -- Create a temp table with everything but the sequence column. CREATE TABLE maintenance_load AS SELECT meter_id,status,inspector,inspection_date FROM maintenance WHERE 1=0; -- Copy data from file into temp table. COPY maintenance_load FROM 'd:/temp/file.txt'; -- Insert data from temp table into main table, which will -- generate the value for the sequence field. INSERT INTO maintenance (meter_id,status,inspector,inspection_date) (SELECT * from maintenance_load); -- Drop temp table. DROP TABLE maintenance_load; Also, not sure if it was your mail client or not, but the data you have supplied was space-separated, you probably want to make sure the actual data file is tab-separated, otherwise it's going to think it's all part of one field. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] copy command - date
Paul Lambert [EMAIL PROTECTED] writes: novice wrote: db5= \copy maintenance FROM test.txt I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. Well, it does, but you have to specify which ones are being provided, eg \copy tab(col1,col4,col7, ... But the long and the short of it is that COPY doesn't see any column delimiters at all in this file. We're guessing as to what the OP intends the columns to be, but whatever he wants, he needs something other than an uncertain number of spaces to separate them ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] copy command - date
Thanks again guys =) I've managed to use temp table to load the data and create new table/s Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'? On 13/08/07, Tom Lane [EMAIL PROTECTED] wrote: Paul Lambert [EMAIL PROTECTED] writes: novice wrote: db5= \copy maintenance FROM test.txt I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. Well, it does, but you have to specify which ones are being provided, eg \copy tab(col1,col4,col7, ... But the long and the short of it is that COPY doesn't see any column delimiters at all in this file. We're guessing as to what the OP intends the columns to be, but whatever he wants, he needs something other than an uncertain number of spaces to separate them ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] copy command - date
I resolved it by doing this - is there another more efficient method? And yes, the text file I am working with doesn't have any TABs 5162 OK SM 06/12/04 06:12 substr(data, 30, 2)||'-'||substr(data, 27, 2)||'-20'||substr(data, 24, 2)||substr(data, 32, 6) as inspection_date On 13/08/07, novice [EMAIL PROTECTED] wrote: Thanks again guys =) I've managed to use temp table to load the data and create new table/s Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'? On 13/08/07, Tom Lane [EMAIL PROTECTED] wrote: Paul Lambert [EMAIL PROTECTED] writes: novice wrote: db5= \copy maintenance FROM test.txt I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. Well, it does, but you have to specify which ones are being provided, eg \copy tab(col1,col4,col7, ... But the long and the short of it is that COPY doesn't see any column delimiters at all in this file. We're guessing as to what the OP intends the columns to be, but whatever he wants, he needs something other than an uncertain number of spaces to separate them ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] copy command - date
novice schrieb: I resolved it by doing this - is there another more efficient method? And yes, the text file I am working with doesn't have any TABs 5162 OK SM 06/12/04 06:12 substr(data, 30, 2)||'-'||substr(data, 27, 2)||'-20'||substr(data, 24, 2)||substr(data, 32, 6) as inspection_date You could try to_date() - see: http://www.postgresql.org/docs/8.2/static/functions-formatting.html Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
