[GENERAL] can these queries be combined into one?
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
[GENERAL] table alias for update
is there a way to use a table alias for the target table in an UPDATE query? I would like to avoid spelling out things like that in psql: update myverylongtablename set col1 = r.col1repl from repltable r where myverylongtablename.id = r.id and myverylongtablename.col2 > 0 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] table alias for update
haman...@t-online.de wrote: > > is there a way to use a table alias for the target table in an UPDATE query? > I would like to avoid spelling out things like that in psql: > > update myverylongtablename set col1 = r.col1repl from repltable r > where myverylongtablename.id = r.id and myverylongtablename.col2 > 0 update myverylongtablename as xyz set col1 = ... where xyz.id = ...; The 'as' is optional ... though easier to read IMO. HTH Bosco. -- 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] Help in Parsing PG log usings CSV format
On 2012-03-22, Martin Gregorie wrote: > Thats fairly standard. A good CSV parser only requires a field to be > quoted if it contains commas or quotes. quotes,commas, or linebreaks copy ( values (2,'comma, etc'),(3,'and "quote".'),(1,'line break') ) to stdout with csv; -- ⚂⚃ 100% natural -- 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?
On Sun, 2012-03-25 at 07: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? > Difficult to be really precise, but you may do this with: UPDATE products SET codes = codes | 512 FROM othertable t WHERE AND ordercode = t.ordercode AND ... RETURNING ordercode, descr, codes; You need at least 8.2 to use the RETURNING clause. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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?
On 2012-03-25, 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 basically what you do is do a join and put some expressions in the select list. I used coalesce here as it was a better fit, but case can also be used... perhaps this: (needs procucts to have a primary key whih will need to be substituted in) select distinct on ( p.PRIMARYY_KEY ) coalesce( t.ordercode|512, p.orderecode ) as ordercode , p.descr, p.codes from products as p left outer join othertable as t on p.ordercode = t.ordercode and where ... ; -- ⚂⚃ 100% natural -- 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?
On Sun, 2012-03-25 at 08:41 +, haman...@t-online.de wrote: > Guillaume Lelarge worte: > >> > > >> > 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? > >> > > >> > >> Difficult to be really precise, but you may do this with: > >> > >> UPDATE products > >> SET codes = codes | 512 > >> FROM othertable t > >> WHERE > >> > >> AND ordercode = t.ordercode AND ... > >> RETURNING > >> ordercode, descr, codes; > >> > >> You need at least 8.2 to use the RETURNING clause. > >> > > Hi Guillaume, > > thanks a lot, but it is not really what I need: I want the whole lot, not > just the rows that were part > of the update > Oops, you're right. Never answer before having some coffee :) I guess you don't have much choice then. You'll need that temp table. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Is record handle available to a check constraint stored procedure call?
On 2012-03-23, Gauthier, Dave wrote: > --_000_0AD01C53605506449BA127FB8B99E5E10C358B82FMSMSX105amrcor_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > v8.3.4 on Linux > > I have a check constraint on a column. The constraint decides pass/fail ba= > sed on the returned status of a stored procedure call that returns either "= > OK" or "NO". So when the stored procedure is called, there's a living atte= > mpt to insert or update a record. > > Question: Is there a handle on the record being inserted or updated? Some= > thing like what's available in a trigger function (new.col1, old.col2, etc.= > ..)? If so, what does it look like? it looks like the name of the table create function somefunc( a foo ) returns boolean as $$ begin return a.id <> 7 ; end $$ language plpgsql; alter table foo add check ( somefunc(foo)); -- ⚂⚃ 100% natural -- 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] Streaming replication and empty databases
Le 2012-03-23 à 09:28, Svoop a écrit : > However, can I use the app_staging database on the staging server > independently? [...] > > Plan B is a second pg instance on the staging server, but I'd like to know if > the above setup might work as well. PostgreSQL disallows writes on slaves. Like you, I arrived at the 2nd cluster / instance running on the slave. I dump the database using pg_dump and reload in my second instance. Works great. Hope that helps! François -- 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?
On Mar 25, 2012, at 1:50, Guillaume Lelarge wrote: > On Sun, 2012-03-25 at 08:41 +, haman...@t-online.de wrote: >> Guillaume Lelarge worte: > > 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? > Difficult to be really precise, but you may do this with: UPDATE products SET codes = codes | 512 FROM othertable t WHERE AND ordercode = t.ordercode AND ... RETURNING ordercode, descr, codes; You need at least 8.2 to use the RETURNING clause. >> >> Hi Guillaume, >> >> thanks a lot, but it is not really what I need: I want the whole lot, not >> just the rows that were part >> of the update >> > > Oops, you're right. Never answer before having some coffee :) > > I guess you don't have much choice then. You'll need that temp table. > > Not seeing the whole original query but you generally omit the where clause and move the condition to a case statement on the update. For records not meeting your criteria you simply update the column with the existing value. David J. -- 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] Howto Replication for dummies?
Hi, On Sun, Mar 25, 2012 at 2:52 AM, Andreas wrote: > Is there a howto that explains what to do to get a mirror-server that > follows the productive system? Here it is http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray...@gmail.com Skype: gray-hemp -- 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] xlog corruption
i'm not suggesting that Postgres should necessarily be able to repair a corruption in the WAL, my issue is that there is no way of knowing that there was a corruption in the WAL because the normal detection of the end of the WAL is reported the same way as a corruption in the middle of the WAL. i'd like some unequivocal indication that the WAL was corrupted. thanks. From: Jeff Davis To: Jameison Martin Cc: "pgsql-general@postgresql.org" Sent: Wednesday, March 14, 2012 3:55 PM Subject: Re: [GENERAL] xlog corruption On Mon, 2012-02-27 at 16:30 -0800, Jameison Martin wrote: > I'd like to get some clarification around an architectural point about > recovery. I see that it is normal to see "unexpected pageaddr" errors > during recovery because of the way Postgres overwrites old log files, > and thus this is taken to be a normal termination condition, i.e. the > end of the log (see > http://doxygen.postgresql.org/xlog_8c.html#a0519e464bfaa79bde3e241e6cff986c7). > My question is how does recovery distinguish between the actual end of the > log as opposed to a log file corruption (e.g. torn page)? > > > I'd like to be able to distinguish between a corruption in the log vs. > a normal recovery condition if possible. If you have a power failure, a torn page in the WAL is expected. Torn pages in the data pages are fixed up using WAL; but WAL doesn't have anything under it to prevent/fix torn pages (unless your filesystem prevents them). Of course, checksums are used to prevent recovery from attempting to play a partial or otherwise corrupt WAL record. What kind of corruption are you trying to detect? Regards, Jeff Davis
Re: [GENERAL] pgcon 2012
On 03/24/2012 12:46 AM, Arvind Singh wrote: Is http://www.pgcon.org/2012/?2nd a valid and certified Postgres Event or is it just a marketing strategy by Private Event Management firm There is no such thing as a "certified" Postgres event. All of the conferences in the world are run by private groups and/or companies. In addition to what attendees normally pay, there are normally a number of companies involved in sponsoring each event. In many cases the events wouldn't be feasible without that sponsorship help. Some conferences also benefit heavily from the various PostgreSQL user's groups around the world too, like the yearly European conference that you might consider too: http://pgconf.eu There are some policies for who can use PostgreSQL community resources like the postgresql.org web site to promote their event, which you can see at http://wiki.postgresql.org/wiki/NewsEventsApproval But many organizations and companies meet those guidelines, and none of them are any more or less official than the others. One helpful thing to consider when deciding which conference to attend is whether the speakers are involved in the areas you're interested in. For the database itself, a list of many frequent contributors is found at http://www.postgresql.org/community/contributors/ If you check the PGCon speaker list, you'll see many of them are also on that contributor list. That's one reason that PGCon is an excellent conference. It's also very well run by its organizers, even though they are "private event management" by your definition. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
