[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "reverse()" on strings
Jeff & Josh, thanks for showing me a solution ! John PS: just curious: is there anything I can do to affect effectiveness of the cache, when ISCACHABLE is used ? (I.e. size / expiring algorithm, or order of inputs - E.g. when building the index, is there anything that would order the inputs first, to maximize cache hit/miss ratio, such as "CREATE INDEX extension_idx ON file (reverse(name)) ORDER BY name" ? On Mon, 26 Aug 2002, Josh Berkus wrote: > Jeff, h012, > > > CREATE FUNCTION fn_strrev(text) returns text as ' > > return reverse($_[0]) > > ' language 'plperl' with (iscachable); > > If you make that "WITH (ISCACHABLE, ISSTRICT)" the index will be faster to > update on columns which contain large numbers of NULLs. "ISSTRICT" refers to > the fact that if the function receives a NULL, it will output a NULL, and > thus saves the parser the time running NULLs through the function. > > Also, remember to use this index, you'll have to call the exact same function > in your queries. > > -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] signed/unsigned integers
Hi Is it true, that I can't define unsigned integers in a table definition? CU Roger ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] signed/unsigned integers
> Is it true, that I can't define unsigned integers in a table definition? > > CU Roger I'm not aware of any unsigned types in Postgres. You could probably define your own one though. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] triggers and plpgsql question
--On mercredi 28 août 2002 08:42 +0200 Mathieu Arnold <[EMAIL PROTECTED]> wrote: > > > --On mardi 27 août 2002 15:38 -0700 Josh Berkus <[EMAIL PROTECTED]> wrote: > >> >> Mathieu, >> >>> The thing I need, is to be able to know what does NEW contains, and I >>> have not found out any mean to do so. If it's not possible to do so, >>> I'll write a function per table, but for the beauty of all this, I would >>> have liked to do it the way above. >> >> You can't do this in PL/pgSQL. See the online documentation on writing >> triggers in C; that is the only way to get what you want. > > So, if I want to avoid C, I'll have to write a function per table. I'll > have a look at SPI (as I believe after a short readout of the doc, I'll > need it). After a few hours of work, here is what I did. What it does is to log everything that gets inserted, deleted or updated into a table. I post it here because I believe that someone else might be interested. -- Mathieu Arnold triggers.c Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Separating data sets in a table
"Andreas Tille" <[EMAIL PROTECTED]> a écrit dans le message de news: [EMAIL PROTECTED] ... > I tried to do the following approach: > >CREATE LOCAL TEMPORARY TABLE ImportOK () INHERITS (Import) ; > >INSERT INTO ImportOK SELECT * FROM Import i > INNER JOIN Ref r ON i.Id = r.Id; > >DELETE FROM Import WHERE Id IN (SELECT Id FROM ImportOK) ; ... > Unfortunately the latest statement is so terribly slow that I can't > imagine that there is a better way to do this. > You must use EXISTS if you work with big tables. EXISTS use indexes, and IN use temporary tables. -- this change nothing for IN : CREATE INDEX import_id_index ON import(id); CREATE INDEX import_ok_id_index ON import_ok(id); -- slow : -- DELETE FROM import WHERE id IN (SELECT id FROM import_ok) ; DELETE FROM import WHERE EXISTS ( SELECT id FROM import_ok AS ok WHERE ok.id = import.id ); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problems with version 7.1, could they be fixed in 7.2?
I've been having problems with a database in postgres 7.1, this database worked just fine for about 6 months, but it is a fast growing database (a lot of records inserted every day, almost none deleted). The problem is that the database started corrupting indexes, and tables (even system tables, like pg_class). I posted my questions previously with the specific messages, but sadly I got no reply :(... I recovered a backup and installed it on another computer to verify that it was not a hardware problem, but I kept having crashes every week. Last week I decided to install a new computer with postgres 7.2 and I loaded the database on this version. So far, no problem... I would like to know if I could expect this problems to be fixed, because I read on the documentation that version 7.2 was optimized for large databases, or should I check something else. Thank you. Ligia ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SELECT ... WHERE ... NOT IN (SELECT ...);
I use a 7.3devel recently build (on a FreeBSD 4.3 box) from CVS sources. I follow all your steps, and psql results : test_db=> \! cat problem.sql --create new temp tbl1 SELECT losteventid AS eventid INTO tbl1 FROM outages; --create new temp tbl2 SELECT regainedeventid AS eventid INTO tbl2 FROM outages; SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); test_db=> \i problem.sql SELECT SELECT eventid - 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083 (10 rows) "Yon Den Baguse Ngarso" <[EMAIL PROTECTED]> a écrit dans le message de news: [EMAIL PROTECTED] > Oops correction. > ... cut > > myhost=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); > eventid > - > (0 rows) > > TIA, > Yon > ---(end of broadcast)--- TIP 3: 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
[SQL] Year of the week : How to ?
How to retrieve the year based on week of a date ?
If I use datepart('year', aDate), I get the year of the date.
If I use datepart('week', aDate), I get the week of the date.
But these aren't corelated. So when aDate is Dec 31st 2001, I get 2001, and
the week is 1. I'd like to have 2002 for the year in this case.
Is there a simple way to do this ?
Emmanuel Guyot
8 Rue des Montées
45100 Orléans
Web site : http://www.increg.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Problems with version 7.1, could they be fixed in 7.2?
Hi Ligia, Are you running VACUUM ANALYSE or is it VACUUM ANALYZE (can never remember, though reasonably sure that its the former). Regards Mark Carew Brisbane Australia ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Can I use "UPDATE" sql statement in trigger before or after update ?
Can I use "UPDATE" sql statement in trigger before or after update, without refire the trigger again ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] SERIAL parameters
Would it be possible to have parameters in the SERIAL datatype?
CREATE TABLE x { ID SERIAL(100, 10) }
means ID starts at 100 and will be incremented by 10. And it would be
nice to have
CREATE TABLE x { ID SERIAL(0,0) }
mean that ID will be issued random non-repeating values (period=2^bitwidth
of type). This is most likely useful in replication/merging data.
---(end of broadcast)---
TIP 3: 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
[SQL] Help needed
Dear postgresql, I am just starting to learn SQL. I have attached a script that allows me to create some tables and indexes. I have rows that are inserted into the tables. I need some help with creating multiple subqueries and table joins, (six or seven table joins). Can you help? Paul Mowat Software QA Engineer Enterprise Data Management --- BMCSoftware Abingdon UK Direct: +44 1235 827408 Fax: +44 1235 827430 Email: [EMAIL PROTECTED] www.bmc.com BaseSetofObjects.sql Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] VIRUS IN MAIL FOR YOU FROM
A virus-infected attachment in a message sent to you from <[EMAIL PROTECTED] > was detected by the campus central mail servers. Delivery of that message has been blocked. If you know the sender, you may want to contact the sender and have the message re-sent after cleaning the virus. If you have questions, please contact the HelpDesk. Details: W32/Klez.h@MM - BEGIN HEADERS - From: roessler <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=OOf4GOs69b3 -- END HEADERS -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems with version 7.1, could they be fixed in 7.2?
On Mon, 26 Aug 2002, Ligia Pimentel wrote: > I've been having problems with a database in postgres 7.1, this database > worked just fine for about 6 months, but it is a fast growing database (a > lot of records inserted every day, almost none deleted). > > The problem is that the database started corrupting indexes, and tables > (even system tables, like pg_class). I posted my questions previously with > the specific messages, but sadly I got no reply :(... > > I recovered a backup and installed it on another computer to verify that it > was not a hardware problem, but I kept having crashes every week. > > Last week I decided to install a new computer with postgres 7.2 and I loaded > the database on this version. So far, no problem... > > I would like to know if I could expect this problems to be fixed, because I > read on the documentation that version 7.2 was optimized for large > databases, or should I check something else. Possibly, there were a bunch of bugs that were fixed between the two versions. There have still been reports of corruption on 7.2, but almost all of those have been traced to hardware problems. As a note, you probably want to run 7.2.2 if you're not already. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SERIAL parameters
You can create the sequence manually and define that a the default for
the column. I suppose we could pass the create sequence params through
SERIAL but there doesn't seem like there is much demand for it.
---
Arnold Putong wrote:
> Would it be possible to have parameters in the SERIAL datatype?
>
> CREATE TABLE x { ID SERIAL(100, 10) }
>
> means ID starts at 100 and will be incremented by 10. And it would be
> nice to have
>
> CREATE TABLE x { ID SERIAL(0,0) }
>
> mean that ID will be issued random non-repeating values (period=2^bitwidth
> of type). This is most likely useful in replication/merging data.
>
>
>
> ---(end of broadcast)---
> TIP 3: 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
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: BM> OK, no one has commented on this, so I guess I am going to have to guess BM> the group's preference. BM> My guess, seeing as very few probably use LIMIT and FOR UPDATE together, BM> is to swap them and document it in the release notes. Was I correct in BM> my guess? My preference is to allow both orders for one release, then only allow the "correct" order in the next. be sure to absolutely make this a big red notice in the changelog. I just scanned my main app and found two instances where I use FOR UPDATE LIMIT 1. These are trivial to change, but difficult to do at the same moment I update the db server. One of these I probably don't even need the LIMIT... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problems with version 7.1, could they be fixed in 7.2?
Ok. Got It. I thought that would help, also. Thanks. Ligia "Stephan Szabo" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > On Mon, 26 Aug 2002, Ligia Pimentel wrote: > > > I've been having problems with a database in postgres 7.1, this database > > worked just fine for about 6 months, but it is a fast growing database (a > > lot of records inserted every day, almost none deleted). > > > > The problem is that the database started corrupting indexes, and tables > > (even system tables, like pg_class). I posted my questions previously with > > the specific messages, but sadly I got no reply :(... > > > > I recovered a backup and installed it on another computer to verify that it > > was not a hardware problem, but I kept having crashes every week. > > > > Last week I decided to install a new computer with postgres 7.2 and I loaded > > the database on this version. So far, no problem... > > > > I would like to know if I could expect this problems to be fixed, because I > > read on the documentation that version 7.2 was optimized for large > > databases, or should I check something else. > > Possibly, there were a bunch of bugs that were fixed between the two > versions. There have still been reports of corruption on 7.2, but almost > all of those have been traced to hardware problems. As a note, you > probably want to run 7.2.2 if you're not already. > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] UPDATE & LIMIT together?
Hi I want to SELECT at max. 10 rows and SET a variable for the select 10 rows with the same query. Under mysql i can use: UPDATE table SET uniq_iq=12345 LIMIT 10 SELECT * FROM table WHERE uniq_id=1234; This is not supported by postgres. Is there some easy solution that does not require locking? -tp msg07235/pgp0.pgp Description: PGP signature
Re: [SQL] Retrieving the new "nextval" for primary keys....
Greg Patnude wrote:
> I am using postgreSQL with Perl::CGI and Perl::DBI::Pg... I would like to be
> able to insert a row from my Perl script [$SQL->exec();] and have postgreSQL
> return the id of the newly inserted record (new.id) directly to the Perl
> script for further processing... Anyone with a solution / idea ???
>
> Nearly EVERY table I create in postgreSQL (7.2) has the following minimum
> structure:
>
> create table "tblName" (
>
> id int4 primary key nextval ("tblName_id_seq"),
>
> ..field...
> )
You can either do it in 2 statements, something like:
$dbh->do("insert into tblName ...");
my ($id) = $dbh->selectrow_array("select currval('tblName_id_seq')");
Or you could create a function which takes the insert statement, and
ends with doing a select on the currval (as above) and returning that.
As I do the 2 statement approach above, I haven't done a function, but
it doesn't look like it would be that hard to do.
HTH,
Kevin
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Retrieving the new nextval...
Hi all,
I'm in trouble with the same problem, but in PHP..
With your solution, I cannot be totally sure that last
inserted raw was mine...
Because I'm on a web page, it could be that, as soon
as I've inserted my record, another one do an
insertion, so I would get the wrong ID...
does transactions resolve this, in Psql???
I thought to solve it with a similiar solution,
working in transactions inserting a raw and
immedialtly after read from DB last raw, but who
assure me that all will go right??
If I was on a server app., I (and you, if it is your
case) would insert a timestamp, and then I'd select
from table where timestamp = mysavedtime;
But in my case there could be two or more equals
timestamp, cause there's not only one application
working with DB...
I'm still reading, searching, trying...
ciao
danilo
--- Kevin Brannen <[EMAIL PROTECTED]> ha
scritto: > Greg Patnude wrote:
> > I am using postgreSQL with Perl::CGI and
> Perl::DBI::Pg... I would like to be
> > able to insert a row from my Perl script
> [$SQL->exec();] and have postgreSQL
> > return the id of the newly inserted record
> (new.id) directly to the Perl
> > script for further processing... Anyone with a
> solution / idea ???
> >
> > Nearly EVERY table I create in postgreSQL (7.2)
> has the following minimum
> > structure:
> >
> > create table "tblName" (
> >
> > id int4 primary key nextval
> ("tblName_id_seq"),
> >
> > ..field...
> > )
>
> You can either do it in 2 statements, something
> like:
>
> $dbh->do("insert into tblName ...");
> my ($id) = $dbh->selectrow_array("select
> currval('tblName_id_seq')");
>
> Or you could create a function which takes the
> insert statement, and
> ends with doing a select on the currval (as above)
> and returning that.
> As I do the 2 statement approach above, I haven't
> done a function, but
> it doesn't look like it would be that hard to do.
>
> HTH,
> Kevin
R
__
Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali
http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Retrieving the new "nextval" for primary keys....
One solution seems to locking table(s), but I prefer to leave it as last chance... using table locks, and the trick of writing and suddenly reading back from DB it probably works, but it doesn't seems so sexy... :) ciao danilo __ Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/ ---(end of broadcast)--- TIP 3: 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
[SQL] nextval...
Ok , now I believe it :) thanks to all. danilo __ Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] UPDATE & LIMIT together?
You have to use a subquery returning the tables primary key to the UPDATE: UPDATE tab SET x=1 WHERE (primkey, col) IN ( SELECT primkey,col FROM tab ORDER BY col LIMIT 10) --- tp wrote: -- Start of PGP signed section. > Hi > > I want to SELECT at max. 10 rows and SET a variable for the > select 10 rows with the same query. > > Under mysql i can use: > UPDATE table SET uniq_iq=12345 LIMIT 10 > SELECT * FROM table WHERE uniq_id=1234; > > This is not supported by postgres. > > Is there some easy solution that does not require locking? > > > -tp -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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
[SQL] Case Statement
Hi Folks, I am having troubles with a case statement in that I want to have the query select only those records that match a particular case. Here's my query: SELECT agency_contact_info.id,organization,department,city,state,description_of_ser vices, CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END + CASE WHEN agency_contact_info.languages_other_text ~ 'Mien' THEN 1 ELSE 0 END AS relevance FROM agency_contact_info WHERE (agency_contact_info.guideregion=1 AND list_online IS TRUE AND (agency_contact_info.id > 0 OR agency_contact_info.languages_other_text ~ 'Mien' )) ORDER BY relevance DESC, agency_contact_info.organization How do I add in the fact that I only want records where the CASE (as relevance) > 0? I've tried using it in the WHERE statement adding a HAVING statement and it doesn't like either. You will see a fair amount of redundancy in the statement above such as "CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END" and "agency_contact_info.id > 0" - this is because it is being built dynamically, and it makes it easier to build the addition blocks of the statement. Thanks in advance, Tom ___ Tom Haddon IT Director The Better Health Foundation 414 Thirteenth Street, Suite 450 Oakland, CA 94612 (510) 444-5096 www.betterhealthfoundation.org ___ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Case Statement
On Wed, 28 Aug 2002, Tom Haddon wrote: > Hi Folks, > > I am having troubles with a case statement in that I want to have the query > select only those records that match a particular case. Here's my query: > > SELECT > agency_contact_info.id,organization,department,city,state,description_of_ser > vices, CASE WHEN agency_contact_info.id > 0 THEN 0 ELSE 0 END + CASE WHEN > agency_contact_info.languages_other_text ~ 'Mien' THEN 1 ELSE 0 END AS > relevance > FROM agency_contact_info WHERE (agency_contact_info.guideregion=1 AND > list_online IS TRUE AND (agency_contact_info.id > 0 OR > agency_contact_info.languages_other_text ~ 'Mien' )) > ORDER BY relevance DESC, agency_contact_info.organization > > How do I add in the fact that I only want records where the CASE (as > relevance) > 0? I've tried using it in the WHERE statement adding a HAVING > statement and it doesn't like either. You will see a fair amount of I think you'll either need to duplicate the case statement or hide it in a subselect with the relevance check on the outer query (like select * from (select ... ) as a where relevance>0. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Retrieving the new nextval...
--- friedrich nietzsche <[EMAIL PROTECTED]>
wrote:
> Hi all,
> I'm in trouble with the same problem, but in PHP..
> With your solution, I cannot be totally sure that
> last
> inserted raw was mine...
> Because I'm on a web page, it could be that, as soon
> as I've inserted my record, another one do an
> insertion, so I would get the wrong ID...
"currval" will return the last value used _for the
current connection_. But if you want to be absolutely
sure, instead call "nextval" before doing your insert,
and use the returned value explicitly.
> does transactions resolve this, in Psql???
> I thought to solve it with a similiar solution,
> working in transactions inserting a raw and
> immedialtly after read from DB last raw, but who
> assure me that all will go right??
> If I was on a server app., I (and you, if it is your
> case) would insert a timestamp, and then I'd select
> from table where timestamp = mysavedtime;
> But in my case there could be two or more equals
> timestamp, cause there's not only one application
> working with DB...
> I'm still reading, searching, trying...
> ciao
> danilo
>
>
>
> --- Kevin Brannen <[EMAIL PROTECTED]> ha
> scritto: > Greg Patnude wrote:
> > > I am using postgreSQL with Perl::CGI and
> > Perl::DBI::Pg... I would like to be
> > > able to insert a row from my Perl script
> > [$SQL->exec();] and have postgreSQL
> > > return the id of the newly inserted record
> > (new.id) directly to the Perl
> > > script for further processing... Anyone with a
> > solution / idea ???
> > >
> > > Nearly EVERY table I create in postgreSQL (7.2)
> > has the following minimum
> > > structure:
> > >
> > > create table "tblName" (
> > >
> > > id int4 primary key nextval
> > ("tblName_id_seq"),
> > >
> > > ..field...
> > > )
> >
> > You can either do it in 2 statements, something
> > like:
> >
> > $dbh->do("insert into tblName ...");
> > my ($id) = $dbh->selectrow_array("select
> > currval('tblName_id_seq')");
> >
> > Or you could create a function which takes the
> > insert statement, and
> > ends with doing a select on the currval (as above)
> > and returning that.
> > As I do the 2 statement approach above, I haven't
> > done a function, but
> > it doesn't look like it would be that hard to do.
> >
> > HTH,
> > Kevin
> R
>
>
__
> Yahoo! Musica: notizie, recensioni, classifiche,
> speciali multimediali
>
http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
>
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
---(end of broadcast)---
TIP 3: 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
[SQL] select question
[postgreql 7.2, linux] I have a table T with columns run, wafer, and test: T(run text, wafer int, test text) Given a run and a set of wafers, I need the set of tests that match *all* the specified wafers: run wafer test a 1 foo a 2 foo a 3 foo a 3 bar E.g. Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches both 1 and 3. Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and bar match 3. Is there some neat way to do this in a single query? Puzzled, George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Detective" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select question
You can use this query SELECT * FROM T WHERE run = 'a' AND wafer = 1 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 2 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 3 AND test = 'foo' UNION SELECT * FROM T WHERE run = 'a' AND wafer = 3 AND test = 'bar' On Wed, 2002-08-28 at 16:12, george young wrote: > [postgreql 7.2, linux] > I have a table T with columns run, wafer, and test: >T(run text, wafer int, test text) > Given a run and a set of wafers, I need the set of tests that match > *all* the specified wafers: > > run wafer test > a 1 foo > a 2 foo > a 3 foo > a 3 bar > > E.g. > Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches >both 1 and 3. > Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and >bar match 3. > > Is there some neat way to do this in a single query? > > Puzzled, > George > > > -- > I cannot think why the whole bed of the ocean is > not one solid mass of oysters, so prolific they seem. Ah, > I am wandering! Strange how the brain controls the brain! > -- Sherlock Holmes in "The Dying Detective" > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] select question
On Wed, 28 Aug 2002 16:12:41 -0400 in message
<[EMAIL PROTECTED]>, george young <[EMAIL PROTECTED]> wrote:
> [postgreql 7.2, linux]
> I have a table T with columns run, wafer, and test:
>T(run text, wafer int, test text)
> Given a run and a set of wafers, I need the set of tests that match
> *all* the specified wafers:
>
> run wafer test
> a 1 foo
> a 2 foo
> a 3 foo
> a 3 bar
>
> E.g.
> Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches
>both 1 and 3.
> Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and
>bar match 3.
>
> Is there some neat way to do this in a single query?
>
select test from T
where run='a'
and wafers in ('1','3')
group by test
eric
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Retrieving the new "nextval" for primary keys....
I am using postgreSQL with Perl::CGI and Perl::DBI::Pg... I would like to be
able to insert a row from my Perl script [$SQL->exec();] and have postgreSQL
return the id of the newly inserted record (new.id) directly to the Perl
script for further processing... Anyone with a solution / idea ???
Nearly EVERY table I create in postgreSQL (7.2) has the following minimum
structure:
create table "tblName" (
id int4 primary key nextval ("tblName_id_seq"),
..field...
..field...
..field...
create_dt date default 'CURRENT_DATE',
change_dt timestamptz default 'now()',
active_flag bool default 'TRUE'
)
---(end of broadcast)---
TIP 3: 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
[SQL] union optimization in views
We are attempting to move a couple of systems from Oracle to Postgres but can not do so without application rewrites due to the current use of views with UNIONs and the criticality of the performances of these views. I was wondering if a decision has been made on the optimization with the UNION clause in views. There are many documents in the SQL archive showing that the "push down" is not occuring and thus the use of UNION's in views is limited to case where the data set is small or performance is not a consideration. I also looked through the TODO list and didn't see anything (of course I could have missed references). thanks - Joe snip of an Article from SQL archives CREATE VIEW two_tables AS SELECT t1.id, t1.name, t1.abbreviation, t1.juris_id FROM t1 UNION ALL SELECT t2.id, t2.name, NULL, t2.juris_id FROM t2;This works fine as a view, since I have made the id's unique between the two tables (using a sequence). However, as t1 has 100,000 records, it isvitally important that queries against this view use an index.As it is a Union view, though, they ignore any indexes: > It's probably not pushing the login='asdadad' condition down into the > queries in the view so it's possibly doing a full union all followed > by the condition (given that it's estimating a larger number of rows > returned). I think there was some question about whether it was safe > to do that optimization (ie, is select * from (a union [all] b) where > condition always the same as> select * from a where condition union [all]> select * from b where condition> )>> There wasn't any final determination --- it's still an open issue > whether there are any limitations the planner would have to consider > when trying to push down conditions into UNIONs. Offhand it seems to > me that the change is always safe when dealing with UNION ALL, but I'm> not quite convinced about UNION. And what of INTERSECT and EXCEPT?>> Another interesting question is whether there are cases where the > planner could legally push down the condition, but should not because > it would end up with a slower plan. I can't think of any examples > offhand, but that doesn't mean there aren't any.
Re: [SQL] SELECT ... WHERE ... NOT IN (SELECT ...);
O, Yes... The problem occure because of null from selecting into tbl2. I have to delete record which eventid=null, and the result become correct. Pls be carefull with Null entri when using NOT IN. Thanks for you all :-) Regards, -Yon- --- Tom Lane <[EMAIL PROTECTED]> wrote: >Yon Den Baguse Ngarso <[EMAIL PROTECTED]> writes: >> If i create tbl1 & tbl2, and then load it with the data. >> The result is CORRECT. Like yours. >> But, if the data loaded from another table, the result become WRONG/ null record. > >Do you have any nulls in what you are selecting into tbl2? >The behavior of NOT IN with nulls is not very intuitive. > > regards, tom lane _ Get [EMAIL PROTECTED] at http://www.dugem.com _ Promote your group and strengthen ties to your members with [EMAIL PROTECTED] by Everyone.net http://www.everyone.net/?btn=tag ---(end of broadcast)--- TIP 3: 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
