Re: [SQL] Substrings by Regular Expression
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Fri, 14 Nov 2003, Jim Drummey wrote: >> pds=> select substring( str1 from ',' ) from address; >> ERROR: pg_atoi: error in ",": can't parse "," >> >> Am I missing something? > That functionality was added in 7.3 I believe. You may wish > to upgrade. Also, it's worth noting that AFAICT the above is illegal per SQL99 --- the textual SUBSTRING variants the spec defines are ::= SUBSTRING FROM [ FOR ] ::= SUBSTRING FROM FOR so it looks to me like you can't omit "FOR " if you want to adhere to the letter of the spec. We do allow omitting that clause as a pretty-obvious extension ... but it's important to realize that it *is* an extension. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Need another way to do this, my sql much to slow...
>
> Ok, I figured that part out by simply changing the way I'm doing to
> query, and writing a function to handle the reply. But it will introduce
> another problem. How to I pass special characters, any character, to a
> function like this:
>
> select msg_2_env('"Ann's Free Gifts & Coupons"
> <[EMAIL PROTECTED]>');
>
> As you can see the message from name is:
> "Ann's Free Gifts & Coupons" [EMAIL PROTECTED]
>
> I need that whole string to match. Including the ",&,@, and yes the
> single quote in Ann's. Passed as a variable this should not be a
> problem, I think, but how do I test this on the command line with psql?
>
> Oh, here is the simple function in case anyone cares to have it...very
> simple. Now processing about 10 records takes 1ms. Down from the
> 12-15 seconds. WooHoo. Just that other little issue..hehehe.
>
> CREATE FUNCTION msg_2_env (text) RETURNS int4 AS
> '
> DECLARE
> intext ALIAS FOR $1;
> result int4;
>
> BEGIN
>
> result := ( SELECT count(DISTINCT
> record_of_claims.msg_sender_num) AS mclaim_count FROM record_of_claims
> WHERE (record_of_claims.env_sender_num = (SELECT
> env_from_senders.env_sender_num FROM env_from_senders WHERE
> (env_from_senders.envelope_from = intext::character varying))) GROUP BY
> record_of_claims.env_sender_num );
>
> RETURN result;
>
> END;
> ' LANGUAGE 'plpgsql';
>
> Jerry Wintrode
> Network Administrator
> Tripos, Inc.
>
The only character you have to care about is the single quote. Do:
select msg_2_env('"Ann''s Free Gifts & Coupons"
<[EMAIL PROTECTED]>');
One more thing: As COUNT returns a bigint my coding would be
..
result bigint;
..
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Compare strings which resembles each other
> > Hy, I have a table containing some rows. Each row contains a field name > which is of type String. > > For example: > in row1 string1 is "Adieu Poulet", > in row2 string2 is "Adieu a Poulet", > in row3 string3 is "Adiue Poulet", > in row4 string4 is "Adieu Pouleet", etc.. > (That is each string resembles each other a bit, they are somewhat similar > to each other) > > What type of query should I make so as it return me all these string. That > is I watn a query which return me strings which are similar or somewhat > similar to each other. > > thanks in advance > gulshan > $PGSQLD/contrib/tsearch/README.tsearch is your friend. Regards, Christoph ---(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
Re: [SQL] Compare strings which resembles each other
On Fri, 21 Nov 2003, Christoph Haller wrote: > > > > Hy, I have a table containing some rows. Each row contains a field name > > which is of type String. > > > > For example: > > in row1 string1 is "Adieu Poulet", > > in row2 string2 is "Adieu a Poulet", > > in row3 string3 is "Adiue Poulet", > > in row4 string4 is "Adieu Pouleet", etc.. > > (That is each string resembles each other a bit, they are somewhat similar > > to each other) > > > > What type of query should I make so as it return me all these string. That > > is I watn a query which return me strings which are similar or somewhat > > similar to each other. > > > > thanks in advance > > gulshan > > > $PGSQLD/contrib/tsearch/README.tsearch > is your friend. I think tsearch(2) isn't what Gulshan need. He needs fuzzy search, there are several modules in contrib directory (metaphone, fuzzystrmatch). Also, we have trgm module which uses trigram and has index support. > > Regards, Christoph > > > ---(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 > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] cast varchar to numeric/money
Hi guys, I know it's impossible to just cast varchar to any numeric datatype, but is there another function/ custom function or any way to force to do that convertion. please help Thanks.. Yudie ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] cast varchar to numeric/money
On Fri, Nov 21, 2003 at 11:47:42 -0600, Yudie <[EMAIL PROTECTED]> wrote: > Hi guys, > > I know it's impossible to just cast varchar to any numeric datatype, > but is there another function/ custom function or any way to force to do > that convertion. You can use to_number to go from text to numeric. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to quote date value?
nobody wrote: I have found it in documentation, it is single quote. But it does not explain why SELECT '1/11/2003' AS "InvoiceDate"; returns "unknown" data type instead of "date". Why not a string ? or a fancy custom type ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cast varchar to numeric/money
I don't have to_numeric function in my 7.2.2 postgre. then it can resolve it with: cast(substr(varcharcolumn,0) as float4) thanks. - Original Message - From: "Bruno Wolff III" <[EMAIL PROTECTED]> To: "Yudie" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, November 21, 2003 11:55 AM Subject: Re: [SQL] cast varchar to numeric/money On Fri, Nov 21, 2003 at 11:47:42 -0600, Yudie <[EMAIL PROTECTED]> wrote: > Hi guys, > > I know it's impossible to just cast varchar to any numeric datatype, > but is there another function/ custom function or any way to force to do > that convertion. You can use to_number to go from text to numeric. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Expressional Indexes
Tom Lane <[EMAIL PROTECTED]> writes: > No, because the above represents a moving cutoff; it will (and should) > be rejected as a non-immutable predicate condition. You could do > something like > > CREATE INDEX my_Nov_03_index on my_table (create_date) > WHERE (create_date >= date '2003-11-01'); > > and then a month from now replace this with > > CREATE INDEX my_Dec_03_index on my_table (create_date) > WHERE (create_date >= date '2003-12-01'); > > bearing in mind that this index can be used with queries that contain > WHERE conditions like "create_date >= some-date-constant". The planner > must be able to convince itself that the right-hand side of the WHERE > condition is >= the cutoff in the index's predicate condition. Since > the planner is not very bright, both items had better be simple DATE > constants, or it won't be able to figure it out ... Note that if you're just doing this to speed up regular queries where you have create_date in some small range, then you'll likely not see much of an increase. Mainly you'll just save space. What can be interesting is to create a partial index like this but over a second unrelated column. Something like: CREATE INDEX my_dec_03_index on my_table (userid) WHERE (create_date >= date '2003-11-02'); Then you can do queries like SELECT * FROM my_table WHERE userid = ? AND create_date >= date '2003-11-02' And it'll be able to efficiently pull out just those records, even if there are thousands more records that are older than 2003-11-02. This avoids having to create a two-column index with a low-selectivity column like "month". -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to quote date value?
On Fri, 21 Nov 2003, Gaetano Mendola wrote: > nobody wrote: > > I have found it in documentation, it is single quote. But it does not > > explain why > > > > SELECT '1/11/2003' AS "InvoiceDate"; > > > > returns "unknown" data type instead of "date". (I haven't seen the original message yet, so I'm replying to a reply) Date literals are generally written as: DATE '1/11/2003' PostgreSQL will try to guess what type you meant with quoted strings in expressions, but in the above there isn't enough context to do guess that you meant a date really (it should probably actually be thought of as a string in such cases). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] cast varchar to numeric/money
Dnia 2003-11-21 19:26, Użytkownik Yudie napisał: I don't have to_numeric function in my 7.2.2 postgre. then it can resolve it with: cast(substr(varcharcolumn,0) as float4) Read once again. The function is not "to_numeric", but "to_number". You can read about this function in manual: 6.7. Data Type Formatting Functions Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Compare strings which resembles each other
On Friday 21 November 2003 15:32, Oleg Bartunov wrote: > On Fri, 21 Nov 2003, Christoph Haller wrote: > > $PGSQLD/contrib/tsearch/README.tsearch > > is your friend. > > I think tsearch(2) isn't what Gulshan need. He needs fuzzy search, > there are several modules in contrib directory (metaphone, fuzzystrmatch). > Also, we have trgm module which uses trigram and has index support. Hi Oleg, do you have any more information on trgm? According to your website it isn't released. I am looking for something along those lines and might be able to assist, at least with testing and documentation. Ian Barwick [EMAIL PROTECTED] ---(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] Compare strings which resembles each other
On Fri, 21 Nov 2003, Ian Barwick wrote: > On Friday 21 November 2003 15:32, Oleg Bartunov wrote: > > On Fri, 21 Nov 2003, Christoph Haller wrote: > > > > $PGSQLD/contrib/tsearch/README.tsearch > > > is your friend. > > > > I think tsearch(2) isn't what Gulshan need. He needs fuzzy search, > > there are several modules in contrib directory (metaphone, fuzzystrmatch). > > Also, we have trgm module which uses trigram and has index support. > > Hi Oleg, > > do you have any more information on trgm? According to your > website it isn't released. I am looking for something along those lines > and might be able to assist, at least with testing and documentation. It's don't released because of lack of documentation :( It's tested and used in production. I'll send you archive in separate message. > > Ian Barwick > [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
