[SQL] unsubscribe
[SQL] unsubscribe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] SQL Newbie
It seems to me that I should be able to do this, but after 5 hrs of trying, I can't figure this one out. I could do this in two queries, but seems like I should be able to do this in one. What I am trying to do: Find the highest speed at which each interface of a router has run over time. I have three tables, two of which (interface, speed_history) are being used in this query (primary / foreign key fields noted as PK / FK): router-> 1:M -> interface -> 1:M -> speed_history --- --- -- - router_no (int2) PK interface_id (int4) PK interface_id (int4) PK name (varchar) router_no (int2) FK updated_time (timestamp) PK link_description (varchar) speed(int4) Data in speed history looks like this: interface_id updated_time speed 1 2005-08-11 08:10:23 450112 1 2005-08-11 10:53:34 501120 <--- 1 2005-08-11 10:58:11 450112 2 2005-08-11 08:10:23 450112 <--- 2 2005-08-11 11:00:44 350234 3 2005-08-11 08:10:23 450112 <--- The rows of speed_history I want back are marked above with ' <--- '. Query results should look like: interface.interface_id interface.link_description speed_history.updated_time speed_history.speed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] catch an 'update where false' ?
hello I' trying to catch un update that fails to update because no row matchs the where clause: for instance: update t set c=1 where cc=2; --and no row with cc=2 I would like to perform a insert into t (c) values 1; before the update is resolved I hoped a trigger before update could help me, but it seems that if where clause evals to false, triggers are not called.. someone knows a way around this ? thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] catch an 'update where false' ?
On Fri, Aug 12, 2005 at 05:13:24PM +0200, santiago wrote: > hello > > I' trying to catch un update that fails to update because no row matchs > the where clause: > > for instance: > update t set c=1 where cc=2; > --and no row with cc=2 > > I would like to perform a > insert into t (c) values 1; > before the update is resolved > > I hoped a trigger before update could help me, but it seems > that if where clause evals to false, triggers are not called.. Triggers FOR EACH ROW are called once for each updated row. I think you could try with a "FOR EACH STATEMENT" trigger. -- Alvaro Herrera () "There is evil in the world. There are dark, awful things. Occasionally, we get a glimpse of them. But there are dark corners; horrors almost impossible to imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Encrypting in Postgresql-8.0
Hi, I'm running Postgresql-8.0 for the database, using cygwin and PostgreSQL 7.4.5 compiled lib. When I tried to encrypt some data by calling encrypt() function, I'm getting blank returns to me from the encrypt function. I'm not sure I'm using the encrypt() function incorrectly or is it PostgreSql 7.4.5 lib not compatible with Postgresql-8.0. I tried to compile Postgresql-8.0.3 in cygwin, but it failed. The error I was getting is: dlltool --dllname postgres.exe --def postgres.def --output-lib libpostgres.adlltool: bfd_open failed open stub file: dakes03194.omake[2]: *** [libpostgres.a] Error 1make[2]: *** Deleting file `libpostgres.a'make[2]: Leaving directory `/usr/local/src/postgresql-8.0.3/src/backend'make[1]: *** [all] Error 2make[1]: Leaving directory `/usr/local/src/postgresql-8.0.3/src'make: *** [all] Error 2 I really think I'm not using the encrypt function correctly. Can some one some me an example on how to encrypt data before insert it into the database and decrypt it when retreive from the database? Thank you Start your day with Yahoo! - make it your home page
Re: [SQL] SQL Newbie
On 8/12/05 11:09 AM, "Lane Van Ingen" <[EMAIL PROTECTED]> wrote: > It seems to me that I should be able to do this, but after 5 hrs of trying, > I > can't figure this one out. > > I could do this in two queries, but seems like I should be able to do this > in > one. What I am trying to do: > Find the highest speed at which each interface of a router has run over > time. > > I have three tables, two of which (interface, speed_history) are being used > in > this query (primary / foreign key fields noted as PK / FK): > > router-> 1:M -> interface -> 1:M -> speed_history > --- --- -- > - > router_no (int2) PK interface_id (int4) PK interface_id (int4) PK > name (varchar) router_no (int2) FK updated_time (timestamp) > PK > link_description (varchar) speed(int4) > > Data in speed history looks like this: > interface_id updated_time speed > 1 2005-08-11 08:10:23 450112 > 1 2005-08-11 10:53:34 501120 <--- > 1 2005-08-11 10:58:11 450112 > 2 2005-08-11 08:10:23 450112 <--- > 2 2005-08-11 11:00:44 350234 > 3 2005-08-11 08:10:23 450112 <--- > The rows of speed_history I want back are marked above with ' <--- '. > > Query results should look like: >interface.interface_id >interface.link_description >speed_history.updated_time >speed_history.speed What about (untested): SELECT a.interface_id, a.link_description, c.updated_time, c.speed FROM interface a, (select interface_id,max(speed) as speed from speed_history,interface group by interface_id) as b, speed_history c WHERE b.interface_id=a.interface_id AND c.speed=b.speed; Sean ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Left join pa on dal resulting in null
Greetings, I have the following join, and in this join I am getting all of the data except that which joins the pa table to the dao table. When I try to join it using a left join, i get all of the data except this table. When I use a right join, I get nothing at all. If I make this a seperate join to dal (rather than nested as it is) I have no connection/path to the dao table because of the way that the postgres optimizer works, and I have to use another alias, and I THINK that the alias was causing other problems, but I'm not certain at the moment. Can someone lead me to the the best way to tackle this problem? The query currently looks like this: SELECT -- a bunch of columns here dr.profile_id FROM dr, da, dal LEFT JOIN ( doo LEFT JOIN ( dao LEFT JOIN pa ON ( pa.dao_id = dao.dao_id )) -- the problem area is here. - - There are cases where there are no related doo values set but - - I'm not sure how to write this better ON ( doo.dao_id = dao.dao_id OR pa.dal_id = doo.dal_id )) ON ( dal.dal_id = doo.dal_id OR dal.dal_id = pa.dal_id ) WHERE dr.dr_id = doo.dr_id AND dr.dr_id = '1' AND da.da_id = dal.da_id ORDER BY da.name Thank you in advance Ilene ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SQL Newbie
Hope this helps SELECT * FROM speed_history as outside etc.. WHERE (speed = ( SELECT speed FROM speed_history as inside etc.. WHERE (outside.interface = inside.interface) LIMIT 1 ORDER BY speed DESC ) ) Hopefully you get the idea - basically it's a corelated sub-query - very useful Nick -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van Ingen Sent: 12 August 2005 16:09 To: pgsql-sql@postgresql.org Subject: [SQL] SQL Newbie It seems to me that I should be able to do this, but after 5 hrs of trying, I can't figure this one out. I could do this in two queries, but seems like I should be able to do this in one. What I am trying to do: Find the highest speed at which each interface of a router has run over time. I have three tables, two of which (interface, speed_history) are being used in this query (primary / foreign key fields noted as PK / FK): router-> 1:M -> interface -> 1:M -> speed_history --- --- -- - router_no (int2) PK interface_id (int4) PK interface_id (int4) PK name (varchar) router_no (int2) FK updated_time (timestamp) PK link_description (varchar) speed(int4) Data in speed history looks like this: interface_id updated_time speed 1 2005-08-11 08:10:23 450112 1 2005-08-11 10:53:34 501120 <--- 1 2005-08-11 10:58:11 450112 2 2005-08-11 08:10:23 450112 <--- 2 2005-08-11 11:00:44 350234 3 2005-08-11 08:10:23 450112 <--- The rows of speed_history I want back are marked above with ' <--- '. Query results should look like: interface.interface_id interface.link_description speed_history.updated_time speed_history.speed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL Newbie
> Data in speed history looks like this: > interface_id updated_time speed > 1 2005-08-11 08:10:23 450112 > 1 2005-08-11 10:53:34 501120 <--- > 1 2005-08-11 10:58:11 450112 > 2 2005-08-11 08:10:23 450112 <--- > 2 2005-08-11 11:00:44 350234 > 3 2005-08-11 08:10:23 450112 <--- > The rows of speed_history I want back are marked above with ' <--- '. > > Query results should look like: > interface.interface_id > interface.link_description > speed_history.updated_time > speed_history.speed The main idea is to join a select of max speeds grouped by interface_id to the interface table. If the query runs too slow, then you may get better performance having an index on (interface_id, speed) and using subselects. In this case you want to select information about all of interfaces and then have one of the columns be a subselect that selects one (using limit) speed from rows that have a matching interface_id ordered by interface_id desc, speed desc. This combination of limit and order by will be faster than using max. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL Newbie
Nick Stone wrote: > Hope this helps > > SELECT > * > FROM > speed_history as outside etc.. > WHERE > (speed = ( > SELECT > speed > FROM > speed_history as inside etc.. > WHERE > (outside.interface = inside.interface) > LIMIT 1 > ORDER BY > speed DESC > ) > ) Worth noting that ORDER BY speed DESC LIMIT 1 is fundamentally the same as MAX (speed), except that MAX (speed) doesn't take advantage of any index present. This is a fairly common idiom but a self-described SQL Newbie may not see the equivalence instantly. -Owen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SQL Newbie
On Fri, Aug 12, 2005 at 18:57:34 +0100, Nick Stone <[EMAIL PROTECTED]> wrote: > Hope this helps > > SELECT > * > FROM > speed_history as outside etc.. > WHERE > (speed = ( > SELECT > speed > FROM > speed_history as inside etc.. > WHERE > (outside.interface = inside.interface) > LIMIT 1 > ORDER BY > speed DESC > ) > ) > > Hopefully you get the idea - basically it's a corelated sub-query - very > useful > Note that you want to order by interface DESC, speed DESC in the subselect and have an index on (interface, speed) for this to really be efficient. I think 8.1 might be smart enough to use an index on (interface, speed) in the above case, but currently released versions won't. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Fwd: How to encrypt a column
Please keep replies posted to the list unless you have a specific reason
not to.
On Fri, Aug 12, 2005 at 14:56:54 -0700,
Jeff Lu <[EMAIL PROTECTED]> wrote:
> I can't see why it's returning (null).
>
> sprintf(query_string, "INSERT INTO mytable values('%s', '%s')", key, ,
> encrypt('hello world', '', 'aes'));
> PQexec(conn, query_string);
I don't know what your problem is for this example. However there is an extra
comma in what you typed that will cause problems if it is really in your code.
However, your method will not work in general because you can't be sure the
output of the AES encryption will be valid text. You should be recoding to
something safe to represent with ascii (such as base64).
>
> I if do this in cygwin shell, it works
>
> $ psql -h localhost intrapos --username postgres
> Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
> Type: \copyright for distribution terms
>\h for help with SQL commands
>\? for help on internal slash commands
>\g or terminate with semicolon to execute query
>\q to quit
> intrapos=# select encrypt('hello world', '', 'aes') \g
> encrypt
> ---
> \333\337\003\217\016\222WC\243\031\306\250`&\265Q
> (1 row)
> intrapos=#
>
> I really appreciate your help, I could not figure this out
>
> Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Thu, Aug 11, 2005 at 13:50:45 -0700,
> Owen Jacobson wrote:
> > Jeff Lu wrote:
> >
> > > Another question is can the encrypted column be of type "text" ?
> >
> > Can't see any reason why not, so long as the encrypted data is represented
> > as text.
>
> There can't be any 0 bytes in the encrypted string or the string will be
> truncated.
>
> ---(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
>
>
> -
> Start your day with Yahoo! - make it your home page
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
