copy command - date

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 |

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 =)

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'?

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'?

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

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