Thread: Export to file
Hi, There is a way to export the result of a query into an external DBF or TXT table? Thank you, MAGO | |||
Attachment
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
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
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
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
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 >
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)
>
>
>
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 >