Thread: Export to file

Export to file

From
"Marvin"
Date:
Hi,
There is a way to export the result
of a query into an external DBF or TXT table?
 
Thank you,
 
 
MAGO
_________________________________________________________________
  IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Attachment

Re: Export to file

From
Oliver Elphick
Date:
On Tue, 2003-09-09 at 17:15, Marvin wrote:
> Hi,
> There is a way to export the result
> of a query into an external DBF or TXT table?
>

You can't write out a DBF file without some other software, but writing
a text file is easy.  In an interactive session of psql, use:

        \o filename

You probably also want:

        \pset format unaligned
        \pset fieldsep ','
        \t

That would produce a text file with comma-separated values and no
headings.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Draw near to God and he will draw near to you.
      Cleanse your hands, you sinners; and purify your
      hearts, you double minded."       James 4:8


Re: Export to file

From
"A.Bhuvaneswaran"
Date:
psql=> \o filename.txt
psql=> select query;
psql=> \o

regards,
bhuvaneswaran

On Tue, 9 Sep 2003, Marvin wrote:

> Hi,
> There is a way to export the result
> of a query into an external DBF or TXT table?
>
> Thank you,
>
>
> MAGO


Re: Export to file

From
Reece Hart
Date:
On Tue, 2003-09-09 at 09:15, Marvin wrote:
>
> There is a way to export the result
> of a query into an external DBF or TXT table?
>

I often do something like this:
$ psql -At -c 'select ... where ...'

This is so handy, that I have a (bash) shell alias for it:
$ alias csbq='psql -UPUBLIC -dcsb -At -c'
then
$ csbq 'select ...'

-Reece

--
Reece Hart, Ph.D.                       rkh@gene.com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece@in-machina.com, GPG: 0x25EC91A0

Cross-DB linking?

From
Andrew Biagioni
Date:
I am thinking of separating my data into various DBs (maybe on the same server,
probably not) -- mostly for performance/stability/backup reasons -- but I have
a considerable amount of foreign keys, views, and queries that would need to
work across DBs if I were to split things the way I want to.

Is it possible to have foreign keys / views / queries work across database
boundaries?  On the same server / on separate servers?  If so, how?

For example, I have:
 - a table, A, with > 200 K rows which never changes;
 - another table, B with < 10 K rows which changes frequently;
 - and a third table, C, which joins A and B, i.e. has foreign keys into A and
B, and changes rarely

I would like to have A in one DB, dbA (possibly its own server);  B in another
DB, dbB (possibly its own server);  and C either with A or with B (this one is
not an issue per se).

What I'm looking to gain is:
 - dbA would be backed up/replicated religiously, and possibly on a server
optimized for frequent writes
 - dbB would NEVER be backed up, possibly on a server optimized for cacheing
 - each database's schema would be simpler and easier to manage
 - as the number of records and users grow, be able to distribute the
computing/storage/memory load among various machines rather than have to
upgrade the hardware

Thanks in advance!

        Andrew




Re: Cross-DB linking?

From
"scott.marlowe"
Date:
You might want to consider using schemas to accomplish some of this.

You can backup individual schemas as of 7.4 (maybe 7.3, but I've not used
it in production, waiting for 7.4 to upgrade from 7.2)

performance will almost certainly suffer if you are doing cross db work,
so schemas help there.

I've never had any stability issues with Postgresql, and certainly not
from having everything in one database.

Other than the ability to spread your load across multiple machines,
7.3/7.4 and schemas should address all your concerns.

And no, you can't fk across databases.  You can get some primitive (but
quite functional) cross database action with the contrib/dblink package.

On Thu, 11 Sep 2003, Andrew Biagioni wrote:

> I am thinking of separating my data into various DBs (maybe on the same server,
> probably not) -- mostly for performance/stability/backup reasons -- but I have
> a considerable amount of foreign keys, views, and queries that would need to
> work across DBs if I were to split things the way I want to.
>
> Is it possible to have foreign keys / views / queries work across database
> boundaries?  On the same server / on separate servers?  If so, how?
>
> For example, I have:
>  - a table, A, with > 200 K rows which never changes;
>  - another table, B with < 10 K rows which changes frequently;
>  - and a third table, C, which joins A and B, i.e. has foreign keys into A and
> B, and changes rarely
>
> I would like to have A in one DB, dbA (possibly its own server);  B in another
> DB, dbB (possibly its own server);  and C either with A or with B (this one is
> not an issue per se).
>
> What I'm looking to gain is:
>  - dbA would be backed up/replicated religiously, and possibly on a server
> optimized for frequent writes
>  - dbB would NEVER be backed up, possibly on a server optimized for cacheing
>  - each database's schema would be simpler and easier to manage
>  - as the number of records and users grow, be able to distribute the
> computing/storage/memory load among various machines rather than have to
> upgrade the hardware
>
> Thanks in advance!
>
>         Andrew
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>


Re: Cross-DB linking?

From
Andrew Biagioni
Date:
Thanks -- I haven't looked at schemas, I guess I will now :-).

As for stability -- I was referring to the hardware breaking down, not
Postgresql!

    Andrew


9/11/03 5:24:50 PM, "scott.marlowe" <scott.marlowe@ihs.com> wrote:

>You might want to consider using schemas to accomplish some of this.
>
>You can backup individual schemas as of 7.4 (maybe 7.3, but I've not used
>it in production, waiting for 7.4 to upgrade from 7.2)
>
>performance will almost certainly suffer if you are doing cross db work,
>so schemas help there.
>
>I've never had any stability issues with Postgresql, and certainly not
>from having everything in one database.
>
>Other than the ability to spread your load across multiple machines,
>7.3/7.4 and schemas should address all your concerns.
>
>And no, you can't fk across databases.  You can get some primitive (but
>quite functional) cross database action with the contrib/dblink package.
>
>On Thu, 11 Sep 2003, Andrew Biagioni wrote:
>
>> I am thinking of separating my data into various DBs (maybe on the same
server,
>> probably not) -- mostly for performance/stability/backup reasons -- but I
have
>> a considerable amount of foreign keys, views, and queries that would need to
>> work across DBs if I were to split things the way I want to.
>>
>> Is it possible to have foreign keys / views / queries work across database
>> boundaries?  On the same server / on separate servers?  If so, how?
>>
>> For example, I have:
>>  - a table, A, with > 200 K rows which never changes;
>>  - another table, B with < 10 K rows which changes frequently;
>>  - and a third table, C, which joins A and B, i.e. has foreign keys into A
and
>> B, and changes rarely
>>
>> I would like to have A in one DB, dbA (possibly its own server);  B in
another
>> DB, dbB (possibly its own server);  and C either with A or with B (this one
is
>> not an issue per se).
>>
>> What I'm looking to gain is:
>>  - dbA would be backed up/replicated religiously, and possibly on a server
>> optimized for frequent writes
>>  - dbB would NEVER be backed up, possibly on a server optimized for cacheing
>>  - each database's schema would be simpler and easier to manage
>>  - as the number of records and users grow, be able to distribute the
>> computing/storage/memory load among various machines rather than have to
>> upgrade the hardware
>>
>> Thanks in advance!
>>
>>         Andrew
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if your
>>       joining column's datatypes do not match
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>




Re: Cross-DB linking?

From
"scott.marlowe"
Date:
On Thu, 11 Sep 2003, Andrew Biagioni wrote:

> Thanks -- I haven't looked at schemas, I guess I will now :-).

Schemas rock.  Like little sandboxes for each user with their own play
areas and what not.

> As for stability -- I was referring to the hardware breaking down, not
> Postgresql!

Ahhh.  I see. You might want to look into the erserver replication
application on gborg.postgresql.org.  That's a pretty nice little system,
and the .org and .info domains run on top of postgresql using it, so it
has had plenty of production testing.


>
>     Andrew
>
>
> 9/11/03 5:24:50 PM, "scott.marlowe" <scott.marlowe@ihs.com> wrote:
>
> >You might want to consider using schemas to accomplish some of this.
> >
> >You can backup individual schemas as of 7.4 (maybe 7.3, but I've not used
> >it in production, waiting for 7.4 to upgrade from 7.2)
> >
> >performance will almost certainly suffer if you are doing cross db work,
> >so schemas help there.
> >
> >I've never had any stability issues with Postgresql, and certainly not
> >from having everything in one database.
> >
> >Other than the ability to spread your load across multiple machines,
> >7.3/7.4 and schemas should address all your concerns.
> >
> >And no, you can't fk across databases.  You can get some primitive (but
> >quite functional) cross database action with the contrib/dblink package.
> >
> >On Thu, 11 Sep 2003, Andrew Biagioni wrote:
> >
> >> I am thinking of separating my data into various DBs (maybe on the same
> server,
> >> probably not) -- mostly for performance/stability/backup reasons -- but I
> have
> >> a considerable amount of foreign keys, views, and queries that would need to
> >> work across DBs if I were to split things the way I want to.
> >>
> >> Is it possible to have foreign keys / views / queries work across database
> >> boundaries?  On the same server / on separate servers?  If so, how?
> >>
> >> For example, I have:
> >>  - a table, A, with > 200 K rows which never changes;
> >>  - another table, B with < 10 K rows which changes frequently;
> >>  - and a third table, C, which joins A and B, i.e. has foreign keys into A
> and
> >> B, and changes rarely
> >>
> >> I would like to have A in one DB, dbA (possibly its own server);  B in
> another
> >> DB, dbB (possibly its own server);  and C either with A or with B (this one
> is
> >> not an issue per se).
> >>
> >> What I'm looking to gain is:
> >>  - dbA would be backed up/replicated religiously, and possibly on a server
> >> optimized for frequent writes
> >>  - dbB would NEVER be backed up, possibly on a server optimized for cacheing
> >>  - each database's schema would be simpler and easier to manage
> >>  - as the number of records and users grow, be able to distribute the
> >> computing/storage/memory load among various machines rather than have to
> >> upgrade the hardware
> >>
> >> Thanks in advance!
> >>
> >>         Andrew
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 9: the planner will ignore your desire to choose an index scan if your
> >>       joining column's datatypes do not match
> >>
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>