Re[2]: [GENERAL] Generic search
Hello Taral, venerdì, 4 dicembre 98, you wrote: >>hygea=> explain select * from comuni where nome = 'A%'; >>NOTICE: QUERY PLAN: >>Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) >>^ T> The question was about LIKE, not =. Because LIKE uses regexp-style matching and T> we have no substring index functionality, it cannot use the index. If you're T> always matching on the first character, you can do something like fulltextindex T> does and use triggers and a second (indexed) table to be able to match on the T> first character only. Sorry, my fault. hygea=> explain select * from comuni where nome like 'A%'; NOTICE: QUERY PLAN: Index Scan using nome_comune_idx on comuni (cost=72.34 size=1 width=84) EXPLAIN -Jose'-
Re: [GENERAL] compiling postgresql 6.4 in BSDi
> On Fri, 4 Dec 1998, Ibrahim wrote: > > > > > I've BSDi 3.1 & BSDi 4.0 . I always get same error messages when I > > compiling postgresql 6.4. > > The error messages : > > > > /usr/bin/yacc -d gram.y > > /usr/sbin/yacc: f - maximum table size exceeded > > ^^^ > > > > I'm using flex 2.5.4a. > > yacc -> bison > lex -> flex > > install bison and you shoudl be okay... The bigger questions is why 6.4 does not use the gram.c file, as it should. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] compiling postgresql 6.4 in BSDi
> > I've BSDi 3.1 & BSDi 4.0 . I always get same error messages when I > compiling postgresql 6.4. > The error messages : > > /usr/bin/yacc -d gram.y > /usr/sbin/yacc: f - maximum table size exceeded > ^^^ > > I'm using flex 2.5.4a. That is strange, because you have flex. Try removing backend/parser/gram.c and recompile. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Text Databases
> On Thu, 3 Dec 1998, Nicholas Humfrey wrote: > > > > > On Wed, 2 Dec 1998, Dustin Sallings wrote: > > >it sounds like you're looking at the wrong tool for a particular job. > > > > I am trying to get the best of both worlds. A good relational database > > system as well as being able to do text searches in mutiple fields. > > > > On Thu, 3 Dec 1998, Marc G. Fournier wrote: > > >See the 'fulltextindex' directory under the contrib directory. > > > > How fast is PostgreSQL at searching for text. Should I, as Dustin Sallings > > wrote look for a different relational database system ? I like PostgreSQL's > > features especially free source code, so I can run it on different > > platforms (Sparc,PPC,i486 etc.) Is there a more suitable free system > > available ? > > From reading the README file in the fulltextindex directory, it > looks like the way they implemented it should be as fast as any other of > the indicescheck the README file and see what you think... Yes, it should be. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Generic search
> >hygea=> explain select * from comuni where nome = 'A%'; > >NOTICE: QUERY PLAN: > >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) > >^ > > The question was about LIKE, not =. Because LIKE uses regexp-style matching and > we have no substring index functionality, it cannot use the index. If you're > always matching on the first character, you can do something like fulltextindex > does and use triggers and a second (indexed) table to be able to match on the > first character only. If the start of the search string is anchored. From the FAQ: When using wild-card operators like LIKE or ~, indices can only be used if the beginning of the search is anchored to the start of the string. So, to use indices, LIKE searches can should not begin with %, and ~(regular expression searches) should start with ^. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Generic search
On Fri, 4 Dec 1998, Taral wrote: > >hygea=> explain select * from comuni where nome = 'A%'; > >NOTICE: QUERY PLAN: > >Index Scan using nome_comune_idx on comuni (cost=2.05 size=2 width=84) > >^ > > The question was about LIKE, not =. Because LIKE uses regexp-style matching and > we have no substring index functionality, it cannot use the index. If you're > always matching on the first character, you can do something like fulltextindex > does and use triggers and a second (indexed) table to be able to match on the > first character only. Actually, on a beginging string only search you can do: explain select * from td_prices where manu~'^IBM'; NOTICE: QUERY PLAN: Index Scan on td_prices (cost=682.34 size=1 width=64) Thats using a btree index. :) You can't do a case insensitive search though.
RE: [GENERAL] How can I obtain tables' structure?
> Paul Mookhachov <[EMAIL PROTECTED]> writes:
>
> > Hi!
>
> Hi !
>
> > How can I obtain information about tables' structure and list of
> tables
> > using psql.lib or Pg.pm (perl package)?
>
> I think you got no wayt to do it directly, but with here's a
> trick :
> all you have to do is to make a request likt this :
> 'select * from ' ($req = $cbase->exec ('select * from table'))
I'd use 'select * from where false'. It save the table scan and
you get the same info.
> now with '$req->nfields' you get the number of fields in the
> table.
> with '$fname = $req->fname($num_field)' you get the name of
> the field. and with '$ftype = $req->ftype($num_field)' you get the
> type of the field.
>
> You can get many other information on the table with some
> other commands... Just take a look at the perldoc page of Pg (to get
> it : 'perldoc Pg').
>
> > Thanx.
>
> You're welcome !
> --
> ___
> {~._.~}Stephane - DUST - Dupille
> ( Y )You were dust and you shall turn into dust
> ()~*~() email : [EMAIL PROTECTED]
> (_)-(_)
[GENERAL] question 1
Hi, i have this query: select * from libro where id_libro_key in (select id_libro from libro_autore where id_auto re in (select id_autore from autore where ((cognome = 'King' and nome = 'Stephen') OR (cog nome = 'Clancy' and nome = 'Tom'\g that return me this error: ERROR: There is no operator '=' for types 'int4' and 'varchar' You will either have to retype this query using an explicit cast, or you will have to define the operator using CREATE OPERATOR but if I split the query in 2 part I obtain: 1)select id_libro from libro_autore where id_autore in (select id_autore from autore where ((cognome = 'King' and nome = 'Stephen') OR (cognome = 'Clancy' and nome = 'Tom')))\g id_libro 11 12 (2 rows) 2)esame=> select * from libro where id_libro_key in ( 11,12)\g titolo |id_libro|id_utente|collocazione|casa_edit| data_publ|tipo|difetto|id_libro_ke y ++-++-+--++---+--- - It |d1 | |d1 |apo |05-12-1940| 1| | 1 1 Il talismano|s1 | |d1 |aop2 |05-12-1985| 1| | 1 2 (2 rows) why I can not make the union/join ? where I'm wrong ? these are the table: esame=> \d libro Table= libro +--+--+---+ | Field | Type| Length| +--+--+---+ | titolo | varchar() not null | 80 | | id_libro | varchar() not null | 10 | | id_utente| int4 | 4 | | collocazione | varchar() not null | 10 | | casa_edit| varchar()| 20 | | data_publ| date | 4 | | tipo | int4 | 4 | | difetto | varchar()| 40 | | id_libro_key | int4 not null default nextval ( | 4 | +--+--+---+ esame=> \d libro_autore Table= libro_autore +--+--+---+ | Field | Type| Length| +--+--+---+ | id_libro | varchar() not null | 10 | | id_autore| int4 not null| 4 | +--+--+---+ "Il divertimento e' giusto se la scimmia ci prende gusto" -- Italian Linux Press: http://ziobudda.enter.it/ILP/ -- Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group [EMAIL PROTECTED] - http://ziobudda.enter.it/ Linux Problem? Ask to [EMAIL PROTECTED] "/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple"
[GENERAL] question 2
Hi again, I have this query : select prestito.*, libro.tipo,libro.id_libro from prestito,libro where prestito.id_libro = libro.id_libro_key that return me (correct!): id_libro|id_utente|data_prestito|visto|notifica1|notifica2|notifica3|tipo|id_libro +-+-+-+-+-+-++ 12| 37| 12-04-1998|f| | | | 1|s1 13| 37| 12-04-1998|f| | | | 2|k1 (2 rows) but if you see the item libro.id_libro is id_libro (s1,k1) and in the prestito.* there is another id_libro (11,12). Now I want to know how can I select the second "id_lib ro". "Il divertimento e' giusto se la scimmia ci prende gusto" -- Italian Linux Press: http://ziobudda.enter.it/ILP/ -- Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group [EMAIL PROTECTED] - http://ziobudda.enter.it/ Linux Problem? Ask to [EMAIL PROTECTED] "/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple"
[GENERAL] question 3
Hi again, I have this 3 tables: esame=> select * from prestito\g id_libro|id_utente|data_prestito|visto|notifica1|notifica2|notifica3 +-+-+-+-+-+- 11| 37| 12-04-1998|f| | | (1 row) esame=> select * from utente\g cognome |nome|id_utente|indirizzo | telefono|tipo_utente|info_utente |l ibri_presi| notifica1| notifica2| notifica3|password|email ++-+++---+--+- --+--+--+--++-- Ballabio|Dario | 37|Via carducci 125| 02-2422700| 1|Ditta Enter S.r.L.| | | | |dario |[EMAIL PROTECTED] esame=> select * from libro\g titolo |id_libro|id_utente|collocazione|casa_edit| data_publ|tipo|difetto|id_libro_ke y ++-++-+--++---+--- - Il talismano|s1 |0|d1 |aop2 |05-12-1985| 1| | 1 2 It |d1 | 37|d1 |apo |05-12-1940| 1| | 1 1 I need a query that for each id_utente return me the numeber of items in "prestito" and th is number is group by libro.tipo "Il divertimento e' giusto se la scimmia ci prende gusto" -- Italian Linux Press: http://ziobudda.enter.it/ILP/ -- Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group [EMAIL PROTECTED] - http://ziobudda.enter.it/ Linux Problem? Ask to [EMAIL PROTECTED] "/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple"
RE: [GENERAL] question 2
The easiest way is to give it another name select presto.*, libro.tipo, libro.id_libro as id_libro2 from ... and just grab id_libro2 for the second id_libro. You can also select it by column number in most interfaces. -DEJ > -Original Message- > From: ZioBudda [SMTP:[EMAIL PROTECTED]] > Sent: Friday, December 04, 1998 12:06 PM > To: [EMAIL PROTECTED] > Subject: [GENERAL] question 2 > > Hi again, > I have this query : > select prestito.*, libro.tipo,libro.id_libro from prestito,libro where > prestito.id_libro = > libro.id_libro_key > that return me (correct!): > id_libro|id_utente|data_prestito|visto|notifica1|notifica2|notifica3|t > ipo|id_libro > +-+-+-+-+-+-+- > ---+ > 12| 37| 12-04-1998|f| | | | > 1|s1 > 13| 37| 12-04-1998|f| | | | > 2|k1 > (2 rows) > > but if you see the item libro.id_libro is id_libro (s1,k1) and in the > prestito.* > there is another id_libro (11,12). Now I want to know how can I > select > the second "id_lib > ro". > > > > > "Il divertimento e' giusto se la scimmia ci prende gusto" > -- > Italian Linux Press: http://ziobudda.enter.it/ILP/ > -- > Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group > [EMAIL PROTECTED] - http://ziobudda.enter.it/ > Linux Problem? Ask to [EMAIL PROTECTED] > "/dev/ziobudda: access to /var/tmp/beer denied, use > /var/adm/pineapple" >
RE: [GENERAL] question 3
I really have no idea what you are truly asking for here, but here's a crack at what I think you want. select count(p.*) from prestito p, libro l where p.id_libro = l.id_libro group by p.id_utente, l.tipo Try it... -DEJ > -Original Message- > Hi again, > I have this 3 tables: > esame=> select * from prestito\g > id_libro|id_utente|data_prestito|visto|notifica1|notifica2|notifica3 > +-+-+-+-+-+- > 11| 37| 12-04-1998|f| | | > (1 row) > > esame=> select * from utente\g > cognome |nome|id_utente|indirizzo | > telefono|tipo_utente|info_utente |l > ibri_presi| notifica1| notifica2| notifica3|password|email > ++-+++---+ > --+- > --+--+--+--++-- > Ballabio|Dario | 37|Via carducci 125| 02-2422700| > 1|Ditta Enter S.r.L.| > | | | |dario |[EMAIL PROTECTED] > > esame=> select * from libro\g > titolo |id_libro|id_utente|collocazione|casa_edit| > data_publ|tipo|difetto|id_libro_ke > y > ++-++-+--+ > +---+--- > - > Il talismano|s1 |0|d1 |aop2 |05-12-1985| > 1| > | 1 > 2 > It |d1 | 37|d1 |apo |05-12-1940| > 1| > | 1 > 1 > > I need a query that for each id_utente return me the numeber of items > in > "prestito" and th > is number is group by libro.tipo > > > > "Il divertimento e' giusto se la scimmia ci prende gusto" > -- > Italian Linux Press: http://ziobudda.enter.it/ILP/ > -- > Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group > [EMAIL PROTECTED] - http://ziobudda.enter.it/ > Linux Problem? Ask to [EMAIL PROTECTED] > "/dev/ziobudda: access to /var/tmp/beer denied, use > /var/adm/pineapple" >
RE: [GENERAL] question 1
I don't know why you are experiencing a problem here but I think I can speed up that query. Let's try a rewrite. SELECT * FROM libro l WHERE EXISTS( SELECT la.id_libro FROM libro_autore la, autore a WHERE la.id_libro = l.id_libro_key AND la.id_autore = a.id_autore AND ((a.cognome = 'King' AND a.nome = 'Stephen') OR (a.cognome = 'Clancy' AND a.nome = 'Tome')) See if that gives you what you are expecting. > -Original Message- > Hi, i have this query: > select * from libro where id_libro_key in (select id_libro from > libro_autore where id_auto > re in (select id_autore from autore where ((cognome = 'King' and nome > = > 'Stephen') OR (cog > nome = 'Clancy' and nome = 'Tom'\g > that return me this error: > ERROR: There is no operator '=' for types 'int4' and 'varchar' > You will either have to retype this query using an explicit > cast, > or you will have to define the operator using CREATE OPERATOR > > but if I split the query in 2 part I obtain: > 1)select id_libro from libro_autore where id_autore in (select > id_autore > from autore where > ((cognome = 'King' and nome = 'Stephen') OR (cognome = 'Clancy' and > nome > = 'Tom')))\g > id_libro > > 11 > 12 > (2 rows) > > 2)esame=> select * from libro where id_libro_key in ( 11,12)\g > titolo |id_libro|id_utente|collocazione|casa_edit| > data_publ|tipo|difetto|id_libro_ke > y > ++-++-+--+ > +---+--- > - > It |d1 | |d1 |apo |05-12-1940| > 1| > | 1 > 1 > Il talismano|s1 | |d1 |aop2 |05-12-1985| > 1| > | 1 > 2 > (2 rows) > > why I can not make the union/join ? > where I'm wrong ? > these are the table: > esame=> \d libro > > Table= libro > +--+-- > +---+ > | Field | Type > | > Length| > +--+-- > +---+ > | titolo | varchar() not null > | > 80 | > | id_libro | varchar() not null > | > 10 | > | id_utente| int4 > | > 4 | > | collocazione | varchar() not null > | > 10 | > | casa_edit| varchar() > | > 20 | > | data_publ| date > | > 4 | > | tipo | int4 > | > 4 | > | difetto | varchar() > | > 40 | > | id_libro_key | int4 not null default nextval ( > | > 4 | > +--+-- > +---+ > > > esame=> \d libro_autore > > Table= libro_autore > +--+-- > +---+ > | Field | Type > | > Length| > +--+-- > +---+ > | id_libro | varchar() not null > | > 10 | > | id_autore| int4 not null > | > 4 | > +--+-- > +---+ > > > > > "Il divertimento e' giusto se la scimmia ci prende gusto" > -- > Italian Linux Press: http://ziobudda.enter.it/ILP/ > -- > Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group > [EMAIL PROTECTED] - http://ziobudda.enter.it/ > Linux Problem? Ask to [EMAIL PROTECTED] > "/dev/ziobudda: access to /var/tmp/beer denied, use > /var/adm/pineapple" >
[GENERAL] Perl DBD / DBI modules
I've recently compiled and put into service Posgresql v6.4. All is working well. I am looking for information on locating, compiling, and implementing a Perl DBD or DBI for Postgresql. Can anyone assist? Bob
RE: [GENERAL] question 1
On Fri, 4 Dec 1998, Jackson, DeJuan wrote: > I don't know why you are experiencing a problem here but I think I can > speed up that query. Let's try a rewrite. tnx for the "new" query. I have post my question1 here because I think that is a postgres problem. ahhh...pgsql-sql ?
[GENERAL] 6.4 ?
I've used PostgreSQL before, but has just joined this mailing list again after having searched www.postgresql.org for any information about 6.4. There's no mention of what is news in 6.4 anywhere to be found. Why not? What _is_ new?
RE: [GENERAL] question 3
On Fri, 4 Dec 1998, Jackson, DeJuan wrote: > I really have no idea what you are truly asking for here, but here's a > crack at what I think you want. > select count(p.*) from prestito p, libro l > where p.id_libro = l.id_libro > group by p.id_utente, l.tipo > Try it... parser: parse error at or near "*" "Il divertimento e' giusto se la scimmia ci prende gusto" -- Italian Linux Press: http://ziobudda.enter.it/ILP/ -- Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group [EMAIL PROTECTED] - http://ziobudda.enter.it/ Linux Problem? Ask to [EMAIL PROTECTED] "/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple"
Re: [GENERAL] Perl DBD / DBI modules
On Fri, 4 Dec 1998, Bob Kruger wrote: Just as with any other perl module: /usr/local/bin/perl -MCPAN -e shell install DBI install DBD::Pg # # I've recently compiled and put into service Posgresql v6.4. All is working # well. # # I am looking for information on locating, compiling, and implementing a # Perl DBD or DBI for Postgresql. Can anyone assist? # # Bob # # # -- SA, beyond.com My girlfriend asked me which one I like better. pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <[EMAIL PROTECTED]> |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE L___ I hope the answer won't upset her.
Re: [GENERAL] 6.4 ?
> I've used PostgreSQL before, but has just joined this mailing list > again after having searched www.postgresql.org for any information > about 6.4. There's no mention of what is news in 6.4 anywhere to be > found. Why not? What _is_ new? See the TODO list on the support page, or see the HISTORY file in the 6.4 distribution. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [GENERAL] question 3
> -Original Message- > From: ZioBudda [SMTP:[EMAIL PROTECTED]] > Sent: Friday, December 04, 1998 4:44 PM > To: Jackson, DeJuan > Cc: [EMAIL PROTECTED] > Subject: RE: [GENERAL] question 3 > > On Fri, 4 Dec 1998, Jackson, DeJuan wrote: > > > I really have no idea what you are truly asking for here, but here's > a > > crack at what I think you want. > > select count(p.*) from prestito p, libro l > > where p.id_libro = l.id_libro > > group by p.id_utente, l.tipo > > Try it... > > parser: parse error at or near "*" sub the * with any column from p this is not NULL. > "Il divertimento e' giusto se la scimmia ci prende gusto" > -- > Italian Linux Press: http://ziobudda.enter.it/ILP/ > -- > Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group > [EMAIL PROTECTED] - http://ziobudda.enter.it/ > Linux Problem? Ask to [EMAIL PROTECTED] > "/dev/ziobudda: access to /var/tmp/beer denied, use > /var/adm/pineapple" >
Re: [GENERAL] compiling postgresql 6.4 in BSDi
The Hermit Hacker wrote: > > On Fri, 4 Dec 1998, Ibrahim wrote: > > > > > I've BSDi 3.1 & BSDi 4.0 . I always get same error messages when I > > compiling postgresql 6.4. > > The error messages : > > > > /usr/bin/yacc -d gram.y > > /usr/sbin/yacc: f - maximum table size exceeded > > ^^^ > > > > I'm using flex 2.5.4a. > > yacc -> bison > lex -> flex > > install bison and you shoudl be okay... It works ! thanx, but I'must remove backend/parser/gram.c first. I think BSDi user shoule know about this instalation procedure. thanx Hermit, thanx Bruce regards, Ibam
