[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Regular Expressions
Hi list, I would like to know if postgresql has a Regular Expressions (Regex) implemented already. With it we could implement queries like Select * from myClientes where name = 'E[zs]equias' where the result occurs even if the field has Ezequias or Esequias. Regards Ezequias ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Regular Expressions
Ezequias R. da Rocha wrote: Hi list, I would like to know if postgresql has a Regular Expressions (Regex) implemented already. With it we could implement queries like Select * from myClientes where name = 'E[zs]equias' where the result occurs even if the field has Ezequias or Esequias. Regards Ezequias Pretty easy to find matches in the documentation at http://search.postgresql.org/ eg. http://www.postgresql.org/docs/8.2/interactive/functions-matching.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Regular Expressions
On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote: Hi list, I would like to know if postgresql has a Regular Expressions (Regex) implemented already. With it we could implement queries like Select * from myClientes where name = 'E[zs]equias' Case Sensitive Regular Match ~ Case Insensitive Regular Match ~* Negated Case Sensitive Regular Match !~ Negated Case Insensitive Regular Match !~* Select * from myClientes where name ~ 'E[zs]equias' where the result occurs even if the field has Ezequias or Esequias. Regards Ezequias ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] job opportunity
I recognize the value of working with people who love what they do, that is why I am looking into the open source community. I am a statistician so I have no idea about how to find OSS programmers. Any suggestions are welcomed. A quantitative hedge fund within Lazard Asset Management in New York City is looking for a talented programmer to join a small team of researchers and portfolio managers. The job would intersect the fields of finance, applied statistics and computer science. The position involves applying intelligent design and development SQL programs to accelerate the speed of research. The individual would work directly with the portfolio manager to find and exploit market inefficiencies in the global equity markets. The role * Data analysis on large financial and market databases * Designing data structures, classes and method to test new research ideas * Use a diversity of statistical methods to estimate stock picking models. * Design and implementation of algorithms, * Software development The ideal candidate will have the following qualifications: * Formal education in Computer science (preferable), mathematics or statistics * Experience programming in SQL and R/Splus * Knowledge of applied statistics or mathematics (not required but interested). * Excellent communication skills. * Experience of general software systems is an advantage (eg .NET, XML, C++, ...). * Willingness and strong interest to learn about finance and capital markets. This opening offers a unique opportunity to get into the highly lucrative hedge fund business within a well-established and reputable firm. Because the team is small the individual will learn quickly about portfolio management and have fun using his or her skills freely. For further information, please send a resume to the email below. [EMAIL PROTECTED] Subject line: Quant-Analyst ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Issue with copying data from a text file.
Hi, I think I had the exact same problem as you do a while back and I solved it by removing the header row and the CSV HEADER clause of the statement. For the large files I had, it was easier (for me) to remove the header row than it was to escape out all the quotes (or regen the file): COPY deal_lines_temp_load FROM 'c:/temp/autodrs_deal_lines.txt' WITH DELIMITER AS '^'; I think the parser doesn't look for nor generate quoted rows except when CSV is specified.. It would be nice if there was a way to specify a HEADER row without invoking CSV parsing rules (friendly hint to core devs!) :) Let us all know if that works! Steve At 03:14 AM 3/20/2007, you wrote: Date: Tue, 20 Mar 2007 11:25:38 +0900 From: Paul Lambert [EMAIL PROTECTED] To: pgsql-sql@postgresql.org Subject: Issue with copying data from a text file. Message-ID: [EMAIL PROTECTED] I have a procedure in place that copies data from a caret delimited text file into a table storing some information. One of the fields in the table contains an item description which may contain item dimensions such as - 17 alloy wheels The problem I am getting when I do my load is I believe due to the presence of the double quotation marks giving the copy the impression that it is to include the information following as a single text string until it gets to the next set of double quotes. As a result, I get the following: AutoDRS=# COPY deal_lines_temp_load FROM 'c:/temp/autodrs_deal_lines.txt' WITH DELIMITER AS '^' CSV HEADER; ERROR: value too long for type character varying(30) CONTEXT: COPY deal_lines_temp_load, line 87, column order_desc: 17 5 spoke alloy wheels.^1291.18^117.38^983.69^1291.18^^C^^ The column as you can see is defined as a 30 character field, the load contains in this column ^17 5 spoke alloy wheels.^ I note an option in the COPY command to specify the quote character, defaulting to double quote. The problem being a single quote will also be used in the data, as will other characters. Is there any way to get a copy to have no quote character? I.e. read the file and put whatever is between the caret characters straight into the appropriate field exactly as is. TIA, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Regular Expressions
Guy Fraser escreveu: On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote: Hi list, I would like to know if postgresql has a Regular Expressions (Regex) implemented already. With it we could implement queries like Select * from myClientes where name = 'E[zs]equias' Case Sensitive Regular Match ~ Case Insensitive Regular Match ~* Negated Case Sensitive Regular Match !~ Negated Case Insensitive Regular Match !~* Select * from myClientes where name ~ 'E[zs]equias' where the result occurs even if the field has Ezequias or Esequias. Regards Ezequias ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Great I am thinking of putting my like to rest. I felt it faster than like statement, have you any information about that ? Ezequias ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Anyone still using the sql_inheritance parameter?
Is anybody still using the ability to set sql_inheritance to OFF? I'm considering removing the parameter in PG 8.3, so that the current default behavior (sql_inheritance = ON) would be the only behavior. sql_inheritance was created in 7.1 to allow existing applications to not be broken when we changed the default behavior, but I have not heard of anyone using it recently. The argument for removing it is basically that user-settable parameters that affect fundamental query semantics are dangerous. As an example, setting sql_inheritance to OFF causes silent malfunctioning of partitioned tables that are built using the currently-recommended approach. You could even argue that this is a security hole, because an unprivileged user could cause a security-definer function to fail to operate as intended --- okay, that's a bit of a stretch, but the scenario is not out of the question. We've recently been discussing the possibility that the search_path parameter could be used to force misbehavior of security-definer functions. There seems to be consensus in favor of adding language features to let creators of functions nail down the search_path to be used by their functions (though there's not a specific proposal yet). I don't really want to go through similar pushups for sql_inheritance; it doesn't seem worth it. So: would anyone cry if sql_inheritance disappeared in 8.3? If there are a lot of complaints, a possible compromise is to keep the variable but make it SUSET, ie, only changeable by superusers. This would still allow the setting to be turned off for use by legacy applications (probably by means of ALTER USER) while removing the objection that non-privileged users could break things. regards, tom lane ---(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
[SQL] growth of the database
Hi, Our database is growing fast. I want to create a cronjob that should tell me what is the current size of the database on each day. How can I find this from the database? Is there any pre-written scripts written by somebody to share? Regards skarthi _ Live Search Maps find all the local information you need, right when you need it. http://maps.live.com/?icid=hmtag2FORM=MGAC01 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Regular Expressions
On Wed, 2007-03-21 at 14:37 -0300, Ezequias R. da Rocha wrote: Guy Fraser escreveu: On Wed, 2007-03-21 at 11:04 -0300, Ezequias R. da Rocha wrote: Hi list, I would like to know if postgresql has a Regular Expressions (Regex) implemented already. With it we could implement queries like Select * from myClientes where name = 'E[zs]equias' Case Sensitive Regular Match ~ Case Insensitive Regular Match ~* Negated Case Sensitive Regular Match !~ Negated Case Insensitive Regular Match !~* Select * from myClientes where name ~ 'E[zs]equias' where the result occurs even if the field has Ezequias or Esequias. Regards Ezequias ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Great I am thinking of putting my like to rest. I felt it faster than like statement, have you any information about that ? No I don't know if regular expressions are faster than LIKE but I think they are more flexible. When developing queries, I usually try different methods of matching to find out what works best for each circumstance. Some times upper() lower() and substr() with an = are more effective than other methods. One of the more powerful features of PostgreSQL is the ability to use sub-selects to reduce the time required to process a subset of data from a larger volume of data. Example : select * from ( select ss_time, ss_date, ss_type, ss_data from full_set where ss_type in ('type_a','type_x') ) as sub_set where upper(ss_data) ~ '[A-Z][0-9][A-Z] ?[0-9][A-Z][0-9]' order by ss_time, ss_date, ss_type ; Ezequias ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Monitor what command is executing at the backend
Hi everybody, Is there a way to see from the log files on what sql statement is currently by which user? In other words, I want to monitor the DB activity. How can I find it? Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Monitor what command is executing at the backend
Is there a way to see from the log files on what sql statement is currently by which user? In other words, I want to monitor the DB activity. for a current snapshot you don't need the logs, try: select * from pg_stat_activity; (command string needs to be enabled for your database.) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Monitor what command is executing at the backend
George, How will I enable command string to see the commands? Regards skarthi From: George Pavlov [EMAIL PROTECTED] To: Karthikeyan Sundaram [EMAIL PROTECTED],pgsql-admin@postgresql.org,pgsql-sql@postgresql.org Subject: Re: [SQL] Monitor what command is executing at the backend Date: Wed, 21 Mar 2007 14:56:50 -0700 Is there a way to see from the log files on what sql statement is currently by which user? In other words, I want to monitor the DB activity. for a current snapshot you don't need the logs, try: select * from pg_stat_activity; (command string needs to be enabled for your database.) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate _ Its tax season, make sure to follow these few simple tips http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=HMMartagline ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Monitor what command is executing at the backend
How will I enable command string to see the commands? in your postgresql.conf set stats_command_string = true read http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html for details ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Regular Expressions
On Wed, Mar 21, 2007 at 02:37:07PM -0300, Ezequias R. da Rocha wrote: Great I am thinking of putting my like to rest. I felt it faster than like statement, have you any information about that ? I think this rather depends on what you're doing. If you're searching for like 'blahblah%' or ~ 'blahblah.*', they're AFAIK about the same. When you have a more complicated RE, though, it might turn out to be a win. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(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