Re: [SQL] order by when using cursors
Pavel Stehule wrote: >> it's known problem - column and variable names collision, so when you >> use any SQL statement inside procedure you have to be carefully about >> using variable names. Oh, I didn't took notice of that. Now knowing it is not a bug and how it works, it makes things much easier!: Thank you! Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] index find method?
hi list, when I do: CREATE INDEX name_index ON some_table (some_col); what method(hash,btree,rtree,etc.) use by default? -- Jorge Andrés Medina Oliva. Systems Manager and Developer. BSDCHiLE. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] index find method?
Hello, The Default method is btree, that is the commonly method used in the indexes. Rafael Domiciano DBA Postgres Senffnet 2008/6/18 Jorge Medina <[EMAIL PROTECTED]>: > hi list, > when I do: > CREATE INDEX name_index ON some_table (some_col); > what method(hash,btree,rtree,etc.) use by default? > > -- > Jorge Andrés Medina Oliva. > Systems Manager and Developer. > BSDCHiLE. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] using calculated column in where-clause
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Patrick > Scharrenberg > Enviado el: Martes, 17 de Junio de 2008 17:46 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] using calculated column in where-clause > > Hi! > > I'd like to do some calculation with values from the table, > show them a new column and use the values in a where-clause. > > Something like this > select a, b , a*b as c from ta where c=2; > > But postgresql complains, that column "c" does not exist. > > Do I have to repeat the calculation (which might be even more complex > :-) ) in the "where"-clause, or is there a better way? > For complex calculations I have obtained better performance using nested queries. For example: select a, b, c select ( select a, b, a*b as c from ta) subquery1 where c = 2; This nesting is probably overhead in such a simple case as this, but in more complex ones and specially with volatile functions it will provide an improvement. Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using calculated column in where-clause
On Wed, Jun 18, 2008 at 1:35 PM, Fernando Hevia <[EMAIL PROTECTED]> wrote: > >> -Mensaje original- >> De: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] En nombre de Patrick >> Scharrenberg >> Enviado el: Martes, 17 de Junio de 2008 17:46 >> Para: pgsql-sql@postgresql.org >> Asunto: [SQL] using calculated column in where-clause >> >> Hi! >> >> I'd like to do some calculation with values from the table, >> show them a new column and use the values in a where-clause. >> >> Something like this >> select a, b , a*b as c from ta where c=2; >> >> But postgresql complains, that column "c" does not exist. >> >> Do I have to repeat the calculation (which might be even more complex >> :-) ) in the "where"-clause, or is there a better way? >> > > For complex calculations I have obtained better performance using nested > queries. For example: > > select a, b, c select > ( select a, b, a*b as c from ta) subquery1 > where c = 2; > > This nesting is probably overhead in such a simple case as this, but in more > complex ones and specially with volatile functions it will provide an > improvement. I was under the impresion from previous discussions that the query planner flattened these out to be the same query. Do you get different query plans when you re-arrange this way? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
