Re: [HACKERS] CSV mode option for pg_dump
> From: Rod Taylor [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 13, 2006 11:31 AM > > > On Mon, 2006-06-12 at 16:28 -0400, Bill Bartlett wrote: > > Can't -- the main production database is over at a CoLo site with > > access only available via SSH, and tightly-restricted SSH at that. > > Generally one of the developers will SSH over to the > server, pull out > > whatever data is needed into a text file via psql or > pg_dump, scp the > > file(s) back here and send them to the user. > > I don't get it. If you can use psql then you already have csv support. > > psql -c 'COPY pg_class TO STDOUT WITH CSV' postgres > pg_class.csv Ah - RTF-UPDATED-M on my part. Most of my systems are still in PG 7.4.x databases so I tend to stick with the 7.x docs, and I didn't notice the "WITH CSV" option added in 8.0. That, plus temp tables, will be very useful. However, I also agree with the need for a new "pg_query / pg_export" program. A program geared solely towards exporting the results of a query would allow many of the options that are needed for the ever-growing variety of output formats (XML, CSV, HTML, XHTML, etc.) and details for each format without needing to clutter up pg_dump with things that really having nothing to do with backing up and restoring data. It could also allow a large range of options related to getting data out (where, order by), many of which have also been discussed for pg_dump recently. - Bill > > > > -Original Message----- > > > From: Joshua D. Drake [mailto:[EMAIL PROTECTED] > > > Sent: Monday, June 12, 2006 4:15 PM > > > To: Bill Bartlett > > > Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' > > > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > > > > > > > Bill Bartlett wrote: > > > > Here's me speaking up -- I'd definitely use it! As a > > > quick way to pull > > > > data into Excel to do basic reports or analysis, a CSV > > > format would be > > > > great. > > > > > > Why not just use ODBC? > > > > > > Joshua D. Drake > > > -- > > > > > > === The PostgreSQL Company: Command Prompt, Inc. === > > >Sales/Support: +1.503.667.4564 || 24x7/Emergency: > > > +1.800.492.2240 > > >Providing the most comprehensive PostgreSQL solutions > > > since 1997 > > > http://www.commandprompt.com/ > > > > > > > -- > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] CSV mode option for pg_dump
Can't -- the main production database is over at a CoLo site with access only available via SSH, and tightly-restricted SSH at that. Generally one of the developers will SSH over to the server, pull out whatever data is needed into a text file via psql or pg_dump, scp the file(s) back here and send them to the user. We're working on implementing a full-blown reporting system to eliminate some of this need, but it's not quite there yet. However, CSV is a very handy format to use to send data to other people (especially "less technical" users), so even in the future the ability to export to CSV would be handy. - Bill > -Original Message- > From: Joshua D. Drake [mailto:[EMAIL PROTECTED] > Sent: Monday, June 12, 2006 4:15 PM > To: Bill Bartlett > Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > Bill Bartlett wrote: > > Here's me speaking up -- I'd definitely use it! As a > quick way to pull > > data into Excel to do basic reports or analysis, a CSV > format would be > > great. > > Why not just use ODBC? > > Joshua D. Drake > -- > > === The PostgreSQL Company: Command Prompt, Inc. === >Sales/Support: +1.503.667.4564 || 24x7/Emergency: > +1.800.492.2240 >Providing the most comprehensive PostgreSQL solutions > since 1997 > http://www.commandprompt.com/ > > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] CSV mode option for pg_dump
Here's me speaking up -- I'd definitely use it! As a quick way to pull data into Excel to do basic reports or analysis, a CSV format would be great. Some of our users currently pull data into Excel for quickie analysis, but creating fixed-width data via psql requires them to parse the data and dumping anything via pg_dump with any delimiter (tabs, etc.) usually doesn't work due to the delimiters being embedded in the real data. - Bill > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Andrew Dunstan > Sent: Monday, June 12, 2006 2:16 PM > To: Tom Lane > Cc: PG Hackers > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > Tom Lane wrote: > > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > > >> Something someone said on IRC just now triggered a little > memory ... > >> I > >> think we should provide an option to have pg_dump work in CSV mode > >> rather than text mode. This probably doesn't have much > importance in the > >> case of text dumps, but in custom or tar dumps where you > might want to > >> get at individual data members, having an option for CSVs > that you want > >> to load into some other product might be nice. > >> > > > > This is silly. You'd just COPY the particular table you > want, not use > > pg_dump. pg_dump's already got an unreasonably large number of > > options without adding ones that have essentially zero use. > Also, I > > think there are sufficient grounds to worry about whether a > CSV dump > > would always reload correctly --- we already know that > that's a poorly > > thought out "standard". > > > > > > > > Well, if you have dozens or hundreds of tables it might well be more > convenient. > > As for not reloading - I went to some trouble to make sure > that we could > reload what we dumped, exactly, unless the force options are used. I > might have made a bug in that, but it isn't dependent on the > particular > CSV format used. > > Naturally you won't have a use for it, but I suspect others might (in > which case they had better speak up ;-) ) > > I suppose the alternative would be to write a little tool in perl or > whatever to do the same thing for you. Maybe a good pgfoundry project. > > cheers > > andrew > > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MS SQL Server compatibility functions
I'd be _very_ interested. I'll also volunteer to help out on this if you need assistance -- we have somewhat of a mixed environment here, so I already have a few (simple) functions that allow some compatibility between MS SQL Server and PostgreSQL (supporting "nextval" on SQL Server, etc.), but it sounds like your work has gone far beyond my work. - Bill > > Hi. > > In the course of porting a database from Microsoft SQL Server to > PostgreSQL I have rewritten a few of the date and string functions in > pl/pgSQL. Started with just datepart, datediff and soundex, but once > started I continued and rewrote all date/time and string functions > supported by MS SQL 2005. Leaving only compatibility with > unicode-handling and binary objects (that MS SQL Server for > some reason > overloads string functions to work with). > > I guess I am not the only one moving from MS SQL Server, so is there > interest for others to use my work, as a contrib perhaps. And > how should > I continue from here in that case? > > regards > > -- > //Fredrik Olsson > Treyst AB > +46-19-362182 > [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Request for a "force interactive mode" flag (-I) for psql
Back in 2003 Bruce Momjian proposed adding a flag (-I) to psql to force it into "interactive" mode. (See http://archives.postgresql.org/pgsql-hackers/2003-11/msg00013.php for the thread.) The proposal was rejected because there was no proven need for it at that time. I'd like to raise this proposal again, since I think in our situation, this is the only fix for our problem. Our environment is as follows: * A large number of PostgreSQL 7.4 servers running on a variety of SuSE Linux (9.0 - 9.3) servers * A growing number of PostgreSQL 8.0.3 servers running on Windows Server 2003 servers. (We are running the native Win32 version of PostgreSQL, not the Cygwin version.) The servers are all located in remote offices. Maintenance is done remotely via SSH to a "local" bash command prompt where we use the command line tools: psql, pg_dump, etc. On Linux we use the native sshd; on Windows we use Cygwin to get bash, sshd, cron, etc. On Linux this works fine; on Windows, however, psql thinks it is not in an interactive console so we get no prompts, no line editing, no history, very little cursor control, etc. (I see that the missing autocomplete feature is a readline issue, but that's a topic for another posting.) This makes remote maintenance on the Windows servers much more difficult than it otherwise could be. The issue appears to be due to isatty() returning false in the Windows Cygwin environment. From other research around the web, it appears that if the app is "Cygwin-aware" is knows to override this check (or allow manual override) or try to do further testing, but in this case, since we are using the native Win32 version of PostgreSQL, psql doesn't do any additional testing. Also, even in the local console on Windows, running rxvt -- our preferred terminal in Windows (since it lets us make our Windows command line act just like our Linux command line ) -- causes psql to think that there is no terminal. (Our first encounter of this "no terminal" problem was in trying to run psql via a local bash shell via rxvt, and originally we thought that psql was hanging. Given our reliance on psql for remote maintenance, this would have prevented our rolling out a Windows version of PostgreSQL. It was only after too much time looking at it with some low-level tools that we stumbled across the fact that psql was simply silently waiting at a command prompt rather than being hung. However, from other posts in these lists [e.g.: http://archives.postgresql.org/pgsql-patches/2004-07/msg00369.php ] it appears that other people also thought psql was hanging when it was run from a terminal program, so I suppose I shouldn't feel too bad...) Bruce's proposal and suggested code simply added a new "-I" flag to force psql into "interactive mode" (by simply setting "pset.notty = 0"). >From everything I can find (including reading through the Cygwin code, ssh and sshd man pages and code, psql code, testing various modes of running the sshd service in Windows, changing Windows profile account permissions, etc.), adding this flag seems to be the only viable option (other than writing a replacement for psql for our Windows servers, something I'd prefer to not do). While I can obviously add this patch myself and build and maintain a custom version of psql for our own use, since there now is a true native version of PostgreSQL for Windows, I think we will see a growing list of people supporting mixed environments just like ours, and bumping into this exact same problem. Thoughts? - Bill Bill Bartlett meridianEMR, Inc. http://www.meridianemr.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
