Re: [GENERAL] Create loop in postgresql
Try it with parentheses around the (SELECT). On 03.09.2012 18:06, Robert Buckley wrote: > this give an error. > > select name,ges_kw_zgb, SELECT round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2); > > ERROR: syntax error at or near "SELECT" > LINE 2: select name,ges_kw_zgb, SELECT round(100 * (ges_kw_zgb / (se... > > > *Von:* salah jubeh > *An:* Robert Buckley ; > "pgsql-general@postgresql.org" > *Gesendet:* 17:45 Montag, 3.September 2012 > *Betreff:* Re: [GENERAL] Create loop in postgresql > > I am wondering why do not you write it like this > > select name,ges_kw_zgb, select round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) .. > > Regards > > > > *From:* Robert Buckley > *To:* "pgsql-general@postgresql.org" > *Sent:* Monday, September 3, 2012 5:30 PM > *Subject:* [GENERAL] Create loop in postgresql > > Hi, > > I am trying to loop through the records in a table and update a > column. I can do this with a case statement but I would like to > simplify this to a simple loop statement. I can´t seem to work out how > to do it though. > > Here is the case statement. > > select name,ges_kw_zgb, > case > When name='Bad Harzburg' then (select round(100 * (ges_kw_zgb / > (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > When name='Braunlage' then (select round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > When name='Braunschweig' then (select round(100 * (ges_kw_zgb / > (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > When name='Büddenstedt' then (select round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > > End as z > > from energie.tennet_auswertung_2010 > ; > > > Instead of having to write the name variable in the function, I would > just like to iterate through each record and execute the select > round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From > energie.tennet_auswertung_2010)),2) command. > > If anyone can help I´d me grateful, > > cheers, > > Rob > > > >
Re: [GENERAL] Create loop in postgresql
And probably the op would prefer to avoid recalculating the sum once for every row. select name,ges_kw_zgb, round(100*ges_kw_zgb/total.totalsum, 2) as z from energie.tennet_auswertung_2010, (select sum(ges_kw_zgb) totalsum >From energie.tennet_auswertung_2010) as total where .. Regards Rainer On 03.09.2012 17:45, salah jubeh wrote: > I am wondering why do not you write it like this > > select name,ges_kw_zgb, select round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) .. > > Regards > > > > *From:* Robert Buckley > *To:* "pgsql-general@postgresql.org" > *Sent:* Monday, September 3, 2012 5:30 PM > *Subject:* [GENERAL] Create loop in postgresql > > Hi, > > I am trying to loop through the records in a table and update a > column. I can do this with a case statement but I would like to > simplify this to a simple loop statement. I can´t seem to work out how > to do it though. > > Here is the case statement. > > select name,ges_kw_zgb, > case > When name='Bad Harzburg' then (select round(100 * (ges_kw_zgb / > (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > When name='Braunlage' then (select round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > When name='Braunschweig' then (select round(100 * (ges_kw_zgb / > (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > When name='Büddenstedt' then (select round(100 * (ges_kw_zgb / (select > sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) > > End as z > > from energie.tennet_auswertung_2010 > ; > > > Instead of having to write the name variable in the function, I would > just like to iterate through each record and execute the select > round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From > energie.tennet_auswertung_2010)),2) command. > > If anyone can help I´d me grateful, > > cheers, > > Rob > >
Re: [GENERAL] Postgresql Error ask for password
On 31.08.2012 16:29, José Pedro Santos wrote: > Dear all, > > Im trying to install one application in my Linux but when I try to use > the expression SU postgresql it ask for password, what is the > password? I try my root password but dont work. Sorry, you are a bit terse on the context of this. Basically, there might not be a "true" password for user postgresql (if your installation is even using that user id, most use "postgres" or "pgsql") If you are in the process of installing some application and that requests you to enter "su postgresql", than likely this procedure is expecting you being root. Then su will succeed (if user is known at all - see above). Nevertheless, most distributions of Linux would prefer you to do a variant of "sudo" and avoid "su" completely. So, if you are running all this as a different user (and not happen to be "postgresql" user already), become root and proceed with "su" as told. However, may be you step back and try to make sense out of what you are supposed to do identify (and overcome) those inaccuracies, that seem to exists with your instructions. Or are you just poking around based on experience that do not perfectly fit with the current environment? The symptoms you depict match a variety of causes (and problems), thus it really is difficult to give proper advice Rainer > > Thanks for your time > > Ciao > Jose Santos
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Hm, sorry but I still can not get into that argument. Take your example 3 (COMINT in place of COMMIT) How should the DB know that (and how) to safely recover from such error? You need to tell - and there are tools to do so right available. In an interactive session: - use "autocommit=on" to indicate that any statement surely will not invalidate any previous one Then the "problem" is non-existent - if you need transactional grouping of statements: you may envelope each statement with transactional sub structure (e.g. SAVEPOINTRELEASE) to indicate to the DB that only the inner most level of transaction is at stake and the "environment" outside that statement may cope with errors. Agreed, this is "unexpected" if coming from a DB that treats syntax errors differently. (May be sometimes there will be a mode with interactive tools that provide such enveloping implicitly (if requested by user)) In a non-interactive session it is more obvious. What should happen when after the failed "COMMIT" above the session is to be terminated? The pending transaction is to be terminated anyway. Moreover, of a syntax error happens with a statement (e.g. some update) and a later statement is assuming it had succeeded and will ruin your data if not, would you still appreciate the DB to simply ignore the error (logging a message of course) and later on happily commit inconsistent data? I'm sure, there will be loud outcry if such would be possible by mere syntax error handling. If your application is prepared to handle syntax errors during run, then use available tools, if not (and most application likely will not provide such logic), accept the need for testing your applications. Any reaction for a transactional system has to guarantee consistency even for the price of convenience. Thus, convenience may cost some extra effort. At the end, I read the complaint as a suggestion to maintainers of interactive tools to build such interactive convenience into their tools. But do not detect evidence for this to be a "feature" of the DB in the first place. Rainer On 19.06.2012 15:35, Rafal Pietrak wrote: > On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: >> On 06/19/2012 02:20 PM, Tom Lane wrote: >>> So you're suggesting that "SELECT 1/0;" should terminate a transaction, >>> but "SELECT 1//0;" should not? How about "ROLBACK;"? It gets pretty >>> squishy pretty fast when you try to decide which sorts of errors are >>> more important than others. >>> >> When put that way, it seems blindingly obvious. You have a talent for >> making a devastating point very succinctly. > I'd humbly disagree. > > Not to drag this discussiong any further, just to make a point that the > other approach is also "blindingly obvious". Only the other way around. > > The point is, that SQL syntax errors are so obviusly different from > execution errors, that noting this distinction should not raise any > ambiguity. In Tom's example "ROLBACK": > 1. should not break the transaction > 2. should only raise NOTICE: "syntax error" > 2.1. in case this was issued from command line - user can always > ROL to see what's next. > 2.2. in case of a compiled program sending a "ROLBACK" to the > backend hack, the programmer should know better. > 3. and BTW: what about rolling back a tediously cooked sequence of > statements finished by "COMINT"? > > Things are not so obvious. And frankly, if not for the "" I'd have > case (3) so often, that it would have driven me crasy. > > > -R > >> -- >> Craig Ringer >> >> POST Newspapers >> 276 Onslow Rd, Shenton Park >> Ph: 08 9381 3088 Fax: 08 9388 2258 >> ABN: 50 008 917 717 >> http://www.postnewspapers.com.au/ >> > > -- 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] can these queries be combined into one?
Solution heavily depends on the relation of products and othertable. If ordercode at most has a single match with ordercode from othertable, then a simple left join and a coalesce() or CASE in the select list will do ( SELECT p.ordercode, p.descr, CASE WHEN t.codes is NULL THEN p.ordercode ELSE t.ordercode | 512 from products p left join othertable t on p.ordercode = t.ordercode WHERE ;) IF othertable has several matching rows you might need to applay an aggregator adding in all the bits for codes However, your update statement seems to imply something along the first case.. Regards Rainer On 25.03.2012 09:16, haman...@t-online.de wrote: > > Hi, > > I am currently doing something like > > select ordercode, descr, codes into temp table x from products where ... > Here codes is a bit-mapped field > update x set codes = codes | 512 from othertable t where ordercode = > t.ordercode and > select * from x > > Is there a way to avoid that temp table? > > Regards > Wolfgang Hamann > > -- 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] Problem with initdb and two versions on one server?
Hello Chris, can not provide much help. But definitely the message sounds like case (1) (8.3 server accessing 7.4 files) I did encounter such with two occasions: a) used an old initdb version b) using an rc script that had hard coded path to the (old) data directory both cases seem not to apply in your case. BTW: you may check PG:VERSION file(s) in data directories to verify what version those directory are claiming to be. Rainer On 28.02.2012 14:46, Chris McCormick wrote: > Hello all, > Because of issues with dump/restore, I am instead setting up a > second cluster under a newer version so I can slowly migrate data (I > have 7.4.30, and am adding 8.3.18 on the same box). The problem is > that when I try to start the new postmaster it complains: > > "FATAL: database files are incompatible with server" > "DETAIL: The data directory was initialised by PostgreSQL version 7.4, > which is not compatible with this version 8.3.18." > > I compiled 8.3.18 from source, with the following flags in the configure: > 1. --prefix=/backup/pgsql8 > 2. I did NOT use --disable-rpath (i.e., I wanted shared library paths > baked into the executable so it would look first into > /backup/pgsql8/lib first, assuming that the library differences would > be too great between 7.4 and 8.3) > 3. --with-pgport=3005 (the existing one is on the standard port) > > I configured, compiled, and installed, and saw the various subfolders > appear under /backup/pgsql8 as expected. > > I created the data directory and chown'd it to my superuser. > > I ran initdb like so: > /backup/pgsql8/bin/initdb –E UTF8 --locale=en_US -D /backup/pgsql8/data > > I assumed by using the full path to the new initdb that the data > directory would be initialised in 8.3.18 style. It ran without errors. > > When I run pg_ctl, I use the full path: > /backup/pgsql8/bin/pg_ctl -D /backup/pgsql8/data -o “-p 3005” start > > But I still get the error. > > I am confused. I feel that I have pointed to 8.3 everywhere, yet > somehow the existing 7.4 is involved. I am not sure if the message is > saying: > 1. "Your postmaster is 8.3, but you're pointed at the old data > directory" (despite my -D flag) > or > 2. "Your data directory is 8.3, but your postmaster is the old 7.4" > (despite my fully-qualified call to pg_ctl) > > Thoughts? > > Thanks for helping a newbie, > Chris > > PS: Yeah, I know /backup ain't the place to put my database. But the > partitions weren't set up right at the beginning, so this is my hack > for now since I ran out of space. > -- 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] How to split up phone numbers?
Dear Andreas, this will basically require some external knowledge about the numbers logic. The Format is not so a problem in the first place. Numbers that start with national prefix will either start with "00" or "+" followed by the 2 or 3 digit international prefix. City part is of variable length (2 to 5 digits for Germany) (will require a list of city prefix numbers for correct identification). "main" part also is of variable length, thus separation of main and individual part only is possible with explicit knowledge about a specific number. (e.g.: 12345678 could be 123456-78 or 12345-678 or 1234-5678, other splittings are unlike but not completely impossible, only restriction is the maximum length of internationally reachable number length - 12 digits including internat. prefix if I remember correctly, but could also be 14, sorry) So, while it usually is easy to produce a valid dial string from any such number given, splitting such number into its logical parts requires additional information to deliver reasonable results. Rainer On 20.02.2012 15:49, Andreas wrote: > Hi, > is there a way to split up phone numbers? > I know that's a tricky topic and it depends on the national phone > number format. > I'm especially interested in a solution for Germany, Swizerland and > Austria. > > I've got everything in a phone number column that makes hardly sense > like: > +49432156780 > 0049 4321 5678 0 > 04321/5678-0 > and so on... > Those 3 samples are actually the same number in different notations. > > Aim would be to get a normalized number split up in 4 seperate columns > nr_nation > nr_city > nr_main > nr_individual > > so I end up with > 49 4321 5678 0 for central > 49 4321 5678 42 for Mr. Smith > > Is this doable? > > It would be a start to at least split off nr_nation and nr_city. > -- 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] UPDATE...RETURNING col INTO var - what is returned?
Thank you for your reply. Yes, testing proved "after" values being returned. Nevertheless I'm wondering whether this behaviour is (formally) defined somewhere or just happens to be so by implementation detail. (Of course, this is a minor concern as the PG team for sure will guard against a change of the existing semantics) With that behaviour a second question rising is: Is there an easy way to update columns of a table and depend on the previous values in an efficient way? (Clearly, one could use SELECT RETURNING INTO before the update, but that is for sure not my notion of "efficient") This caused my initial doubts: In most cases if you update a column value, you will "know" the new value (as you control the assignment). (Exceptions might be using timestamp values (one that change in real time not such that stay with transaction start) or using values written by other transactions (if transaction separation would allow; might not be save anyway)) So returning "old" values would have provided access to additional information, right? Rainer Am 27.05.2010 19:12, schrieb Alban Hertroys: > On 27 May 2010, at 18:12, Rainer Pruy wrote: > >> Hi all, >> I just got quite confused on the exact semantics >> of "RETURNING expressions INTO target" with an update statement. >> >> And while trying to resolve failed to find an answer in the docs. >> >> Question was whether - in case "expression" is involving a column that is >> assigned by the update itself - >> the value returned is based on the "old" value or the "new" value". >> >> While I'm no back to assuming is has to be the "old" one, >> I did not find a reference to that fact with the docs. > > I'm pretty sure it's the new value, since that's what INSERT...RETURNING > returns - very convenient if the value you inserted was generated somehow (by > a sequence for example). > Since you can also UPDATE some column using a generated value, it'd make > sense if it would behave the same way. > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:737,4bfea81210411699814628! > > > -- Rainer Pruy Geschäftsführer Acrys Consult GmbH & Co. KG Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel Tel: +49-6101-98760-0 Fax: +49-6101-98760-50 Web: http://www.acrys.com - Email: off...@acrys.com Handelsregister: Frankfurt am Main, HRA 31151 Komplementärin: Acrys Verwaltungs GmbH Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel Handelsregister: Frankfurt am Main, HRB 57625 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UPDATE...RETURNING col INTO var - what is returned?
Hi all, I just got quite confused on the exact semantics of "RETURNING expressions INTO target" with an update statement. And while trying to resolve failed to find an answer in the docs. Question was whether - in case "expression" is involving a column that is assigned by the update itself - the value returned is based on the "old" value or the "new" value". While I'm no back to assuming is has to be the "old" one, I did not find a reference to that fact with the docs. Where should I have looked? Or is it actually missing? Then, could one add this, e.g. to the description of RETURNING .. INTO (Executing a query with a single row result) just for clarification, to dispel doubts. Thanks Rainer -- 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] Advantage of more than 1 tablespace on 1 disk?
Hi Thom, table spaces are not in the first place related with addressing usage pattern of individual tables. They are a mechanism for putting up a *logical* layout of persistent storage. As such they are describing segments of persistence storage that will (or "might from the point of view of the schema designer") benefit from being mapped to physical disks independently. (e.g. separating table data and index data, or arrange for indexes used with hot queries to be accessible via different IO channels) For a given machine and disk configuration you may then decide what table space to be mapped to what physical disk. (Hopefully being aware that this will decrease throughput) For addressing your actual question you might have a look at indexing, clustering and/or clustering. Those deal with efficiency of accessing individual tables. Rainer Thom Brown schrieb: > Hi, > > I've got a database with massive tables which fall into 2 categories: > Tables which don't change often but get read a LOT, and tables which > are heavily added to continuously and sometimes read. > > Would there be any advantage in moving the latter logging tables to a > separate tablespace, bearing in mind it would still be on the same > disk? Or have I kinda missed how tablespaces work? > > Thanks > > Thom > -- 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] Annoying Reply-To
Sigh... wasting another junk of bandwidth with this bike shed discussion Granted, replying here is more annoying and less convenient compared to other lists - as long as your MUA still does not provide decent support for mailing lists. Down back from 1998 is RFC 2369 that defined additional headers for mailing lists to indicate important information (e.g. how to post messages). Thus, since then there is no excuse in hijacking Reply-To headers for use of simplifying mailing list replies. Even if for the last ten years most MUAs did not care adding support, there is no real reason for blaming mailing list maintainers that follow current standards for inconveniences caused by dumb MUAs. Especially if there is a reasonable workaround (using Replay All - not ideal as any workaround but manageable). Thus, *please* complain with the maintainer of your MUA to get the annoyance alleviated - or change your MUA. As long as there are "friendly" list maintainers that abuse mail headers for overcoming deficiencies of some MUAs, there will be no change for the better with MUAs. Sorry I'm a bit fussy here, but nowadays there is so much effort wasted with solving the wrong problems in making bad things bearable instead of fixing the underlying reasons in the first place Rainer Collin Kidder wrote > >>> I resent that you're trying to make this a personal thing. >>> >> >> I was going to answer the rest of this email, then I realized that the >> real problem was right here, and discussing anything else was dancing >> around the issue and wasting time. >> >> You can resent it or not, but this _is_ a personal thing. It's personal >> because you are the only one complaining about it. Despite the large >> number of people on this list, I don't see anyone jumping in to defend >> you. >> >> I'm not saying your problems aren't real, I'm just saying you're >> apparently >> the only person in this community that has enough trouble with them to >> take the time to start a discussion. To that degree, the problem is >> very personal to you. >> >> > > I was going to stay out of this but I'll jump in and defend him. The > people on this list are so pedantic, so sure that their way is the only > way that they absolutely rain nuclear fire down on anyone who dares to > disagree. And you wonder why no one sprang to his defense??? > > And, I do agree with him on this issue. > -- 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] is a 'pairwise' possible / feasible in SQL?
Rajarshi Guha wrote > > On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote: >> On 2:08 pm 08/04/08 Rajarshi Guha <[EMAIL PROTECTED]> wrote: >>> paircount >>> - - >>> 123 & 456 1 >>> 667 & 879 2 > > > >> select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join >> aic_cid b on a.cid <>b.cid and a.id = b.id where b.cid is not null >> group by >> a.cid, b.cid order by a.cid; >> ac | bc | count >> -+-+--- >> 123 | 456 | 1 >> 123 | 667 | 1 >> 123 | 878 | 1 >> 123 | 879 | 1 >> 456 | 123 | 1 >> 456 | 878 | 1 >> 667 | 123 | 1 >> 667 | 879 | 2 >> 667 | 999 | 1 >> 878 | 123 | 1 >> 878 | 456 | 1 >> 879 | 123 | 1 >> 879 | 667 | 2 >> 879 | 999 | 1 >> 999 | 667 | 1 >> 999 | 879 | 1 > >> Is that what you are looking for? > > Thanks a lot - this is very close. Ideally, I'd want unique pairs, so > the row > > 879 | 999 | 1 > > is the same as > > 999 | 879 | 1 > > Can these duplicates be avoided? Depends on values and other distinguishing attributes For the given example - assuming pairing of a given cid with itself is not to be expected: add a "and a.cid < b.cid" to the query Rainer > > --- > Rajarshi Guha <[EMAIL PROTECTED]> > GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84 > --- > How I wish I were what I was when I wished I were what I am. > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
