Thread: Really easy problem...

----- Original Message -----

From: James David Smith

To: pgsql-novice@postgresql.org

Sent: Thursday, August 02, 2012 12:12 PM

Subject: [NOVICE] Really easy problem...

Hi everyone,

 

I'm struggling with something which I'm sure should be REALLY easy. But I can't quite get it to work. I've got a CSV file with two columns which looks like the below:

 

2012-07-15 10:00:00, 1

2012-07-15 10:00:00, 2

2012-07-15 10:00:00, 3

2012-07-15 10:00:00, 4

2012-07-15 10:00:00, 5

 

I want to copy the data into this table:

 

CREATE TABLE master_table_interpolated(
date_time TIMESTAMP WITH TIME ZONE,
person_id INTEGER,
person_location GEOMETRY);

 

So the data from the CSV file should go into the first two columns of my table, and leave the final one blank. I use this query to pull the CSV file in, however I can't figure out how to map the data to the correct columns in my table.

 

COPY master_table_interpolated from 'C:/Program Files/PostgreSQL/9.0/data/basetime.csv' DELIMITERS ',' CSV;

 

Ideas please?

 

Thanks

 

James

On 2 August 2012 12:12, James David Smith <james.david.smith@gmail.com> wrote:

> Ideas please?

http://tapoueh.org/pgsql/pgloader.html

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

----- Original Message -----

From: James David Smith

To: Oliveiros d'Azevedo Cristina

Cc: pgsql-novice@postgresql.org

Sent: Thursday, August 02, 2012 12:51 PM

Subject: Re: [NOVICE] Really easy problem...

Hi Oliver & Craig,

Yes, I tried that. I thought that it would work. It didn't.

However... I've just tried again and it has! Damm. The only thing that has changed from before was I've been playing around with the source CSV file so it must have been formatted incorrectly or a comma in the wrong place or something.

Thanks all,

James

On 2 August 2012 12:26, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:

Howdy, James,

 

Have you tried adding column names?

 

COPY master_table_interpolated (date_time, person_id) ...

 

?

 

Just took a look at this http://www.postgresql.org/docs/9.0/static/sql-copy.html

 

Best,

Oliver

 

----- Original Message -----

From: James David Smith

To: pgsql-novice@postgresql.org

Sent: Thursday, August 02, 2012 12:12 PM

Subject: [NOVICE] Really easy problem...

Hi everyone,

 

I'm struggling with something which I'm sure should be REALLY easy. But I can't quite get it to work. I've got a CSV file with two columns which looks like the below:

 

2012-07-15 10:00:00, 1

2012-07-15 10:00:00, 2

2012-07-15 10:00:00, 3

2012-07-15 10:00:00, 4

2012-07-15 10:00:00, 5

 

I want to copy the data into this table:

 

CREATE TABLE master_table_interpolated(
date_time TIMESTAMP WITH TIME ZONE,
person_id INTEGER,
person_location GEOMETRY);

 

So the data from the CSV file should go into the first two columns of my table, and leave the final one blank. I use this query to pull the CSV file in, however I can't figure out how to map the data to the correct columns in my table.

 

COPY master_table_interpolated from 'C:/Program Files/PostgreSQL/9.0/data/basetime.csv' DELIMITERS ',' CSV;

 

Ideas please?

 

Thanks

 

James