Re: Data loading from a flat file...

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

On 1/6/06, Angshu Kar <angshu96(at)gmail(dot)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(dot)r(dot)s(at)gmail(dot)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(at)gmail(dot)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(dot)r(dot)s(at)gmail(dot)com> wrote:
> > > > Please show the output of describe command of the table
> > > >
> > > > On 1/6/06, Angshu Kar <angshu96(at)gmail(dot)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(at)gmail(dot)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(dot)r(dot)s(at)gmail(dot)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(at)gmail(dot)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(at)fuhr(dot)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...