Re: [SQL] When is a shared library unloaded?
> "Jon Horsman" <[EMAIL PROTECTED]> writes: > > I'm randomly having issues with my triggers not firing (it turns out > > they are getting removed) and when i turned INFO logs on i saw the > > following > > > 2007-08-14 13:41:44.740305500 LOCATION: _fini, medbevent_init.c:129 > > 2007-08-14 13:41:45.790850500 INFO: 0: medbeventlib -Unloading > > library _fini() is called > > > Could someone please explain when a shared library gets > > loaded/unloaded and how this all works? > > Are you sure the above doesn't just occur during server process > termination? AFAIK Postgres never unloads a library, except when you > specifically command it to load an updated version via the LOAD command. > > regards, tom lane Yup, the server process is still up and going, i have other clients connected to postgres that still function when this happens. I'm not using the LOAD command anywhere so thats not the problem. One of my colleagues was doing some reading and said he found something that stating that the fini() function is "unreliable", i'm not sure where he read that though... Jon. ---(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
[SQL] Boolean without default declared
If a column with a boolean datatype doesn't have a default value. What type of value is set if nothing is inserted into that column? Here is my test table and the queries I have tried. I can't seem to be able to select the rows where happy has no value. Table "public.users" Column | Type | Modifiers +---+--- id | character varying(32) | email | text | happy | boolean | money | numeric | *select * from users;* id | email | happy | money ++---+- 4 | me | | 1324.23 4 | me | | 1324.23 3 | as | | 123.2 1 | afjssd | t | *select * from users where happy;* id | email | happy | money ++---+--- 1 | afjssd | t | *select * from users where not happy;* id | email | happy | money +---+---+--- (0 rows) *select * from users where happy = NULL;* id | email | happy | money +---+---+--- (0 rows) *select * from users where happy = '';* ERROR: invalid input syntax for type boolean: "" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Boolean without default declared
--- Jon Collette <[EMAIL PROTECTED]> wrote: > If a column with a boolean datatype doesn't have a default value. What > type of value is set if nothing is inserted into that column? Here is > my test table and the queries I have tried. I can't seem to be able to > select the rows where happy has no value. > > *select * from users where happy = '';* > ERROR: invalid input syntax for type boolean: "" Databases have three value logic in it expressions. is the expression true, is the expression false, is the expreassion unknown i.e. null. SELECT * FROM users WHERE happy IS NULL; UPDATE users SET happy = false WHERE happy IS NULL; ALTER TABLE USER ALTER COLUMN happy SET NOT NULL; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Boolean without default declared
--- Jon Collette <[EMAIL PROTECTED]> wrote: > Thanks that was it exactly. I did notice another strange thing. When > using IS NULL in an plpgsql IF statement you have to use ISNULL. Same > with NOT NULL. Does this mean that ISNULL is more proper than IS NULL? Good question, I am not sure about the proper syntax for plpgsql. However, IS NULL is correct for sql statements. Also, don't forget to cc. the list so that other can chime in as well. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Problem with phone list.
Hi all. I've qot a problem I need to solve. I'm sure it's pretty simple; I just can't seem to get it, so here goes... I've got a table, actually a view that joins 3 tables, that contains a phone number, a unique id, and a call duration. The phone number has duplicates in it but the unique id is unique. I need to get a list of distinct phone numbers and the coorisponding largest call duration. I've got the idea that this should be a self-join on phone number where a.id<>b.id, but I just can't seem to get the max duration. Any hints would be much appreciated. Mike. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with phone list.
Try this: Select * from view v1 where duration = (select max(duration) from view v2 where v2.phone_number = v1.phone_number) You could get more than one call listed for the same number if many calls match max(duration) for that number. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Mike Diehl Enviado el: Miércoles, 15 de Agosto de 2007 17:28 Para: SQL Postgresql List Asunto: [SQL] Problem with phone list. Hi all. I've qot a problem I need to solve. I'm sure it's pretty simple; I just can't seem to get it, so here goes... I've got a table, actually a view that joins 3 tables, that contains a phone number, a unique id, and a call duration. The phone number has duplicates in it but the unique id is unique. I need to get a list of distinct phone numbers and the coorisponding largest call duration. I've got the idea that this should be a self-join on phone number where a.id<>b.id, but I just can't seem to get the max duration. Any hints would be much appreciated. Mike. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with phone list.
On 8/15/07, Mike Diehl <[EMAIL PROTECTED]> wrote: > Any hints would be much appreciated. DDL + sample data, please... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem with phone list.
--- Mike Diehl <[EMAIL PROTECTED]> wrote: > I've qot a problem I need to solve. I'm sure it's pretty simple; I just > can't > seem to get it, so here goes... > > I've got a table, actually a view that joins 3 tables, that contains a phone > number, a unique id, and a call duration. > > The phone number has duplicates in it but the unique id is unique. > > I need to get a list of distinct phone numbers and the coorisponding largest > call duration. > > I've got the idea that this should be a self-join on phone number where > a.id<>b.id, but I just can't seem to get the max duration. SELECT phone_number, max( duration ) as max_duration FROM your_view GROUP BY phone_number; if you need the unique Id also, SELECT DISTINCT ON ( phone_number ) id, phone_number, duration FROM your_view ORDER BY duration desc; or SELECT V1.id, V1.phone_number, V1.duration FROM your_view AS V1 INNER JOIN ( SELECT phone_number, max( duration ) FROM your_view GROUP BY phone_number ) AS V2( phone_number, duration ) ON (V1.phone_number, V1.duration) = (V2.phone_number, V2.duration); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with phone list.
On Aug 15, 2007, at 15:28 , Mike Diehl wrote: I've got a table, actually a view that joins 3 tables, that contains a phone number, a unique id, and a call duration. The phone number has duplicates in it but the unique id is unique. I need to get a list of distinct phone numbers and the coorisponding largest call duration. If you don't need the id, the simplest thing to do is just SELECT phone_number, max(call_duration) FROM calls GROUP BY phone_number; However, I assume you want the id as well. My first thought is to use PostgreSQL's DISTINCT ON (if you don't mind using non-SQL-standard syntax): SELECT DISTINCT ON (phone_number) phone_number, call_duration, id FROM calls ORDER BY phone_number , call_duration DESC; Another way is to figure out the maximum duration for each phone number and join this back to the full list. SELECT id, phone_number, call_duration FROM calls NATURAL JOIN ( SELECT phone_number, max(call_duration) as call_duration FROM calls GROUP BY phone_number ) max_call_durations_per_number; Two caveats: this either potentially returns more than one id per phone number (if more than one call with the same phone number has the same duration, which is also the max). If you add a DISTINCT (and ORDER BY) to the subquery, you could get distinct numbers, but potentially miss information. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Boolean without default declared
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > --- Jon Collette <[EMAIL PROTECTED]> wrote: >> Thanks that was it exactly. I did notice another strange thing. When >> using IS NULL in an plpgsql IF statement you have to use ISNULL. Same >> with NOT NULL. Does this mean that ISNULL is more proper than IS NULL? IS NULL is in the SQL standard, the other is not; and I dunno what you did wrong but you certainly don't "have to" use ISNULL in plpgsql. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with phone list.
Yup, that did it. I don't know why I made it harder than it had to be. Thank you. Mike. On Wednesday 15 August 2007 02:58:22 pm Fernando Hevia wrote: > Try this: > > Select * > from view v1 > where duration = (select max(duration) from view v2 where v2.phone_number = > v1.phone_number) > > You could get more than one call listed for the same number if many calls > match max(duration) for that number. > > > -Mensaje original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > En nombre de Mike Diehl > Enviado el: Miércoles, 15 de Agosto de 2007 17:28 > Para: SQL Postgresql List > Asunto: [SQL] Problem with phone list. > > Hi all. > > I've qot a problem I need to solve. I'm sure it's pretty simple; I just > can't > seem to get it, so here goes... > > I've got a table, actually a view that joins 3 tables, that contains a > phone > > number, a unique id, and a call duration. > > The phone number has duplicates in it but the unique id is unique. > > I need to get a list of distinct phone numbers and the coorisponding > largest > > call duration. > > I've got the idea that this should be a self-join on phone number where > a.id<>b.id, but I just can't seem to get the max duration. > > Any hints would be much appreciated. > > Mike. > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Mike Diehl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
