Обсуждение: 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!
novice <user.postgresql@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote:
> novice <user.postgresql@gmail.com> 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
novice <user.postgresql@gmail.com> 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
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
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
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
Thank you! That was exactly what I was looking for =) On 13/08/07, Paul Lambert <paul.lambert@autoledgers.com.au> 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 >
Paul Lambert <paul.lambert@autoledgers.com.au> 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
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 <tgl@sss.pgh.pa.us> wrote: > Paul Lambert <paul.lambert@autoledgers.com.au> 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 >
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 <user.postgresql@gmail.com> 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 <tgl@sss.pgh.pa.us> wrote:
> > Paul Lambert <paul.lambert@autoledgers.com.au> 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
> >
>
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
On 8/12/07, novice <user.postgresql@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote: > Paul Lambert <paul.lambert@autoledgers.com.au> 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 >