Re: [SQL] GiST index question: performance
Steve Midgley wrote: > my ISP that manages my Pg SQL server is (in my interests) > concerned about installing anything non-standard (read: unstable) > onto their server. I was able to get them to install your TSearch2 > b/c it's been proven many times, but I'm hesitant to even bring up > Q3C since it's less widely deployed. How do you manage to get your own code installed under that theory? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Time of executed query
Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time of executed query
note the time just before your operation starts note the time just after it ends show timeafter - timebefore >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 14:20 >>> Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time of executed query
I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this result in a new column the same time will be replicated many times and consuming more processing. Any other suggestion ? Ezequias 2007/3/6, Bart Degryse <[EMAIL PROTECTED]>: note the time just before your operation starts note the time just after it ends show timeafter - timebefore >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 14:20 >>> Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] convert to a string
Hello
i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a
number 10
N_GEN (text) = 10
on error resume next
Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
If err <> 0 then 'If table not found
GetFieldValue = "1"
else
GetFieldValue = oRs("N_GEN") + 1
End If
But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to
it??
Shavonne Wijesinghe
http://www.studioform.it
Re: [SQL] Time of executed query
Did you try EXPLAIN ANALYZE ? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this result in a new column the same time will be replicated many times and consuming more processing. Any other suggestion ? Ezequias 2007/3/6, Bart Degryse <[EMAIL PROTECTED]>: > > > note the time just before your operation starts > note the time just after it ends > show timeafter - timebefore > > >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 > 14:20 >>> > > Hi list, > > It is possible to retrieve the time of a SQL statement leads to > execute ? I would like to put in my application how much time each > operation leads to finish. > > Any suggestion ? > > -- > Ezequias Rodrigues da Rocha > http://ezequiasrocha.blogspot.com/ > use Mozilla Firefox:http://br.mozdev.org/firefox/ > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time of executed query
Assuming you're using stored procedures... Start procedure with logging current time, name of procedure,... Perform whatever must be performed End procedure with logging current time, name of procedure,... Query logging table to get time spent Alternative, still assuming you're using stored procedures... Start procedure with noting current time, name of procedure,... Perform whatever must be performed End procedure with logging current time - start time, name of procedure,... Query logging table to get time spent >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 14:37 >>> I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this result in a new column the same time will be replicated many times and consuming more processing. Any other suggestion ? Ezequias 2007/3/6, Bart Degryse <[EMAIL PROTECTED]>: > > > note the time just before your operation starts > note the time just after it ends > show timeafter - timebefore > > >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 > 14:20 >>> > > Hi list, > > It is possible to retrieve the time of a SQL statement leads to > execute ? I would like to put in my application how much time each > operation leads to finish. > > Any suggestion ? > > -- > Ezequias Rodrigues da Rocha > http://ezequiasrocha.blogspot.com/ > use Mozilla Firefox:http://br.mozdev.org/firefox/ > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] convert to a string
I assume GetFieldValue is the name of a VB function or property get?
Try either
Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
or
GetFieldValue = cint(oRs("N_GEN")) + 1
or
GetFieldValue = clng(oRs("N_GEN")) + 1
In the last two cases make sure that the function or property get returns an
int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-03-06 14:42 >>>
Hello
i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a
number 10
N_GEN (text) = 10
on error resume next
Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
If err <> 0 then 'If table not found
GetFieldValue = "1"
else
GetFieldValue = oRs("N_GEN") + 1
End If
But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to
it??
Shavonne Wijesinghe
http://www.studioform.it ( http://www.studioform.it/ )
Re: [SQL] convert to a string
am Tue, dem 06.03.2007, um 14:42:53 +0100 mailte Shavonne Marietta Wijesinghe
folgendes:
> Hello
>
> i have a column N_GEN in postgreSql defined as text. In this coloumn i insert
> a
> number 10
Why do you store numbers as text?
>
> N_GEN (text) = 10
>
>
> on error resume next
> Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
>
> But it doesn't add the 1 to my N_GEN
You can't add a number to a string.
> How can i set the Recordset to convert the string to a number and then add 1
> to
> it??
You can try to cast it to int:
test=# select '10'::text;
text
--
10
(1 row)
test=*# select '10'::text::int;
int4
--
10
(1 row)
In your Query, change 'SELECT N_GEN FROM' to 'SELECT N_GEN::int FROM'.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Time of executed query
EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My resultset is retrieving my rows. Another question that cames with the Analyze. PgAdmin return the time to retrieve the data or all time to fill the grid on the "SQL Editor". -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ 2007/3/6, Shoaib Mir <[EMAIL PROTECTED]>: Did you try EXPLAIN ANALYZE ? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > I preffer that the database give me this information. I don't know if > it is possible becouse if we retrieve many rows and if we want to put > this result in a new column the same time will be replicated many > times and consuming more processing. > > Any other suggestion ? > Ezequias > > 2007/3/6, Bart Degryse <[EMAIL PROTECTED]>: > > > > > > note the time just before your operation starts > > note the time just after it ends > > show timeafter - timebefore > > > > >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED] > 2007-03-06 > > 14:20 >>> > > > > Hi list, > > > > It is possible to retrieve the time of a SQL statement leads to > > execute ? I would like to put in my application how much time each > > operation leads to finish. > > > > Any suggestion ? > > > > -- > > Ezequias Rodrigues da Rocha > > http://ezequiasrocha.blogspot.com/ > > use Mozilla Firefox: http://br.mozdev.org/firefox/ > > > > ---(end of > > broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > > > -- > Ezequias Rodrigues da Rocha > http://ezequiasrocha.blogspot.com/ > use Mozilla Firefox:http://br.mozdev.org/firefox/ > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Time of executed query
EXPLAIN ANALYZE is only for tuning purposes. Read the manual (http://www.postgresql.org/docs/8.1/interactive/sql-explain.html) to get more insight >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 14:55 >>> EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My resultset is retrieving my rows. Another question that cames with the Analyze. PgAdmin return the time to retrieve the data or all time to fill the grid on the "SQL Editor". -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ 2007/3/6, Shoaib Mir <[EMAIL PROTECTED]>: > Did you try EXPLAIN ANALYZE ? > > --- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > > On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > > I preffer that the database give me this information. I don't know if > > it is possible becouse if we retrieve many rows and if we want to put > > this result in a new column the same time will be replicated many > > times and consuming more processing. > > > > Any other suggestion ? > > Ezequias > > > > 2007/3/6, Bart Degryse <[EMAIL PROTECTED]>: > > > > > > > > > note the time just before your operation starts > > > note the time just after it ends > > > show timeafter - timebefore > > > > > > >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED] > 2007-03-06 > > > 14:20 >>> > > > > > > Hi list, > > > > > > It is possible to retrieve the time of a SQL statement leads to > > > execute ? I would like to put in my application how much time each > > > operation leads to finish. > > > > > > Any suggestion ? > > > > > > -- > > > Ezequias Rodrigues da Rocha > > > http://ezequiasrocha.blogspot.com/ > > > use Mozilla Firefox: http://br.mozdev.org/firefox/ > > > > > > ---(end of > > > broadcast)--- > > > TIP 5: don't forget to increase your free space map settings > > > > > > > > > -- > > Ezequias Rodrigues da Rocha > > http://ezequiasrocha.blogspot.com/ > > use Mozilla Firefox:http://br.mozdev.org/firefox/ > > > > ---(end of > broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > >
Re: [SQL] Time of executed query
EXPLAIN ANALYZE does give the query execution time at the end of its output. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My resultset is retrieving my rows. Another question that cames with the Analyze. PgAdmin return the time to retrieve the data or all time to fill the grid on the "SQL Editor". -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ 2007/3/6, Shoaib Mir <[EMAIL PROTECTED]>: > Did you try EXPLAIN ANALYZE ? > > --- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > > On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > > I preffer that the database give me this information. I don't know if > > it is possible becouse if we retrieve many rows and if we want to put > > this result in a new column the same time will be replicated many > > times and consuming more processing. > > > > Any other suggestion ? > > Ezequias > > > > 2007/3/6, Bart Degryse <[EMAIL PROTECTED]>: > > > > > > > > > note the time just before your operation starts > > > note the time just after it ends > > > show timeafter - timebefore > > > > > > >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED] > 2007-03-06 > > > 14:20 >>> > > > > > > Hi list, > > > > > > It is possible to retrieve the time of a SQL statement leads to > > > execute ? I would like to put in my application how much time each > > > operation leads to finish. > > > > > > Any suggestion ? > > > > > > -- > > > Ezequias Rodrigues da Rocha > > > http://ezequiasrocha.blogspot.com/ > > > use Mozilla Firefox: http://br.mozdev.org/firefox/ > > > > > > ---(end of > > > broadcast)--- > > > TIP 5: don't forget to increase your free space map settings > > > > > > > > > -- > > Ezequias Rodrigues da Rocha > > http://ezequiasrocha.blogspot.com/ > > use Mozilla Firefox:http://br.mozdev.org/firefox/ > > > > ---(end of > broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > >
Re: [SQL] convert to a string
when i try the
set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
it returns 1 and for the rest i doens't change. alsways 10
:(
- Original Message -
From: Bart Degryse
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 2:54 PM
Subject: Re: [SQL] convert to a string
I assume GetFieldValue is the name of a VB function or property get?
Try either
Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN
DESC")
or
GetFieldValue = cint(oRs("N_GEN")) + 1
or
GetFieldValue = clng(oRs("N_GEN")) + 1
In the last two cases make sure that the function or property get returns an
int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-03-06 14:42 >>>
Hello
i have a column N_GEN in postgreSql defined as text. In this coloumn i insert
a number 10
N_GEN (text) = 10
on error resume next
Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
If err <> 0 then 'If table not found
GetFieldValue = "1"
else
GetFieldValue = oRs("N_GEN") + 1
End If
But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1
to it??
Shavonne Wijesinghe
http://www.studioform.it
Re: [SQL] convert to a string
As Andreas already asked: do you really have a good reason to store numbers as
text
If so, show us some more information: table definition, some table data, a
complete (vb?) function/property get, ... anything that is directly involved.
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-03-06 15:32 >>>
when i try the
set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
it returns 1 and for the rest i doens't change. alsways 10
:(
- Original Message -
From: Bart Degryse ( mailto:[EMAIL PROTECTED] )
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 2:54 PM
Subject: Re: [SQL] convert to a string
I assume GetFieldValue is the name of a VB function or property get?
Try either
Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
or
GetFieldValue = cint(oRs("N_GEN")) + 1
or
GetFieldValue = clng(oRs("N_GEN")) + 1
In the last two cases make sure that the function or property get returns an
int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-03-06 14:42 >>>
Hello
i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a
number 10
N_GEN (text) = 10
on error resume next
Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
If err <> 0 then 'If table not found
GetFieldValue = "1"
else
GetFieldValue = oRs("N_GEN") + 1
End If
But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to
it??
Shavonne Wijesinghe
http://www.studioform.it ( http://www.studioform.it/ )
Re: [SQL] convert to a string
i figured it out
Set oRs = oConn.Execute("SELECT N_GEN FROM " & MyTable & " ORDER BY N_GEN::INT
DESC")
thanks to everyone
- Original Message -
From: Shavonne Marietta Wijesinghe
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 3:32 PM
Subject: Re: [SQL] convert to a string
when i try the
set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
it returns 1 and for the rest i doens't change. alsways 10
:(
- Original Message -
From: Bart Degryse
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 2:54 PM
Subject: Re: [SQL] convert to a string
I assume GetFieldValue is the name of a VB function or property get?
Try either
Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN
DESC")
or
GetFieldValue = cint(oRs("N_GEN")) + 1
or
GetFieldValue = clng(oRs("N_GEN")) + 1
In the last two cases make sure that the function or property get returns
an int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-03-06 14:42 >>>
Hello
i have a column N_GEN in postgreSql defined as text. In this coloumn i
insert a number 10
N_GEN (text) = 10
on error resume next
Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
If err <> 0 then 'If table not found
GetFieldValue = "1"
else
GetFieldValue = oRs("N_GEN") + 1
End If
But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add
1 to it??
Shavonne Wijesinghe
http://www.studioform.it
[SQL] Selecting exactly one row for each column value
I've got the following table: fweimer=> SELECT * FROM tab; a | b | c ---+---+--- 1 | 2 | 3 5 | 6 | 7 1 | 2 | 2 2 | 3 | 4 1 | 2 | 2 2 | 3 | 4 For each value in the first column, I need one (and only one) matching row from the table. A possible solution is: a | b | c ---+---+--- 5 | 6 | 7 2 | 3 | 4 1 | 2 | 3 Of course, SELECT a, (SELECT b FROM tab i WHERE i.a = o.a LIMIT 1), (SELECT c FROM TAB i WHERE i.a = o.a LIMIT 1) FROM tab o GROUP BY o.a; does the trick, but this approach seems to rely on undefined behavior and quickly gets messy when the number of columns increases. Is there a better way to implement this? -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Selecting exactly one row for each column value
am Tue, dem 06.03.2007, um 16:03:36 +0100 mailte Florian Weimer folgendes: > Is there a better way to implement this? DISTINCT ON() http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] [Fwd: View Vs. Table]
Hi, I have two tables which currently are being aggregated into a third table. I am proposing eliminating or drastically shortening the 3rd aggregation table, and instead just using a View. Which brings me to the question, which is better? Reading from a table or a view or is there a difference in performance? >From the application's perspective the View is much better as data is being stored in one place, reducing errors and also storage. But from the db point of view, is there a bigger performace hit when I query a view Vs a table or is there no difference. Thanks, Radhika -- It is all a matter of perspective. You choose your view by choosing where to stand. Larry Wall --- -- It is all a matter of perspective. You choose your view by choosing where to stand. Larry Wall --- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Selecting exactly one row for each column value
Florian Weimer <[EMAIL PROTECTED]> writes: > For each value in the first column, I need one (and only one) matching > row from the table. A possible solution is: SELECT DISTINCT ON would do it, if you don't mind a non-portable solution. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time of executed query
On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ Use psql: t=# \timing t=# SELECT * FROM generate_series(1,10) WHERE random() = 0.234; generate_series - (0 rows) Time: 125.000 ms t=# See: http://www.postgresql.org/docs/8.2/static/app-psql.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Time of executed query
On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ Sorry, didn't see the app part. Use clock_timestamp(). See: http://www.postgresql.org/docs/8.2/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Selecting exactly one row for each column value
* Tom Lane: > Florian Weimer <[EMAIL PROTECTED]> writes: >> For each value in the first column, I need one (and only one) matching >> row from the table. A possible solution is: > > SELECT DISTINCT ON would do it, if you don't mind a non-portable solution. Cool, thanks a lot. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [Fwd: View Vs. Table]
On Tue, Mar 06, 2007 at 09:40:52AM -0500, Radhika Sambamurti wrote: > But from the db point of view, is there a bigger performace hit when I > query a view Vs a table or is there no difference. It rather depends on your view definition. See the discussion of views and, especially, the discussion of rules in the manual (views are basically just an automatic application of certain rules). A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Inc
Hi list, There is any function to increment with 1 some field. For example. I have a table with a field that on each update it incrementes a field that is allways configured to 0 before the starting of updates. Regards -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Inc
Ezequias Rodrigues da Rocha wrote: Hi list, There is any function to increment with 1 some field. For example. I have a table with a field that on each update it incrementes a field that is allways configured to 0 before the starting of updates. Something like: CREATE FUNCTION my_autoinc() RETURNS TRIGGER AS $$ BEGIN NEW.counter := NEW.counter + 1; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER my_counter_trig BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE my_autoinc(); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Inc
Thank you but I must inc an specific row. How to do that ? Ezequias 2007/3/6, Richard Huxton : Ezequias Rodrigues da Rocha wrote: > Hi list, > > There is any function to increment with 1 some field. > > For example. I have a table with a field that on each update it > incrementes a field that is allways configured to 0 before the > starting of updates. Something like: CREATE FUNCTION my_autoinc() RETURNS TRIGGER AS $$ BEGIN NEW.counter := NEW.counter + 1; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER my_counter_trig BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE my_autoinc(); -- Richard Huxton Archonet Ltd -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Inc
--- Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > Thank you but I must inc an specific row. How to do that ? > Does, UPDATE your_table SET your_row = your_row +1 WHERE your_pkey = ; not do what you need? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] GiST index question: performance
Hi Peter, :) All my Pg code is written via (or handed to) an abstraction layer, and I actually write no functions or stored procedures at all. I write using Rails, so in this case it's a Ruby library called ActiveRecord which has a Postgres module that allows me to talk via "ActiveRecord-speak" or via direct Postgres sql commands. (For example, AR has no idea how to create a GiST index, so I issue that DDL statement manually using the special syntax - also AR is not always so smart about SQL queries so tricky ones I write by hand). Maybe I misunderstand Q3C completely but it looks like C code that has to be installed into the Postgres server itself - not a series of SQL functions that can implemented on an unmodified server. I think my ISP is fine with anything that gets installed via user-level privileges. Anything that requires root and/or anything that involves binary code they are more cautious about. To be fair, I'm cautious about the same things, but given Oleg's reputation and contributions to Pg, I wouldn't be so concerned about Q3C specifically. Am I ignorant of something fundamental in this conversation? I really do appreciate any education or insight here. Are C code "patches" or functions more of a risk to server stability/reliability than higher level code? Or am I speaking gibberish? Thanks, Steve At 01:01 AM 3/6/2007, Peter Eisentraut wrote: Steve Midgley wrote: > my ISP that manages my Pg SQL server is (in my interests) > concerned about installing anything non-standard (read: unstable) > onto their server. I was able to get them to install your TSearch2 > b/c it's been proven many times, but I'm hesitant to even bring up > Q3C since it's less widely deployed. How do you manage to get your own code installed under that theory? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Inc
Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> schrieb: > Thank you but I must inc an specific row. How to do that ? Read the answer again. Please, no silly TOFU (german synonym for text above, fullquote below). > >> For example. I have a table with a field that on each update it > >> incrementes a field that is allways configured to 0 before the > >> starting of updates. > >Something like: > >CREATE FUNCTION my_autoinc() RETURNS TRIGGER AS $$ > >BEGIN > > NEW.counter := NEW.counter + 1; > > RETURN NEW; > >END; > >$$ LANGUAGE plpgsql; > >CREATE TRIGGER my_counter_trig BEFORE UPDATE ON my_table > >FOR EACH ROW EXECUTE PROCEDURE my_autoinc(); Your task is only to adapt this to your table-design. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Inc
Richard Broersma Jr <[EMAIL PROTECTED]> schrieb: > --- Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > > > Thank you but I must inc an specific row. How to do that ? > > > > Does, > > UPDATE your_table > SET your_row = your_row +1 > WHERE your_pkey = ; > > not do what you need? I think, he is searching for a TRIGGER and he has got the solution. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Inc
Now that's ok. Thank you all. I just thought that there was a function to inc an integer field in postgresql. Best regards Ezequias 2007/3/6, Andreas Kretschmer <[EMAIL PROTECTED]>: Richard Broersma Jr <[EMAIL PROTECTED]> schrieb: > --- Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > > > Thank you but I must inc an specific row. How to do that ? > > > > Does, > > UPDATE your_table > SET your_row = your_row +1 > WHERE your_pkey = ; > > not do what you need? I think, he is searching for a TRIGGER and he has got the solution. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Selecting exactly one row for each column value
Am Dienstag, 6. März 2007 16:03 schrieb Florian Weimer: > a | b | c > ---+---+--- > 5 | 6 | 7 > 2 | 3 | 4 > 1 | 2 | 3 Hi, couldn't you accomplish this by: select distinct on (a) * from tablename order by a; here: create table tab (a int,b int,c int); insert into tab values (1,2,3); insert into tab values (5,6,7); insert into tab values (1,2,3); insert into tab values (2,3,4); insert into tab values (1,2,2); insert into tab values (2,3,4); select * from tab; a | b | c ---+---+--- 1 | 2 | 3 5 | 6 | 7 1 | 2 | 3 2 | 3 | 4 1 | 2 | 2 2 | 3 | 4 (6 rows) select distinct on (a) * from tab order by a; a | b | c ---+---+--- 1 | 2 | 3 2 | 3 | 4 5 | 6 | 7 (3 rows) my regards, Stefan -- email: [EMAIL PROTECTED] tel : +49 (0)6232-497631 http://www.yukonho.de ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Inc
Ezequias Rodrigues da Rocha wrote: Thank you but I must inc an specific row. How to do that ? I'm not sure I understand. A row in a different table? ... BEGIN UPDATE some_table SET counter = counter + 1; END; ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] GiST index question: performance
On Tue, 6 Mar 2007, Steve Midgley wrote: Hi Peter, :) All my Pg code is written via (or handed to) an abstraction layer, and I actually write no functions or stored procedures at all. I write using Rails, so in this case it's a Ruby library called ActiveRecord which has a Postgres module that allows me to talk via "ActiveRecord-speak" or via direct Postgres sql commands. (For example, AR has no idea how to create a GiST index, so I issue that DDL statement manually using the special syntax - also AR is not always so smart about SQL queries so tricky ones I write by hand). Maybe I misunderstand Q3C completely but it looks like C code that has to be installed into the Postgres server itself - not a series of SQL functions that can implemented on an unmodified server. I think my ISP is fine with anything that gets installed via user-level privileges. Anything that requires root and/or anything that involves binary code they are more cautious about. Q3C as a contrib module doesn't require root priviliges, you could compile it in your home directory ! The only issue is that you should have pg superuser rights, but you can always ask somebody with such rights to install compiled module to your database. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Inc
I did like this: update base.destinationTable set MyCountField = MyCountField + 1 where id = new.keyField_ID; I think it works becouse my tests are ok. My really thank you all. Ezequias 2007/3/6, Richard Huxton : Ezequias Rodrigues da Rocha wrote: > Thank you but I must inc an specific row. How to do that ? I'm not sure I understand. A row in a different table? ... BEGIN UPDATE some_table SET counter = counter + 1; END; ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] best index for ~ ordering?
Hi all, i have a large table with one varchar field, and im triyng to get information about what index should i use in order to using that index (if this is possible) for ~ searching, and/or using LIKE searching. Thanks! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] best index for ~ ordering?
On Tue, Mar 06, 2007 at 06:34:23PM -0300, Gerardo Herzig wrote: > Hi all, i have a large table with one varchar field, and im triyng to > get information about what index should i use in order to using that > index (if this is possible) for ~ searching, and/or using LIKE searching. What sort of match are you doing? If you're doing left-anchored searches (LIKE 'blah%') then your standard btree is good. If you're doing unanchored searches (LIKE '%blah%' or similar) you're best doing tsearch. If it's right-anchored, you can do an index on the reverse of the string. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to union table without union statement?
I think the tables should contain 50k rows of record and it should be insert 7k rows per month. And the condition of the database is running, so it should not change the tables to partitioning right now. I am looking for some JOIN statement but still doesn't understand how to use JOIN to replace UNION, so dose anyone can give me direction? Thanks. On 3/1/07, Shane Ambler <[EMAIL PROTECTED]> wrote: Have you done an EXPLAIN on the query? Is there an index on the tagname columns? If so does the EXPLAIN show them being used? How many rows do you have in each table (roughly)? Have you considered other structure options like partitioning? Is there a real need to have these tables separate? or could you have them all in one table with an column to identify the source of the log entry? -- Jr. P calendarw
Re: [SQL] best index for ~ ordering?
Well, im wondering if is possible using LIKE '%blah%', even better would be upper/lower(string) like '%blah%', Im not at work right now, i will try it latter and makes you know about the results of using tsearch indexing. Thanks a lot, man! Gerardo > On Tue, Mar 06, 2007 at 06:34:23PM -0300, Gerardo Herzig wrote: >> Hi all, i have a large table with one varchar field, and im triyng to >> get information about what index should i use in order to using that >> index (if this is possible) for ~ searching, and/or using LIKE >> searching. > > What sort of match are you doing? If you're doing left-anchored > searches (LIKE 'blah%') then your standard btree is good. If you're > doing unanchored searches (LIKE '%blah%' or similar) you're best doing > tsearch. If it's right-anchored, you can do an index on the reverse > of the string. > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > Unfortunately reformatting the Internet is a little more painful > than reformatting your hard drive when it gets out of whack. > --Scott Morris > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
