Data loading from a flat file...

On Thu, Jan 05, 2006 at 10:51:55PM -0600, Angshu Kar wrote:

Hi Pgsql,

I've a table schema in pg say as:

(A B C D E)

where none of these fields allow null.

Now, I've a flat file that has got tab-delimited data for B,C and E fields.
And A,B come from sequences.

Could anyone please let me know how I can upload the complete dataset into
the table?

COPY table (b, c, e) FROM file?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Thanks Jim. the statement is running without any error but nothing is
getting copied into the table!

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2
.
.
.

Does anyone know what can it represent?
Is it creating any trouble for the COPY command?
And can we use INSERT with COPY?

Thanks,
Angshu

On 1/5/06, Jim C. Nasby <jnasby@pervasive.com> wrote:

On Thu, Jan 05, 2006 at 10:51:55PM -0600, Angshu Kar wrote:

Hi Pgsql,

I've a table schema in pg say as:

(A B C D E)

where none of these fields allow null.

Now, I've a flat file that has got tab-delimited data for B,C and E

fields.

And A,B come from sequences.

Could anyone please let me know how I can upload the complete dataset

into

the table?

COPY table (b, c, e) FROM file?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

On Thu, Jan 05, 2006 at 22:51:55 -0600,
Angshu Kar <angshu96@gmail.com> wrote:

Hi Pgsql,

I've a table schema in pg say as:

(A B C D E)

where none of these fields allow null.

Now, I've a flat file that has got tab-delimited data for B,C and E fields.
And A,B come from sequences.

Could anyone please let me know how I can upload the complete dataset into
the table?

Use COPY or INSERT with explicitly named columns. (I think this is a
relatively new feature for COPY, but you can use it for INSERT in any version.)

Could you please tell me what's the syntax with INSERT for this?

On 1/5/06, Bruno Wolff III <bruno@wolff.to> wrote:

On Thu, Jan 05, 2006 at 22:51:55 -0600,
Angshu Kar <angshu96@gmail.com> wrote:

Hi Pgsql,

I've a table schema in pg say as:

(A B C D E)

where none of these fields allow null.

Now, I've a flat file that has got tab-delimited data for B,C and E

fields.

And A,B come from sequences.

Could anyone please let me know how I can upload the complete dataset

into

the table?

Use COPY or INSERT with explicitly named columns. (I think this is a
relatively new feature for COPY, but you can use it for INSERT in any
version.)

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

On Thu, Jan 05, 2006 at 10:51:55PM -0600, Angshu Kar wrote:

I've a table schema in pg say as:

(A B C D E)

where none of these fields allow null.

Now, I've a flat file that has got tab-delimited data for B,C and E fields.
And A,B come from sequences.

Could anyone please let me know how I can upload the complete dataset into
the table?

See the documentation for COPY (or \copy in psql). If you need to
adjust sequence values after the load you can use ALTER SEQUENCE
or the setval() function.

--
Michael Fuhr

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but nothing is
getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return? How much data
is there? What version of PostgreSQL are you using and on what
platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters? Are they between fields
as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get COPY
to read it. That should be an easy job for a script (somebody here
can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the appropriate
INSERT commands; that's another scripting job.

--
Michael Fuhr

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux
m/c! The file has about 2GB data.It returns back to the prompt very soon.
I'm using less or vi command to view the file and getting those ^M as
mentioned (i.e. between fields). Any clue how I can massage the data? If you
suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's throwing the
error:
ERROR: could not open file "/home/akar/final.out" for reading: Permission
denied
I've changed the file owner to postgres but without any avail!Also do I need
to change the permission to akar directory? How(I'm a linux freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr <mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but nothing is
getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return? How much data
is there? What version of PostgreSQL are you using and on what
platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters? Are they between fields
as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get COPY
to read it. That should be an easy job for a script (somebody here
can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the appropriate
INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

I've solved the permission issue but now the error is:

ERROR: missing data for column "subject_entry_id"
CONTEXT: COPY distance, line 1: "107128"

I feel the ^M is creating the problem! Any means to remove that? I mean
using the delimiters option?

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my linux
m/c! The file has about 2GB data.It returns back to the prompt very soon.
I'm using less or vi command to view the file and getting those ^M as
mentioned (i.e. between fields). Any clue how I can massage the data? If
you suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's throwing the
error:
ERROR: could not open file "/home/akar/final.out" for reading: Permission
denied
I've changed the file owner to postgres but without any avail!Also do I
need to change the permission to akar directory? How(I'm a linux freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr <mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but nothing is
getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return? How much data
is there? What version of PostgreSQL are you using and on what
platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters? Are they between fields
as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get COPY
to read it. That should be an easy job for a script (somebody here
can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the appropriate
INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding command. But
^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx <username>.

For this command to succeed you need to execute this command as root
or the owner of the directory

Thanks for the chmod command Pandu but the cat command is not doing
anything!

And as Michael suggested that file has indeed carriage returns embedded in
fields. I opened it in my windows m/c using textpad and got:

B1
C1
E1
B2
C2
E2
.
.
.

Any more suggestions on how to solve this? :(

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding command. But
^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx <username>.

For this command to succeed you need to execute this command as root
or the owner of the directory

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my

linux

m/c! The file has about 2GB data.It returns back to the prompt very

soon.

I'm using less or vi command to view the file and getting those ^M as
mentioned (i.e. between fields). Any clue how I can massage the data? If

you

suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's throwing

the

error:
ERROR: could not open file "/home/akar/final.out" for reading:

Permission

denied
I've changed the file owner to postgres but without any avail!Also do I
need to change the permission to akar directory? How(I'm a linux

freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr <mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but nothing

is

getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return? How much data
is there? What version of PostgreSQL are you using and on what
platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters? Are they between fields
as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get COPY
to read it. That should be an easy job for a script (somebody here
can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the appropriate
INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

More problems solved and created - Now I'm getting the error:

null value in column "subject_id" violates not-null constraint

and this is nothing but column A which I talked about in the very beginning!
Since its not null how can I COPY to B C and E. The same problem will arise
with field E too!

AK

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

Thanks for the chmod command Pandu but the cat command is not doing
anything!

And as Michael suggested that file has indeed carriage returns embedded in
fields. I opened it in my windows m/c using textpad and got:

B1
C1
E1
B2
C2
E2
.
.
.

Any more suggestions on how to solve this? :(

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding command. But
^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx <username>.

For this command to succeed you need to execute this command as root
or the owner of the directory

On 1/6/06, Angshu Kar <angshu96@gmail.com > wrote:

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in my

linux

m/c! The file has about 2GB data.It returns back to the prompt very

soon.

I'm using less or vi command to view the file and getting those ^M as
mentioned (i.e. between fields). Any clue how I can massage the data?

If you

suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's throwing

the

error:
ERROR: could not open file "/home/akar/final.out" for reading:

Permission

denied
I've changed the file owner to postgres but without any avail!Also do

I

need to change the permission to akar directory? How(I'm a linux

freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr <mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but nothing

is

getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return? How much data
is there? What version of PostgreSQL are you using and on what
platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters? Are they between fields
as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get COPY
to read it. That should be an easy job for a script (somebody here
can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the

appropriate

INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

cat test | paste - - -

This will merge three consecutive lines into a single line. Note that
there is a space between each -.

Please show the output of describe command of the table

here it is:

Table "public.distance"
Column | Type | Modifiers
----------------------+------------------+-----------
distance_id | integer | not null
query_id | integer | not null
subject_id | integer | not null
distanceparameter_id | integer |
pvalue | double precision | not null
Indexes:
"distance_pkey" primary key, btree (distance_id)
"distance_query_id_key" unique, btree (query_id, subject_id,
distanceparameter_id)
Foreign-key constraints:
"distance_distanceparameter_id_fkey" FOREIGN KEY (distanceparameter_id)
REFERENCES distanceparameter(distanceparameter_id) ON UPDATE RESTRICT ON
DELETE RESTRICT
"distance_query_id_fkey" FOREIGN KEY (query_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT
"distance_subject_id_fkey" FOREIGN KEY (subject_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

Please show the output of describe command of the table

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

More problems solved and created - Now I'm getting the error:

null value in column "subject_id" violates not-null constraint

and this is nothing but column A which I talked about in the very

beginning!

Since its not null how can I COPY to B C and E. The same problem will

arise

with field E too!

AK

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

Thanks for the chmod command Pandu but the cat command is not doing

anything!

And as Michael suggested that file has indeed carriage returns

embedded in

fields. I opened it in my windows m/c using textpad and got:

B1
C1
E1
B2
C2
E2
.
.
.

Any more suggestions on how to solve this? :(

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding command.

But

^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx <username>.

For this command to succeed you need to execute this command as root
or the owner of the directory

On 1/6/06, Angshu Kar < angshu96@gmail.com > wrote:

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB in

my

linux

m/c! The file has about 2GB data.It returns back to the prompt

very

soon.

I'm using less or vi command to view the file and getting those ^M

as

mentioned (i.e. between fields). Any clue how I can massage the

data?

If you

suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's

throwing

the

error:
ERROR: could not open file "/home/akar/final.out" for reading:

Permission

denied
I've changed the file owner to postgres but without any

avail!Also do

I

need to change the permission to akar directory? How(I'm a linux

freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr <mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but

nothing

is

getting copied into the table!

What client are you using and what's the exact command you ran?
Does the command finish or does it never return? How much data
is there? What version of PostgreSQL are you using and on what
platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are you
viewing the file to see these characters? Are they between

fields

as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't get

COPY

to read it. That should be an easy job for a script (somebody

here

can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the

appropriate

INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

Issue the following command before you execute copy

ALTER TABLE DISTANCE ALTER COLUMN <column name which should use seq>
SET DEFAULT nextval('<sequence to be used>')

Btw, what version of postgres are you using?

Thanks a lot Pandu.Everything works ok. Now one last thing : I want to
insert a fixed value to the D field in all rows. Any statement for that?

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

Issue the following command before you execute copy

ALTER TABLE DISTANCE ALTER COLUMN <column name which should use seq>
SET DEFAULT nextval('<sequence to be used>')

Btw, what version of postgres are you using?

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

here it is:

Table "public.distance"
Column | Type | Modifiers
----------------------+------------------+-----------
distance_id | integer | not null
query_id | integer | not null
subject_id | integer | not null
distanceparameter_id | integer |
pvalue | double precision | not null
Indexes:
"distance_pkey" primary key, btree (distance_id)
"distance_query_id_key" unique, btree (query_id, subject_id,
distanceparameter_id)
Foreign-key constraints:
"distance_distanceparameter_id_fkey" FOREIGN KEY
(distanceparameter_id) REFERENCES
distanceparameter(distanceparameter_id) ON UPDATE RESTRICT
ON DELETE RESTRICT
"distance_query_id_fkey" FOREIGN KEY (query_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT
"distance_subject_id_fkey" FOREIGN KEY (subject_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

Please show the output of describe command of the table

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

More problems solved and created - Now I'm getting the error:

null value in column "subject_id" violates not-null constraint

and this is nothing but column A which I talked about in the very

beginning!

Since its not null how can I COPY to B C and E. The same problem

will

arise

with field E too!

AK

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

Thanks for the chmod command Pandu but the cat command is not

doing

anything!

And as Michael suggested that file has indeed carriage returns

embedded in

fields. I opened it in my windows m/c using textpad and got:

B1
C1
E1
B2
C2
E2
.
.
.

Any more suggestions on how to solve this? :(

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding

command.

But

^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx <username>.

For this command to succeed you need to execute this command as

root

or the owner of the directory

On 1/6/06, Angshu Kar < angshu96@gmail.com > wrote:

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the DB

in

my

linux

m/c! The file has about 2GB data.It returns back to the prompt

very

soon.

I'm using less or vi command to view the file and getting

those ^M

as

mentioned (i.e. between fields). Any clue how I can massage

the

data?

If you

suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's

throwing

the

error:
ERROR: could not open file "/home/akar/final.out" for

reading:

Permission

denied
I've changed the file owner to postgres but without any

avail!Also do

I

need to change the permission to akar directory? How(I'm a

linux

freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr < mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar wrote:

Thanks Jim. the statement is running without any error but

nothing

is

getting copied into the table!

What client are you using and what's the exact command you

ran?

Does the command finish or does it never return? How much

data

is there? What version of PostgreSQL are you using and on

what

platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how are

you

viewing the file to see these characters? Are they between

fields

as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't

get

COPY

to read it. That should be an easy job for a script

(somebody

here

can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the

appropriate

INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure

always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

No need for that. Everything works fine. I'm grateful pgsql. And thanks a
ton Pandu
:)

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

Thanks a lot Pandu.Everything works ok. Now one last thing : I want to
insert a fixed value to the D field in all rows. Any statement for that?

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

Issue the following command before you execute copy

ALTER TABLE DISTANCE ALTER COLUMN <column name which should use seq>
SET DEFAULT nextval('<sequence to be used>')

Btw, what version of postgres are you using?

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

here it is:

Table "public.distance"
Column | Type | Modifiers
----------------------+------------------+-----------
distance_id | integer | not null
query_id | integer | not null
subject_id | integer | not null
distanceparameter_id | integer |
pvalue | double precision | not null
Indexes:
"distance_pkey" primary key, btree (distance_id)
"distance_query_id_key" unique, btree (query_id, subject_id,
distanceparameter_id)
Foreign-key constraints:
"distance_distanceparameter_id_fkey" FOREIGN KEY
(distanceparameter_id) REFERENCES
distanceparameter(distanceparameter_id) ON UPDATE RESTRICT
ON DELETE RESTRICT
"distance_query_id_fkey" FOREIGN KEY (query_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT
"distance_subject_id_fkey" FOREIGN KEY (subject_id) REFERENCES
entry(entry_id) ON UPDATE RESTRICT ON DELETE RESTRICT

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com> wrote:

Please show the output of describe command of the table

On 1/6/06, Angshu Kar <angshu96@gmail.com> wrote:

More problems solved and created - Now I'm getting the error:

null value in column "subject_id" violates not-null constraint

and this is nothing but column A which I talked about in the very

beginning!

Since its not null how can I COPY to B C and E. The same problem

will

arise

with field E too!

AK

On 1/6/06, Angshu Kar < angshu96@gmail.com> wrote:

Thanks for the chmod command Pandu but the cat command is not

doing

anything!

And as Michael suggested that file has indeed carriage returns

embedded in

fields. I opened it in my windows m/c using textpad and got:

B1
C1
E1
B2
C2
E2
.
.
.

Any more suggestions on how to solve this? :(

On 1/6/06, Pandurangan R S <pandurangan.r.s@gmail.com > wrote:

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding

command.

But

^V will not be displayed on the screen.

You might need to change directory permission too.

use chmod +rx <username>.

For this command to succeed you need to execute this command

as root

or the owner of the directory

On 1/6/06, Angshu Kar < angshu96@gmail.com > wrote:

Thanks Michael.

I'm using PgAdmin III 1.4.0 from my WinXP m/c to access the

DB in

my

linux

m/c! The file has about 2GB data.It returns back to the

prompt

very

soon.

I'm using less or vi command to view the file and getting

those ^M

as

mentioned (i.e. between fields). Any clue how I can massage

the

data?

If you

suggest I can try and write the script.

Also, now I'm facing another permission related problem!It's

throwing

the

error:
ERROR: could not open file "/home/akar/final.out" for

reading:

Permission

denied
I've changed the file owner to postgres but without any

avail!Also do

I

need to change the permission to akar directory? How(I'm a

linux

freshie)?

Thanks,
Angshu

On 1/5/06, Michael Fuhr < mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 11:04:16PM -0600, Angshu Kar

wrote:

Thanks Jim. the statement is running without any error

but

nothing

is

getting copied into the table!

What client are you using and what's the exact command you

ran?

Does the command finish or does it never return? How much

data

is there? What version of PostgreSQL are you using and on

what

platform?

Also, my data file is showing some ^M chars like

B1^M C1^M E1
B2^M C2^M E2

The ^M sequence might represent a carriage return -- how

are you

viewing the file to see these characters? Are they

between

fields

as shown or only at the ends of lines?

Is it creating any trouble for the COPY command?

Possibly; you might need to massage the data if you can't

get

COPY

to read it. That should be an easy job for a script

(somebody

here

can probably help).

And can we use INSERT with COPY?

To use INSERT you'd need to read the data and generate the

appropriate

INSERT commands; that's another scripting job.

--
Michael Fuhr

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure

always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure

always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--

Ignore the impossible but honor it ...
The only enviable second position is success, since failure always

comes

first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

--
Ignore the impossible but honor it ...
The only enviable second position is success, since failure always comes
first...

Pandurangan R S wrote:

To get rid of ^M characters you could use

cat file | tr -d ^M

you need to type ^V before you type ^M in the preceeding command. But
^V will not be displayed on the screen.

Or you can use dos2unix/unix2dos, if installed. I believe they are in
the sysutils package.

On Thu, Jan 05, 2006 at 23:04:55 -0600,
Angshu Kar <angshu96@gmail.com> wrote:

Could you please tell me what's the syntax with INSERT for this?

http://developer.postgresql.org/docs/postgres/sql-insert.html