[GENERAL] Cumulative (Running) Sum
Hello Group, I might have missed this somewhere, but PostgreSQL doesn't presently support the cumulative/running aggregate function SUM() OVER (PARTITION BY) syntax that Oracle does, right? Here's an example of what I'm talking about: Say we have a table of sales by month person. We want to query this table and show both a month's sales AND the cumulative sum for that person. Something like this: MONTH PERSON VALUE CUMULATIVE_SUM -- -- JanuaryDavid5050 JanuaryMatt 1010 February David4595 February Matt 5 15 March David60155 March Matt 2035 In Oracle this is nicely accomplished by using the following syntax: SELECT c.Month, c.Person, c.Value, sum(c.value) over(partition by c.Person order by c.Month_Num, c.Person) as Cumulative_Sum FROM CS_Test c ORDER BY c.Month_Num ASC, c.Person ASC In PostgreSQL however, we can do this, but we have to use a subquery: SELECT c.Month, c.Person, c.Value, (select sum(c2.value) from CS_Test c2 where c2.Month_Num = c.Month_num and c2.person = c.person) as Cumulative_Sum FROM CS_Test c ORDER BY c.Month_Num ASC, c.Person ASC So is there planned support for the newer syntax or is a subquery the best/only way to go on PostgreSQL for now? Thanks, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cache lookup failed for relation X
I found some posts be developers discussing this issue last Jan. and Nov.. See link below. http://search.postgresql.org/search?m=1q=cache+lookup+failed+for+relation l=d=s= But have not been able to find any suggestion on how an end user can use to get around this problem. Basically, we have a table X, trying to drop it would result an error message below: cache lookup failed for relation X I am using PgAdmin and I can still view the schema of this table, but any operation on it would get the above error. Any suggestion on how to drop a table in this situation. I am also wondering whether this problem has been fixed in newer PostgreSQL releases. BTW, our postgreSQL version is 7.4.3. Thanks. This communication may contain information that is confidential, privileged or subject to copyright. If you are not the intended recipient, please advise by return e-mail and delete the message and any attachments immediately without reading, copying or forwarding to others.
[GENERAL] cache lookup failed for relation X
I found some posts be developers discussing this issue last Jan. and Nov.. See link below. http://search.postgresql.org/search?m=1q=cache+lookup+failed+for+relationl=d=s= But have not been able to find any suggestion on how an end user can use to get around this problem. Basically, we have a table X, trying to drop it would result an error message below: cache lookup failed for relation X I am using PgAdmin and I can still view the schema of this table, but any operation on it would get the above error. Any suggestion on how to drop a table in this situation. I am also wondering whether this problem has been fixed in newer PostgreSQL releases. BTW, our postgreSQL version is 7.4.3. Thanks. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
Lewis, On Sat, Mar 8, 2008 at 3:13 AM, Lewis Cunningham [EMAIL PROTECTED] wrote: My problem is not even so much the feed. I know how to use yahoo pipes and feedburner to create a custom feed. The problem is two fold. First, this is supposed to be a community aggregator and a single person is making arbitrary rules on who and how to use it. Second, is the matter of a policy that is undocumented, subject to a single person's interpretation and where enforcement is not open. Well there's a major part of your misunderstanding. planetpostgresql.org IS NOT a community project. It is a project started and run by a very well liked and respected community member (with the assistance of a few others), that the community - read web team, plus others - support and encourage. If it were a community project it would be under postgresql.org, as all our sites are, (with the exception of pgFoundry) I was just surprised to get an email booting me off the aggregator. I've certainly seen requests to keep introductory paragraphs to a reasonable length in the past - did you not get them as well? -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER Diagram design tools (Linux)
On Fri, Mar 07, 2008 at 01:22:49PM -0800, Colin Fox wrote: I've created a pg_foundry project for this. Assuming the project gets approved, I'll post the link here. Wouldn't it be better just to send that XSLT to the upstream project? Cheers, David. Regards, cf Malinka Rellikwodahs wrote: i'm interested in both the xml extractor and the conversion, could you reply with a link or some such it would be greatly appreciated ;) On Fri, Mar 7, 2008 at 2:44 PM, Colin Fox [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Conor McTernan wrote: I was wondering if anyone knows of any good ER Diagram tools for Postgres that run on Linux. I have been using DBDesigner by FabForce for a couple of years, but development has stopped while MySQL workbench is being built (for windows only). Neither of these applications will talk to Postgres and I've found DBDesigner to be a bit buggy at the best of times (it's still quite good and better than nothing I suppose). I've been using PgAdmin3 which is great for updating/managing tables/view etc, but I would really like something for modelling ER diagrams which will talk directly to Postgres. Does anyone know of any commercial or open source software that will do this? Cheers, Conor ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] so that your message can get through to the mailing list cleanly I've created an XSL stylesheet that works with graphviz to reverse engineer an ERD from a postgres database. If anyone's interested, I can make this available. It works quite well. It uses a postgres-to-xml extractor that someone wrote (I don't know who - their name is not in the file) and then I convert the xml to graphviz. Regards cf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.3
On 3/8/08, Alex Turner [EMAIL PROTECTED] wrote: Well - I know that my stored proc is segfaulting based on a strace of postgresql. Don't know how that affects trac which isn't using that stored proc... the mystery continues. Either way I didn't get a corefile, and ulimit -a show I have unlimited core file size :( * Are you absolutely sure that ulimit applies to the actual running postmaster process? * Are you sure you're looking in the right place for core files? -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.3
No I'm not. Where would a core file be if there was going to be one? I'm not sure how I can tell if the ulimit applies to the running postmaster I am the postgres user and ulimit -a show unlimited for core, and I run pg_ctl start. I have put it in that one place in /etc/ and also in ~/.bash_profile for postgres Alex On Sat, Mar 8, 2008 at 11:01 AM, Douglas McNaught [EMAIL PROTECTED] wrote: On 3/8/08, Alex Turner [EMAIL PROTECTED] wrote: Well - I know that my stored proc is segfaulting based on a strace of postgresql. Don't know how that affects trac which isn't using that stored proc... the mystery continues. Either way I didn't get a corefile, and ulimit -a show I have unlimited core file size :( * Are you absolutely sure that ulimit applies to the actual running postmaster process? * Are you sure you're looking in the right place for core files? -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.3
On 3/8/08, Alex Turner [EMAIL PROTECTED] wrote: No I'm not. Where would a core file be if there was going to be one? They should appear in the data directory (e.g. /var/lib/pgsql/data). I'm not sure how I can tell if the ulimit applies to the running postmaster I am the postgres user and ulimit -a show unlimited for core, and I run pg_ctl start. I have put it in that one place in /etc/ and also in ~/.bash_profile for postgres That should work. It would be nice to be able to see the limits for a process through /proc, but unfortunately that's never been implemented... -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.3
On Sat, Mar 8, 2008 at 1:05 PM, Douglas McNaught [EMAIL PROTECTED] wrote: On 3/8/08, Alex Turner [EMAIL PROTECTED] wrote: No I'm not. Where would a core file be if there was going to be one? They should appear in the data directory (e.g. /var/lib/pgsql/data). Yeah - thats where I was looking, so I'm guessing some where I don't have ulimit set up right for the process :(. My strace showed the segfault right after loading distance.so which is my shared object that contains my stored procs for that database, so I'm pretty sure it was in there. I found what was going on in that bit and fixed it so it's not crashing anymore, I'm just worried about how on earth that could have affected other back end processes that were querying unrelated databases. I'm not sure how I can tell if the ulimit applies to the running postmaster I am the postgres user and ulimit -a show unlimited for core, and I run pg_ctl start. I have put it in that one place in /etc/ and also in ~/.bash_profile for postgres That should work. It would be nice to be able to see the limits for a process through /proc, but unfortunately that's never been implemented... That would be nice. I wish there were more than 24 hours in a day so I could scratch some of my proverbial itches like that. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER Diagram design tools (Linux)
David Fetter wrote: On Fri, Mar 07, 2008 at 01:22:49PM -0800, Colin Fox wrote: I've created a pg_foundry project for this. Assuming the project gets approved, I'll post the link here. Wouldn't it be better just to send that XSLT to the upstream project? Cheers, David. I don't know which project you mean. The postgresql project? I have a couple of files that go along with it - instructions, some documentation, examples, etc. I'm certainly more than willing to provide this to the PG team, if they're interested. It just seems that the pg_foundry is a logical place to put all the project information. Regards, cf Regards, cf Malinka Rellikwodahs wrote: i'm interested in both the xml extractor and the conversion, could you reply with a link or some such it would be greatly appreciated ;) On Fri, Mar 7, 2008 at 2:44 PM, Colin Fox [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Conor McTernan wrote: I was wondering if anyone knows of any good ER Diagram tools for Postgres that run on Linux. I have been using DBDesigner by FabForce for a couple of years, but development has stopped while MySQL workbench is being built (for windows only). Neither of these applications will talk to Postgres and I've found DBDesigner to be a bit buggy at the best of times (it's still quite good and better than nothing I suppose). I've been using PgAdmin3 which is great for updating/managing tables/view etc, but I would really like something for modelling ER diagrams which will talk directly to Postgres. Does anyone know of any commercial or open source software that will do this? Cheers, Conor ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] so that your message can get through to the mailing list cleanly I've created an XSL stylesheet that works with graphviz to reverse engineer an ERD from a postgres database. If anyone's interested, I can make this available. It works quite well. It uses a postgres-to-xml extractor that someone wrote (I don't know who - their name is not in the file) and then I convert the xml to graphviz. Regards cf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cumulative (Running) Sum
Matt Culbreth [EMAIL PROTECTED] writes: I might have missed this somewhere, but PostgreSQL doesn't presently support the cumulative/running aggregate function SUM() OVER (PARTITION BY) syntax that Oracle does, right? Right. There are people interested in this, and it'll likely show up in 8.4 or later, but it doesn't exist now. (What's actually on the road map is the SQL:2003 windowing functions. I'm not sure the spec is exactly compatible with Oracle, but it definitely has this type of functionality.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cache lookup failed for relation X
Wei Wu [EMAIL PROTECTED] writes: Basically, we have a table X, trying to drop it would result an error message below: cache lookup failed for relation X You've got missing catalog rows, apparently. I am using PgAdmin and I can still view the schema of this table, but any operation on it would get the above error. Hmmm ... how long since the system catalogs in this database were vacuumed? I am also wondering whether this problem has been fixed in newer PostgreSQL releases. BTW, our postgreSQL version is 7.4.3. It's hard to say, but you are three and a half years behind on bug fixes, some of which certainly could have led to corruption of this sort. You really should be running 7.4.19. Read the release notes here: http://www.postgresql.org/docs/7.4/static/release.html regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.3
Alex Turner [EMAIL PROTECTED] writes: ... I found what was going on in that bit and fixed it so it's not crashing anymore, I'm just worried about how on earth that could have affected other back end processes that were querying unrelated databases. You do know that a crash in any backend prompts the postmaster to restart the whole database cluster? I've forgotten the start of this thread, but what you say here sounds like expected behavior. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
Dave Page wrote: Well there's a major part of your misunderstanding. planetpostgresql.org IS NOT a community project. It is a project started and run by a very well liked and respected community member (with the assistance of a few others), that the community - read web team, plus others - support and encourage. If it were a community project it would be under postgresql.org, as all our sites are, (with the exception of pgFoundry) And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
Guy Rouillier [EMAIL PROTECTED] writes: Dave Page wrote: Well there's a major part of your misunderstanding. planetpostgresql.org IS NOT a community project. It is a project started and run by a very well liked and respected community member (with the assistance of a few others), that the community - read web team, plus others - support and encourage. If it were a community project it would be under postgresql.org, as all our sites are, (with the exception of pgFoundry) And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. Which in fact has got only the weakest claim to be a community project. If it actually were such, in the sense of having been started with community-wide discussion and approval, it would have been set up under postgresql.org. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cumulative (Running) Sum
On Fri, Mar 07, 2008 at 06:50:17AM -0800, Matt Culbreth wrote: I might have missed this somewhere, but PostgreSQL doesn't presently support the cumulative/running aggregate function SUM() OVER (PARTITION BY) syntax that Oracle does, right? you might find this useful: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ please also read comments. depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
Err, no. That is also not a postgresql.org website. Let me clarify, so JD doesn't slap my wrists again. Official PostgreSQL project sites/services are deployed under the postgresql.org domain, having been agreed upon by -www or the sysadmin team. We have a policy of not using alternate domain names, or even site names as far as is practical. The only exceptions are pgFoundry, and some backend infrastructure. Other sites, such as planetpostgresql an postgresqldocs, are not run as part of the PostgreSQ On 3/8/08, Guy Rouillier [EMAIL PROTECTED] wrote: Dave Page wrote: Well there's a major part of your misunderstanding. planetpostgresql.org IS NOT a community project. It is a project started and run by a very well liked and respected community member (with the assistance of a few others), that the community - read web team, plus others - support and encourage. If it were a community project it would be under postgresql.org, as all our sites are, (with the exception of pgFoundry) And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent from my mobile device Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, 08 Mar 2008 15:39:32 -0500 Tom Lane [EMAIL PROTECTED] wrote: Guy Rouillier [EMAIL PROTECTED] writes: Dave Page wrote: Well there's a major part of your misunderstanding. planetpostgresql.org IS NOT a community project. It is a project started and run by a very well liked and respected community member (with the assistance of a few others), that the community - read web team, plus others - support and encourage. If it were a community project it would be under postgresql.org, as all our sites are, (with the exception of pgFoundry) And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. Which in fact has got only the weakest claim to be a community project. If it actually were such, in the sense of having been started with community-wide discussion and approval, it would have been set up under postgresql.org. I suggest you check the archives the the numerous threads on the topic of having community editable documentation that have essentially been ignored by the Web team (of which I am a part). regards, tom lane The community is much bigger than the small atom of .Org. .Org is obviously the hub and the central switch no question but your assessment of it not being a community project is a testament to arrogance that I haven't seen in some days on these lists. Are you to say that the easy 99% of people that use PostgreSQL that don't participate within the .Org aren't part of our community? Or perhaps that the ITPUG folks, who for the most part do not participate on these lists aren't part of our community? Or perhaps you are saying that the very hard work by Elein via the old Bits days are not part of the community just because it doesn't have PostgreSQL.org address. Tom with the utmost of respect, you are in this instance the most centered of the definition wrong I have seen. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH0wFzATb/zqfZUUQRAipKAJ9YMbSe+n1Po192p4LsoicLxtENnQCgissO tsUnc3U5JxELsF0DtV8F1PQ= =65IL -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, 8 Mar 2008 20:57:28 + Dave Page [EMAIL PROTECTED] wrote: Err, no. That is also not a postgresql.org website. Let me clarify, so JD doesn't slap my wrists again. Official PostgreSQL project sites/services are deployed under the postgresql.org domain, having been agreed upon by -www or the sysadmin team. We have a policy of not using alternate domain names, or even site names as far as is practical. The only exceptions are pgFoundry, and some backend infrastructure. Right. Other sites, such as planetpostgresql an postgresqldocs, are not run as part of the PostgreSQ This got cut off but I assume you meant, as part of the PostgreSQL.Org infrastructure (or PostgreSQL.Org community). That would be correct. PlanetPostgresql, Postgresqldocs, Postgresqlconference, ITPUG, PostgreSQLFR, PostgreSQL.eu are PostgreSQL community projects but they are not PostgreSQL.Org projects. Which in my mind is cool (I know not everyone agrees) because it is a testament to the strength of our community as a whole. To look at PostgreSQL as just PostgreSQL.org doesn't do the community or the project itself justice. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH0wJvATb/zqfZUUQRAlrUAJoCUaCODEBcpybMxaolvSZBvTLzCwCfS8Wl Q57H0YsZ5cawnaHL2DpDIwI= =LjJr -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.3
Ah... no I didn't know that - that would explain all the other behaviour then!! Good to know. Alex On Sat, Mar 8, 2008 at 3:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Alex Turner [EMAIL PROTECTED] writes: ... I found what was going on in that bit and fixed it so it's not crashing anymore, I'm just worried about how on earth that could have affected other back end processes that were querying unrelated databases. You do know that a crash in any backend prompts the postmaster to restart the whole database cluster? I've forgotten the start of this thread, but what you say here sounds like expected behavior. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Community websites (Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs)
Hi, Either please change the subject, or let's move this discussion to -www, with a different subject. Thanks. -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[GENERAL] loading a psql input file on win xp
HELLO i have psql file which is to be used for loading a database it was downloaded from this link http://conceptnet.media.mit.edu/ the description says The ConceptNet 3 database, as a PostgreSQL input file. You will need to be running a PostgreSQL server to install ConceptNet 3. i was earlier suggested to use following command to add it to database psql -d your_database_name -f conceptnet-2007-09-25.psql however i do not know where to execute this command i am using a windows xp with with postgresql installed in D:\Program Files\PostgreSQL\8.3\ the file is located in folder D:\conc\ now can someone please tell me where to execute above command should i use dos (i mean command prompt ) please help sorry for my foolish question -- akshay uday bhat. t.y.c.e. department of chemical engineering university institute of chemical technology mumbai India On action alone be thy interest, Never on its fruits. Let not the fruits of action be thy motive, Nor be thy attachment to inaction. Ask and it shall be given to you; seek and you shall find; knock and it shall be opened to you
[GENERAL] Service account not showing
I installed PostgreSQL on my Windows XP Home PC (at home) but when I look under the Control Panel, User accounts, the special postgres user name is not listed. However, using net user postgres shows the following:- User namepostgres Full Namepostgres Comment PostgreSQL service account User's comment Country code 000 (System Default) Account active Yes Account expires Never Password last set3/7/2008 9:53 AM Password expires Never Password changeable 3/7/2008 9:53 AM Password requiredYes User may change password No Workstations allowed All Logon script User profile Home directory Last logon 3/8/2008 9:27 AM Logon hours allowed All Local Group Memberships Global Group memberships *None How can I make Windows show this user so that I can access it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] loading a psql input file on win xp
On 08/03/2008 19:46, akshay bhat wrote: now can someone please tell me where to execute above command should i use dos (i mean command prompt ) Yes, that's it - run it at the command prompt. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] loading a psql input file on win xp
akshay bhat wrote: The ConceptNet 3 database, as a PostgreSQL input file. You will need to be running a PostgreSQL server to install ConceptNet 3. i was earlier suggested to use following command to add it to database psql -d your_database_name -f conceptnet-2007-09-25.psql however i do not know where to execute this command Run the command anywhere you want. Just make sure the bit after -f points to the proper location of the .psql file. Also, be sure you have created your own database before running that command. Information on creating databases may be found in the PostgreSQL documentation. Colin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
Tom Lane wrote: Guy Rouillier [EMAIL PROTECTED] writes: And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. Which in fact has got only the weakest claim to be a community project. If it actually were such, in the sense of having been started with community-wide discussion and approval, it would have been set up under postgresql.org. Well, color me confused. I don't pretend to understand what all this is about, and as just a user of PG, I don't necessarily have the need to know. I'm just trying to give back to the community in whatever small way I can, and I thought community documentation would offer me the opportunity to do that. The only thing I think I have a right to ask is that whatever contributions I may make not be a waste of effort because the PG decision-makers have decided that a certain repository is now official, and the previous one is defunct. So I'd ask those decision-makers to come up with a single consistent story for us run-of-the-mill community members. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
What is the community, WAS: Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, 08 Mar 2008 19:27:43 -0500 Guy Rouillier [EMAIL PROTECTED] wrote: The only thing I think I have a right to ask is that whatever contributions I may make not be a waste of effort because the PG decision-makers have decided that a certain repository is now official, and the previous one is defunct. So I'd ask those decision-makers to come up with a single consistent story for us run-of-the-mill community members. I don't think anyone can argue with this: http://www.postgresqldocs.org is not a PostgreSQL.org project. It is a PostgreSQL Community project, by the very nature that it has PostgreSQL community members contributing to it. The confusion comes in when sweeping statements of tasks and projects not being part of the community. Where community is not correctly defined. However when we use such a limited, false and frankly naive definition of community, PostgreSQL.org becomes nothing because it loses everything that makes it powerful. Consider that only three drivers are developed under PostgreSQL.org, jdbc, psqlodbc and C. All of the others are developed independently of PostgreSQL.Org and yet are respectable and deserving members of the community. Further consider that applications that make PostgreSQL.org powerful Drupal, WordPress, Postgis, Postbooks, and LedgerSMB etc... are also not developed under PostgreSQL.org but are very much a part of the community. Lastly let's not forget our regional communities such as PostgreSQL.eu, itpug.org, PostgreSQLFR.org, PostgreSQL.BR etc... We can in anyway suggest they are not members of the community can we? As a run-of-the-mill community member, I argue that your perceptions, are more important than the self inflated ideals of any contributor (myself included), because there are 1000 run-of-the-mill community members for every known contributor. It is those 1000 run-of-the-mill community members that are actually driving the growth of our Community which encompasses but is not anywhere near limited to PostgreSQL.org and yet has everything to do with, PostgreSQL Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH0zIpATb/zqfZUUQRAh9iAJ93qioqemOZJAsempOEQyX/nSCa2ACgim77 BjLOXFFq64gt0NJCvna0tHA= =+Jac -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
On Sat, 8 Mar 2008, Guy Rouillier wrote: The only thing I think I have a right to ask is that whatever contributions I may make not be a waste of effort because the PG decision-makers have decided that a certain repository is now official, and the previous one is defunct. Contributions to postgresqldocs.org are licensed such the author still retains copyright on that work. If you write something there and later decide some other site would be a better home for the documentation you wrote, you can copy whatever you did over. You should never work on documentation you want to contribute to the world with someone if you don't end up with the ability to use it elsewhere afterwards. So I'd ask those decision-makers to come up with a single consistent story for us run-of-the-mill community members. Right now the official home for community documentation is http://www.postgresql.org/docs/techdocs I personally find editing and posting material there too difficult, which is why I'm writing on the postgresqldocs.org wiki instead. The PostgreSQL WWW team is investigating a more flexible approach as well. You can find a recent statement of their plans in this area at http://archives.postgresql.org/pgsql-www/2008-02/msg00217.php If you're concerned about contributing to a site not officially under the banner of the PostgreSQL Global Development Group, by all means wait to see what they come up. There can't be a single consistent story from them and from run-of-the-mill me until they've built something that isn't available yet. Since I like to write but am not into that sort of infrastructure building task, I just keep chugging away at what I'm good at while I wait to see how that turns out. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] data import - duplicates
I have ~350K rows of sample data that has come to me in 64 text files (CSV) that I'd like to import into a new database. Each file can be considered its own category and is so named. That is, each file name will be inserted into a separate categories table. I'd like to relate each row to its category. However, while the rows are unique in each file, some rows are found in more than one file. I also must parse some of the fields into separate columns, but that's no big deal. But it means that I must do some pre-processing on these files, in any case. After some thought, I thought I might brute-force the problem with Perl by reading each line of each file into an assoc. array unless it's already in it, in which case I'd append the key based on the list of categories that line is found in (I'd still need to parse outthe keys later but I'd get rid of the dupes). Each array row would be like so: 'key' = '1,4,etc.', 'text' = 'a line' Aside from the fact that the array search would become ridiculously large after just a couple of files, I realised that this is a non-starter for the simple fact that this data comprises less than 25% of the eventual total. So refactoring it in this way would be a waste of time (there will probably be dupes in the files to come). So, I'd like to instead parse out my columns properly and write each line (along with its category key) to a new, single file to be copied into a working table. ONce I've done so, is there an efficient method I can use to select all duplicates (save for the category key) into a set from which I could then select into the final table (and insert the keys into the category join table)? For example (pk is the PK from the working table and ck is the category key), my dupes query on the working table would give the following set: pk ck 1 1 a a a a 2 3 a a a a 3 3 b b b b 4 7 b b b b 5 4 a a a a I would then want to insert just the unique rows into the final table yet add all of the the PKs and CKs to the category join table. After that was done, I'd delete all of these from the working table and then move the unique rows that are left to the final table (and insert the keys into the join table). I hope that makes sense. I'm not looking for anyone to do my homework for me; I'm sure I could fix up a tasty function for this (the data is destined for MySQL, alas, but I'll be damned if I won't use PG for the heavy lifting). What I'm really looking for is a handy way to grab all of those dupes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] data import - duplicates
I haven't tested but this is what I would do (uses arrays, which are
handy when you need them), with the names changed to protect the
innocent:
begin;
-- create a table with some duplicates in one of the columns (y is ck);
wsprague=# select x, x%4 as y into temp fbar from generate_series(1,10) as x(a);
SELECT
wsprague=# select * from fbar;
x | y
+---
1 | 1
2 | 2
3 | 3
4 | 0
5 | 1
6 | 2
7 | 3
8 | 0
9 | 1
10 | 2
(10 rows)
-- create a table with the pk, an array of the duplicate keys, and the
length of that array
select y, x_list, array_upper(x_list, 1) as x_len into baz from
(select y, array_accum(x) as x_list from fbar group by y ) a ;
wsprague=# select * from baz;
y | x_list | x_len
---+--+---
3 | {3,7}| 2
2 | {2,6,10} | 3
1 | {1,5,9} | 3
0 | {4,8}| 2
(4 rows)
-- delete all rows that don't have ck in the first element of the pk list
wsprague=# delete from fbar where not exists (select 1 from baz where
fbar.x=baz.x_list[1]);DELETE 6
wsprague=# select * from fbar;
x | y
---+---
1 | 1
2 | 2
3 | 3
4 | 0
(4 rows)
commit;
I hope that makes sense. It should be easy to make into a function
(like clean_table(table_name text, pk_name text, dup_key_name text).
I don't know how well it will work with indexes. You could probably
even write a function to do the entire import inside postgres,
starting with a copy and moving to merging tables and ending with some
consistency checks, and thus benefit from transaction isolation of
the whole process
HTH
On Sat, Mar 8, 2008 at 9:42 PM, brian [EMAIL PROTECTED] wrote:
I have ~350K rows of sample data that has come to me in 64 text files
(CSV) that I'd like to import into a new database. Each file can be
considered its own category and is so named. That is, each file name
will be inserted into a separate categories table. I'd like to relate
each row to its category. However, while the rows are unique in each
file, some rows are found in more than one file.
I also must parse some of the fields into separate columns, but that's
no big deal. But it means that I must do some pre-processing on these
files, in any case.
After some thought, I thought I might brute-force the problem with Perl
by reading each line of each file into an assoc. array unless it's
already in it, in which case I'd append the key based on the list of
categories that line is found in (I'd still need to parse outthe keys
later but I'd get rid of the dupes). Each array row would be like so:
'key' = '1,4,etc.', 'text' = 'a line'
Aside from the fact that the array search would become ridiculously
large after just a couple of files, I realised that this is a
non-starter for the simple fact that this data comprises less than 25%
of the eventual total. So refactoring it in this way would be a waste of
time (there will probably be dupes in the files to come).
So, I'd like to instead parse out my columns properly and write each
line (along with its category key) to a new, single file to be copied
into a working table. ONce I've done so, is there an efficient method I
can use to select all duplicates (save for the category key) into a set
from which I could then select into the final table (and insert the keys
into the category join table)?
For example (pk is the PK from the working table and ck is the category
key), my dupes query on the working table would give the following set:
pk ck
1 1 a a a a
2 3 a a a a
3 3 b b b b
4 7 b b b b
5 4 a a a a
I would then want to insert just the unique rows into the final table
yet add all of the the PKs and CKs to the category join table. After
that was done, I'd delete all of these from the working table and then
move the unique rows that are left to the final table (and insert the
keys into the join table).
I hope that makes sense. I'm not looking for anyone to do my homework
for me; I'm sure I could fix up a tasty function for this (the data is
destined for MySQL, alas, but I'll be damned if I won't use PG for the
heavy lifting). What I'm really looking for is a handy way to grab all
of those dupes.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
