Re: [SQL] NULLIF problem
Thanks! On Nov 28, 2007 10:47 AM, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > On Nov 27, 2007, at 21:04 , Gera Mel Handumon wrote: > > > I encounter an error if i use NULLIF with timestamp with time zone. > > eq. dbtime=nullif(mytime,'') > > > > i want to null the value of field DBTIME if the variable mytime=" " > > > > DBTIME ="timestamp with time zone" datatype > > > > error: column DBTIME is of type timestamp with time zone but > > expression is of type text. > > I believe the reason is that '' is not a valid timestamp value: think > of it this way: > > IF mytime = '' THEN > mytime := NULL; > END IF; > > The first thing it needs to do is compare the mytime value with ''. > As '' is not a valid timestamp value, it may be casing mytime to > text. You'll run into problems if you're assigning a text value to a > timestamp field (which happens after the initial comparison--and the > cast--are done.) > > I think you may need to handle this is you middleware, or handle the > IF THEN explicitly in a function. Maybe CASE would work: > > CASE WHEN mytime = '' THEN NULL > ELSE CAST(mytime AS TIMESTAMP) > END > > Michael Glaesemann > grzm seespotcode net > > > -- Gera Mel E. Handumon Application Programmer PaySoft Solutions, Inc. - "Share your knowledge. It's a way to achieve immortality" - Dalai Lama
Re: [SQL] NULLIF problem
On Nov 28, 2007, at 14:00 , Erik Jones wrote: Why not just: UPDATE table SET mytime=NULL WHERE mytime=''; If mytime is a timestamp field, it won't have any values ''. I believe the OP is updating mytime to a client-supplied value which is passing '' when it probably means NULL. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] statement-level trigger sample out there?
Hi Can anyone provide me a simple example of a statement-level trigger? Problem is I do not know how to access the old.*, new.* values. Thanks Chris -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail ---(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] statement-level trigger sample out there?
Hello You cannot to access to values in statement trigger. Postgres doesn't support it. Regards Pavel Stehule On 29/11/2007, Christian Kindler <[EMAIL PROTECTED]> wrote: > Hi > > Can anyone provide me a simple example of a statement-level trigger? Problem > is I do not know how to access the old.*, new.* values. > > Thanks > Chris > -- > GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. > Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail > > ---(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 > ---(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] statement-level trigger sample out there?
I am curious (coming from a MS SQL Server background, I just started playing with PostgreSQL recently). What type of situation would warrant a statement-level trigger that can't access the old and new values? Without that access, isn't the only information you get is the fact that an operation occurred on the table? Or am I missing something? -- Stephen Pavel Stehule wrote: Hello You cannot to access to values in statement trigger. Postgres doesn't support it. Regards Pavel Stehule On 29/11/2007, Christian Kindler <[EMAIL PROTECTED]> wrote: Hi Can anyone provide me a simple example of a statement-level trigger? Problem is I do not know how to access the old.*, new.* values. Thanks Chris -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail ---(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 ---(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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] statement-level trigger sample out there?
On 29/11/2007, Stephen Cook <[EMAIL PROTECTED]> wrote: > I am curious (coming from a MS SQL Server background, I just started > playing with PostgreSQL recently). yes, I belive. I was too. There are total different style of triggeres in Postgres. But If I can say, programming in Postgres is much more confortable. But It is different. You have to use ROW triggers. > > What type of situation would warrant a statement-level trigger that > can't access the old and new values? Without that access, isn't the > only information you get is the fact that an operation occurred on the > table? Or am I missing something? > > -- Stephen > Statement triggers can be used in some kind of auditing. Maybe. I didn't use it. And I use Postgres five years. Pavel > > > Pavel Stehule wrote: > > Hello > > > > You cannot to access to values in statement trigger. Postgres doesn't > > support it. > > > > Regards > > Pavel Stehule > > > > On 29/11/2007, Christian Kindler <[EMAIL PROTECTED]> wrote: > >> Hi > >> > >> Can anyone provide me a simple example of a statement-level trigger? > >> Problem is I do not know how to access the old.*, new.* values. > >> > >> Thanks > >> Chris > >> -- > >> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. > >> Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail > >> > >> ---(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 > >> > > > > ---(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 > > > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL]
Hmmm deamn ... I have a realy big table (> 2'000'000 records). every second there are several inserts and updates. the thing is i need a last row reference depending on a foreing_key. something like this: id, foreign_key, last_id, value1, value1, date >1<, 3,null, 12, 13, 2007-01-01 >2<, 4,null, 11, 10, 2007-01-01 4, 3, >1<, 12, 13, 2007-01-02 5, 4, >2<, 11, 10, 2007-01-02 ... of course the sequence can have holes so I have to calculate the real last row id. for now i calculate for each row by invoking a "select max(id) where foreign_key = $1" but this cost a lot of performance. I could do this easily with one update for the whole query - if i could know which foreign_key and which date range was performed. any ideas? thanks chris On Thu, November 29, 2007 3:26 pm, Pavel Stehule wrote: > On 29/11/2007, Stephen Cook <[EMAIL PROTECTED]> wrote: >> I am curious (coming from a MS SQL Server background, I just started >> playing with PostgreSQL recently). > > yes, I belive. I was too. There are total different style of triggeres > in Postgres. But If I can say, programming in Postgres is much more > confortable. But It is different. You have to use ROW triggers. > >> >> What type of situation would warrant a statement-level trigger that >> can't access the old and new values? Without that access, isn't the >> only information you get is the fact that an operation occurred on the >> table? Or am I missing something? >> >> -- Stephen >> > > Statement triggers can be used in some kind of auditing. Maybe. I > didn't use it. And I use Postgres five years. > > Pavel >> >> >> Pavel Stehule wrote: >> > Hello >> > >> > You cannot to access to values in statement trigger. Postgres doesn't >> > support it. >> > >> > Regards >> > Pavel Stehule >> > >> > On 29/11/2007, Christian Kindler <[EMAIL PROTECTED]> wrote: >> >> Hi >> >> >> >> Can anyone provide me a simple example of a statement-level trigger? >> Problem is I do not know how to access the old.*, new.* values. >> >> >> >> Thanks >> >> Chris >> >> -- >> >> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. >> >> Alle Infos und kostenlose Anmeldung: >> http://www.gmx.net/de/go/freemail >> >> >> >> ---(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 >> >> >> > >> > ---(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 >> > >> >> ---(end of broadcast)--- >> TIP 7: You can help support the PostgreSQL project by donating at >> >> http://www.postgresql.org/about/donate >> > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- cu Chris Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger ---(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] statement-level trigger sample out there?
SORRRY - have lost the subject :-) Hmmm deamn ... I have a realy big table (> 2'000'000 records). every second there are several inserts and updates. the thing is i need a last row reference depending on a foreing_key. something like this: id, foreign_key, last_id, value1, value1, date >1<, 3,null, 12, 13, 2007-01-01 >2<, 4,null, 11, 10, 2007-01-01 4, 3, >1<, 12, 13, 2007-01-02 5, 4, >2<, 11, 10, 2007-01-02 ... of course the sequence can have holes so I have to calculate the real last row id. for now i calculate for each row by invoking a "select max(id) where foreign_key = $1" but this cost a lot of performance. I could do this easily with one update for the whole query - if i could know which foreign_key and which date range was performed. any ideas? thanks chris On Thu, November 29, 2007 3:26 pm, Pavel Stehule wrote: > On 29/11/2007, Stephen Cook <[EMAIL PROTECTED]> wrote: >> I am curious (coming from a MS SQL Server background, I just started >> playing with PostgreSQL recently). > > yes, I belive. I was too. There are total different style of triggeres > in Postgres. But If I can say, programming in Postgres is much more > confortable. But It is different. You have to use ROW triggers. > >> >> What type of situation would warrant a statement-level trigger that >> can't access the old and new values? Without that access, isn't the >> only information you get is the fact that an operation occurred on the >> table? Or am I missing something? >> >> -- Stephen >> > > Statement triggers can be used in some kind of auditing. Maybe. I > didn't use it. And I use Postgres five years. > > Pavel >> >> >> Pavel Stehule wrote: >> > Hello >> > >> > You cannot to access to values in statement trigger. Postgres doesn't >> > support it. >> > >> > Regards >> > Pavel Stehule >> > >> > On 29/11/2007, Christian Kindler <[EMAIL PROTECTED]> wrote: >> >> Hi >> >> >> >> Can anyone provide me a simple example of a statement-level trigger? >> Problem is I do not know how to access the old.*, new.* values. >> >> >> >> Thanks >> >> Chris >> >> -- >> >> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. >> >> Alle Infos und kostenlose Anmeldung: >> http://www.gmx.net/de/go/freemail >> >> >> >> ---(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 >> >> >> > >> > ---(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 >> > >> >> ---(end of broadcast)--- >> TIP 7: You can help support the PostgreSQL project by donating at >> >> http://www.postgresql.org/about/donate >> > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- cu Chris Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger -- cu Chris Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] statement-level trigger sample out there?
Stephen Cook wrote: I am curious (coming from a MS SQL Server background, I just started playing with PostgreSQL recently). What type of situation would warrant a statement-level trigger that can't access the old and new values? Without that access, isn't the only information you get is the fact that an operation occurred on the table? Or am I missing something? -- Stephen What about this. Suppose you have this table "planets": planet_name | star_id| There is a lot of stars, right? And if a very common query involves a "select planet_name, count(*) from planets group by star_id"Well, if there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per galaxy...Thats a lot of planets to count!!! So maybe you want a helper table who maintains such of subtotals. Well, each time you discover a new galaxy, insert every planet in the monster table, and *after* all the inserts, run a trigger for updating the helper table. Cheers. Gerardo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] statement-level trigger sample out there?
Gerardo Herzig escribió: > Stephen Cook wrote: > >> I am curious (coming from a MS SQL Server background, I just started >> playing with PostgreSQL recently). >> >> What type of situation would warrant a statement-level trigger that can't >> access the old and new values? Without that access, isn't the only >> information you get is the fact that an operation occurred on the table? >> Or am I missing something? > > What about this. Suppose you have this table "planets": > planet_name | star_id| > > There is a lot of stars, right? And if a very common query involves a > "select planet_name, count(*) from planets group by star_id"Well, if > there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per > galaxy...Thats a lot of planets to count!!! So maybe you want a helper > table who maintains such of subtotals. > > Well, each time you discover a new galaxy, insert every planet in the > monster table, and *after* all the inserts, run a trigger for updating the > helper table. Right, but it would be much more useful if you can access the NEW set and instead of counting all the planets from scratch, you just take the current count and add the number of planets being added. You can do it with FOR EACH ROW triggers, but it's much worse because you need one UPDATE on the counter for each new planet. Perhaps the usefulness is that you store _in memory_ the number of planets added during the FOR EACH ROW trigger, and when that's done, call the FOR EACH STATEMENT trigger that does a single update adding the number in memory. This would work only if the FOR EACH STATEMENT trigger was promised to be executed after all the FOR EACH ROW triggers were called. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "No reniegues de lo que alguna vez creíste" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] statement-level trigger sample out there?
Alvaro Herrera wrote: Gerardo Herzig escribió: Stephen Cook wrote: I am curious (coming from a MS SQL Server background, I just started playing with PostgreSQL recently). What type of situation would warrant a statement-level trigger that can't access the old and new values? Without that access, isn't the only information you get is the fact that an operation occurred on the table? Or am I missing something? What about this. Suppose you have this table "planets": planet_name | star_id| There is a lot of stars, right? And if a very common query involves a "select planet_name, count(*) from planets group by star_id"Well, if there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per galaxy...Thats a lot of planets to count!!! So maybe you want a helper table who maintains such of subtotals. Well, each time you discover a new galaxy, insert every planet in the monster table, and *after* all the inserts, run a trigger for updating the helper table. Right, but it would be much more useful if you can access the NEW set and instead of counting all the planets from scratch, you just take the current count and add the number of planets being added. You can do it with FOR EACH ROW triggers, but it's much worse because you need one UPDATE on the counter for each new planet. Perhaps the usefulness is that you store _in memory_ the number of planets added during the FOR EACH ROW trigger, and when that's done, call the FOR EACH STATEMENT trigger that does a single update adding the number in memory. This would work only if the FOR EACH STATEMENT trigger was promised to be executed after all the FOR EACH ROW triggers were called. Shure. In that case, i will do the initial inserts into a temporary table, do the counting, updating the helper table, and then insert into the planets table. I use that approach and works fine to me. Gerardo ---(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
