Обсуждение: example query for postgresql

Is there a place that gives examples of all the kind of queries
postgresql support?

Sometimes I think if there is a way to obtain a result in just one
statement without writing a function and it would be nice to have a
panoramic of all the strategies I could try for single statements.


The SQL reference
http://www.postgresql.org/docs/8.1/interactive/sql-commands.html
isn't inspirational every time

Even if it is not exactly what I'm looking for a sort of "one liner"
as the one you find for perl, sed, awk...

As an example of things the kind of things I'm looking for:

update system
  set weight=subselect._avg
  from (
    select round(avg(weight)) as _avg
    from system where type='module'
  ) as subselect
  where name='users_commerce';


thx

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On 19/01/2008 18:22, Ivan Sergio Borgonovo wrote:
> Is there a place that gives examples of all the kind of queries
> postgresql support?

This may not be what you're looking for, but I've found O'Reilly's "SQL
Cookbook" (ISBN 0-596-00976-3) incredibly useful: it gives sample
solutions to all sorts of problems in SQL suitable for PostgreSQL,
MySQL, DB2, SQL Server and Oracle.

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Jan 19, 2008, at 12:22 PM, Ivan Sergio Borgonovo wrote:

> Is there a place that gives examples of all the kind of queries
> postgresql support?
>
> Sometimes I think if there is a way to obtain a result in just one
> statement without writing a function and it would be nice to have a
> panoramic of all the strategies I could try for single statements.
>
>
> The SQL reference
> http://www.postgresql.org/docs/8.1/interactive/sql-commands.html
> isn't inspirational every time
>
> Even if it is not exactly what I'm looking for a sort of "one liner"
> as the one you find for perl, sed, awk...
>
> As an example of things the kind of things I'm looking for:

You're best bets are to a. post questions to the pgsql-sql mailing
list when you've got an idea that something you've written could be
done better, b. get some books demonstrating different query
techniques and read through them (O'Reilly has a couple of really
good ones such as SQL Hacks), and c. practice.  You're not going to
find any definitive list of everything you can do with Postgresql as
it's virtually unlimited.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

I've found that multiple simple statements often work faster then convoluted single statements.



Ivan Sergio Borgonovo wrote:
> Is there a place that gives examples of all the kind of queries
> postgresql support?
>
> Sometimes I think if there is a way to obtain a result in just one
> statement without writing a function and it would be nice to have a
> panoramic of all the strategies I could try for single statements.
>
>
> The SQL reference
> http://www.postgresql.org/docs/8.1/interactive/sql-commands.html
> isn't inspirational every time
>
> Even if it is not exactly what I'm looking for a sort of "one liner"
> as the one you find for perl, sed, awk...
>
> As an example of things the kind of things I'm looking for:
>
> update system
>   set weight=subselect._avg
>   from (
>     select round(avg(weight)) as _avg
>     from system where type='module'
>   ) as subselect
>   where name='users_commerce';
>
>
> thx
>

On Sun, 20 Jan 2008 09:30:11 +0200
Sim Zacks <sim@compulab.co.il> wrote:

> I've found that multiple simple statements often work faster then
> convoluted single statements.

I don't want to steal any further time to anyone considering my
interest now is just academic since I can't handle any more
information I could put into action shortly... but still is there a
good reason that several simple statement could run faster than a
more complex one?

Is the planner able to correlate nearby simple statements?
I'd think that a more complex statement gives more clues on what's
your target to the planner that can then find a better way to
achieve it.
And yeah... on more complex statement the human can make things
worse, writing unnecessary complex sql.


--
Ivan Sergio Borgonovo
http://www.webthatworks.it

I recall a time I was working on MSSQL and wrote a killer update statement that
took 35 minutes to run. I was trying to figure out how to improve the time so I
asked someone with more experience then me to look at it. He looked at it for a
while and said that he couldn't figure out how the query actually worked, but if
it were him he would write it in 5 statements.
I laughed at him (thinking I was much better), but as an experiment I rewrote it
in 5 simple statements and the whole thing ran in under a minute.

As I understand it, the planner does what you ask it to. If you tell it to do 4
inner joins and 7 left joins along with a couple sub-selects while munging the
data using functions, it will.

OTOH throwing data into a temporary table and running a couple of updates on it
and then updating 1 table against the second (or selecting the new data, for
that matter) can be a lot less work.

Sim

Ivan Sergio Borgonovo wrote:
> On Sun, 20 Jan 2008 09:30:11 +0200
> Sim Zacks <sim@compulab.co.il> wrote:
>
>> I've found that multiple simple statements often work faster then
>> convoluted single statements.
>
> I don't want to steal any further time to anyone considering my
> interest now is just academic since I can't handle any more
> information I could put into action shortly... but still is there a
> good reason that several simple statement could run faster than a
> more complex one?
>
> Is the planner able to correlate nearby simple statements?
> I'd think that a more complex statement gives more clues on what's
> your target to the planner that can then find a better way to
> achieve it.
> And yeah... on more complex statement the human can make things
> worse, writing unnecessary complex sql.
>
>