Re: [SQL] Query Help
"Brian C. Doyle" wrote: > What do I have to do a query where information in table1 is not in table2 > > I am looking for something like > > Select table1.firstname where table1.firstname is not in table2.firstname > and table2.date='yesterday' > > I tried > Select table1.firstname where table1.firstname != table2.firstname and > table2.date='yesterday' > > and that did not work just listed everyone imho the most natrural way for what you need seems to be : select whatyouwant from table1 where not exists select * from table2 where table2.firstname = table1.firstname and table2.date='yesterday'; hoping that helps P. Jacquot
[SQL] Strange Execution-Plan for NOT EXISTS
Hello all, I tried (just for academical fun) to resolve ID´s which are not used anymore, e.g.: ID NAME -- + -- 1 | NOBODY 2 | ANYBODY 4 | EVERYBODY Now I want to get "3" as the next usable ID. I really know that this is kind of bad style but a friend of mine asked me if I had a solution for this problem. My solution would be the following statement: select (min(id) + 1) from t_dummy d1 where not exists ( select id from t_dummy d2 where d2.id = (d1.id + 1) ); The explain plan for this statement, tested against a real table with about 8,000 records in a freshly vacuumed database, looks like the following: Aggregate (cost=2924207.88..2924207.88 rows=1 width=12) -> Seq Scan on t_dummy d1 (cost=0.00..2924207.88 rows=1 width=12) SubPlan -> Seq Scan on t_dummy d2 (cost=0.00..331.36 rows=1 width=12 ) Why that? Wouldn´t it be possible to simple use the primary key index in the sub-query and exit from the outer query at the first occurence of an ID which has no following entry? Any ideas from anyone? Am I wrong? Best regards, Jens = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
Re: [SQL] Query Help
Thank you to everyone with their suggestions. Where on the PostgreSQL site would I have found more info on the NOT EXISTS At 11:20 AM 12/27/00 -0500, you wrote: >What do I have to do a query where information in table1 is not in table2 > >I am looking for something like > >Select table1.firstname where table1.firstname is not in table2.firstname >and table2.date='yesterday' > >I tried >Select table1.firstname where table1.firstname != table2.firstname and >table2.date='yesterday' > >and that did not work just listed everyone >
Re: [SQL] How to represent a tree-structure in a relational database
Ron Peterson wrote: > > This structure is more 'normal' in the sense that nodes without children > (in a tree, the leaf nodes) don't have records in the edge table. Phghpth. Should have had my coffee first. The first data structure given would only have a null parent id for the root node, not all the leaf nodes. My mistake. Thought it might be politic to point that out before someone (correctly) called me an idiot. -Ron-
[SQL] 7.1 feature?
I need to do something like this: SELECT n.name FROM (SELECT p.contact_seq AS contact_seq, p.lastname||', '||p.firstname AS name FROM person p UNION SELECT co.name AS name FROM company co) n; Of course there's more; this is cut from the original select. But PostgreSQL complains about SELECT (i guess it's the SELECT in the subquery) In the dox I read: A FROM item can also be a parenthesized sub-SELECT (note that an alias clause is required for a sub-SELECT!). This is an extremely handy feature since it's the only way to get multiple levels of grouping, aggregation, or sorting in a single query. Is this a 7.1 feature? Or is my typing wrong? And will 7.1 support unions in subselects like above? -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 11.00-17.00 Web: www.suse.dk
Re: [SQL] 7.1 feature?
Kaare Rasmussen <[EMAIL PROTECTED]> writes: > In the dox I read: You're reading the 7.1 docs. 7.0 docs live at http://www.postgresql.org/users-lounge/docs/7.0/postgres/postgres.htm regards, tom lane
[SQL] rserv
I'm getting ready to spend some time testing out the new replication server. The documentation is sparse, so I'm wondering if there are any known bugs? Also, is there a specific forum/list for discussion of the server? Thanks ... Bryan Ingram
[SQL] MD5 use in PL/Perl
I'd like to be able to only store the database of usernames and passwrods
here locally as a md5 hash. (in case the black hats come to visitI'd
like to make life hard for them) Using AuthPG, I should be able to create
a SQL call to postgresbut there is no native md5 hashing function.
In my ideal blue-sky worldthe SQL call would like this:
SELECT name FROM Sample_table WHERE ( (userid='12345') AND
(userhashed=md5out('abc')) )
With the sample table looks like this:
Sample_table:
nameuseriduserhashed
fred12345 900150983cd24fb0d6963f7d28e17f72
I'd get the string 'fred' in name from Sample_table.
Idea 1) A call to a shell script.A question was asked back in 1999 if
there was a way to use a shell script in an SQL call.that person had
no public responses. Moved onto
Idea 2) use PL/Perl to take in the text to be hashed, and output the
hash. Read the docs, looked on the list for more examples..
This perl code works as I'm expecting.
use MD5;
my $mdval = new MD5;
my $result ;
my $out;
$mdval->add('abc');
$result = $mdval->digest();
$out= unpack("H*" , $result );
print $out;
Attempting to xlate to PL/Perl
settle=# create function md5out3(varchar) returns varchar(32) as '
settle'# use MD5;
settle'# my $mdval = new MD5;
settle'# my $result ;
settle'# my $out;
settle'# $mdval->add($_[0]);
settle'# $result = $mdval->digest();
settle'# $out= unpack("H*" , $result );
settle'# return $out;'
settle-# LANGUAGE 'plperl';
CREATE
settle=# select md5out3('fred');
ERROR: creation of function failed : require trapped by operation mask at
(eval 6) line 2.
So...
What did I do wrong WRT PL/Perl? (Let me guesshaving perl call perl
modules causes breakage) Should I be trying something different
to get to my desired end goal?
Re: [SQL] MD5 use in PL/Perl
Marc Rassbach writes:
> Attempting to xlate to PL/Perl
>
> settle=# create function md5out3(varchar) returns varchar(32) as '
> settle'# use MD5;
> settle'# my $mdval = new MD5;
> settle'# my $result ;
> settle'# my $out;
> settle'# $mdval->add($_[0]);
> settle'# $result = $mdval->digest();
> settle'# $out= unpack("H*" , $result );
> settle'# return $out;'
> settle-# LANGUAGE 'plperl';
> CREATE
> settle=# select md5out3('fred');
> ERROR: creation of function failed : require trapped by operation mask at
> (eval 6) line 2.
You can't use external modules ("use", "require") for security reasons.
FWIW, if I were to write an MD5 function then I'd take one of the
implementations floating around (mhash, Kerberos, OpenSSL, RFC) and make a
C function wrapper around it.
Incidentally, someone has already done this for the upcoming 7.1 release,
but since the function call interface has changed the back port won't be
trivial.
--
Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] How to trim values?
Hi, I'm trying to figure out how to take a value like 3.68009074974387 (that is calculated from values in my database) and have PostgreSQL hand me 3.68. Any suggestions would be appreciated. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6999 North Vancouver, BC, V7M 2J5
[SQL] Simultaneous Connection Problem
I have a table that is sequence_number (my PK), session_start_time, and session_stop_stime. I would like to query it to determine the max number of simultaneous sessions. Has anyone conquered a problem like this? It seems like it should be in a book somewhere, but I haven't found it yet. Thanks, -- Webb Sprague Programmer O1 Communications
Re: [SQL] How to trim values?
[EMAIL PROTECTED] wrote: >Hi, > >I'm trying to figure out how to take a value like 3.68009074974387 >(that is calculated from values in my database) and have PostgreSQL >hand me 3.68. Any suggestions would be appreciated. cast it to numeric(x,2) (where x is the total number of digits, and 2 is two decimal places). template1=# select 3.68009074974387::numeric(3,2); ?column? -- 3.68 (1 row) or use round(value,2) template1=# select round(3.68009074974387, 2); round --- 3.68 (1 row) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For God shall bring every work into judgment, with every secret thing, whether it be good, or whether it be evil." Ecclesiastes 12:14
[SQL] Looking for comments
Rather thant making long sentences & comment. Anyone willing to give me a little help on this tables definition is welcome http://lautre.org/tsmets/DB.html Tomorrow the pageswill be colorized a little bit more & a drawing of the expected tables will be provided. Tx, Thomas, -- Thu Dec 28 23:51:18 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = The story of the butterfly: "I was in Bogota and waiting for a lady friend. I was in love, a long time ago. I waited three days. I was hungry but could not go out for food, lest she come and I not be there to greet her. Then, on the third day, I heard a knock." "I hurried along the old passage and there, in the sunlight, there was nothing." "Just," Vance Joy said, "a butterfly, flying away." -- Peter Carey, BLISS = End of Quote ===
