Re: [SQL] Extending Regular Expression bounds limit of 255.
On 28 May 2010 07:33, Brent DeSpain wrote:
> In the docs
> http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
> it says that Regular Expression bounds {m,n} that m and n can be 0-255. Is
> there a way to extend the upper limit. We are trying to be consistent
> between Regular Expressionimplementation an the other implementations do not
> have this limit.
Interesting. The POSIX standard for REs dictates this limit; which
implementations
you're using don't adhere to it?
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] data import: 12-hour time w/o AM/PM
On 9 February 2011 07:14, Tarlika Elisabeth Schmitz wrote: > From the date and time I want to create a timestamp. > I know that > - the events take place during the day, say between 10:30 and 22:30 > - it's always a set of events at one location spaced about 30min apart > - the imported data are chained (have a link to previous/next event) Any chance of seeing actual data? Might be a job for awk/perl rather than the RDBMS. Cheers, Andrej -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting location info from string
On 23 May 2011 10:00, Tarlika Elisabeth Schmitz wrote: > On Sun, 22 May 2011 21:05:26 +0100 > Tarlika Elisabeth Schmitz wrote: > >>A column contains location information, which may contain any of the >>following: >> >>1) null >>2) country name (e.g. "France") >>3) city name, region name (e.g. "Bonn, Nordrhein-Westfalen") >>4) city name, Rg. region name (e.g. "Frankfurt, Rg. Hessen") >>5) city name, Rg region name (e.g. "Frankfurt, Rg Hessen") > > > I also need to cope with variations of COUNTRY.NAME and REGION.NAME. I'm not sure I fully understand your request, but sanitising that data will be tedious, whichever way you dice it - particularly with the variations on region. Another thing of great import is whether the city can occur in the data column all by itself; if yes, it's next to impossible to distinguish it from a country. Specially if we assume that typos/misspelling are feasible on top of punctuation ... of course, you could create a list of valid cities and countries, with homophones thrown in for good measure, and compare & replace things appropriately, w/ name w/o a clean match being reported for human interaction =o) If I had a task like that to perform I'd dump the data out to file and have a good go at it w/ sed & awk, or perl, depending on how complex & voluminous the data is. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting location info from string
On 26 May 2011 09:13, Tarlika Elisabeth Schmitz wrote: > On Wed, 25 May 2011 09:25:48 -0600 > Rob Sargent wrote: > >> >> >>On 05/24/2011 10:57 AM, Lew wrote: >>> Tarlika Elisabeth Schmitz wrote: >>> >>>> CREATE TABLE person >>>> ( >>>> id integer NOT NULL, >>>> "name" character varying(256) NOT NULL, >>>> "location" character varying(256), >>>> CONSTRAINT person_pkey PRIMARY KEY (id) >>>> ); >>>> >>>> this was just a TEMPORARY table I created for quick analysis >>>> of my CSV data (now renamed to temp_person). > > CREATE TABLE country > ( > id character varying(3) NOT NULL, -- alpha-3 code > "name" character varying(50) NOT NULL, > CONSTRAINT country_pkey PRIMARY KEY (id) > ); > > >>To minimize the ultimately quite necessary human adjudication, one >>might make good use of what is often termed "crowd sourcing": Keep >>all the distinct "hand entered" values and a map to the final human >>assessment. > > I was wondering how to do just that. I don't think it would be a good > idea to hard code this into the clean-up script. Take, for instance, > variations of COUNTRY.NAME spelling. Where would I store these? > > I could do with a concept for this problem, which applies to a lot of > string-type info. I'd start w/ downloading a list as mentioned here: http://answers.google.com/answers/threadview?id=596822 And run it through a wee perl script using http://search.cpan.org/~maurice/Text-DoubleMetaphone-0.07/DoubleMetaphone.pm to make phonetic matches ... Then I'd run your own data through DoubleMetaphone, and clean up matches if not too many false positives show up. Cheers, Andrej -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to store a password encripted in a user defined table
On 3/1/07, Eugenio Flores <[EMAIL PROTECTED]> wrote: Hello, I wonder if somebody knows how to store passwords in a column that is part of a user defined table. Assuming that your passwords are application specific use a sha1 or md5 algorithm (depending on how sensitive your data is) and store that in a varchar or char field. When the user authenticates the password gets hashed in the app and compared against the stored hash. Cheers, Andrej ---(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
Re: [SQL] SHA-1 vs MD5
On 3/8/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: What is the problem you're trying to solve? Md5 is probably good enough for many cases, but for long-term use, you're right that sha-1 is what you need. Actually, you need sha-256, quite frankly. Looking at his last mail he's after a password hash. To the OP: Currently there's no support in pg for sha algorithms, but you could always implement those in your application and store the hash in pg that way. Cheers, Andrej ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] postgres configuration
On 3/14/07, Sumeet <[EMAIL PROTECTED]> wrote: Hi All, Hi, Sorry if this is the wrong list to ask this question. General woould have been better :) My Vacuum's are running very very slow and expecially when vacuuming indexes in large tables of size in 5-10 gigabytes, can some one help me determine the parameters for postgres.conf which will help me vacuum fast. I'm running postgres on a server with 32 gigs of ram and 8 processors. And the disk subsystem is ? Thanks, Sumeet. Cheers, Andrej ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Issue with copying data from a text file.
On 3/20/07, Paul Lambert <[EMAIL PROTECTED]> wrote: The source file comes from extracts on our main application which sits inside an in-house pretending-to-be-a-dbms file system. The content of these extracts would be difficult to change - the extract program would need to parse the data looking for quotes and preceed them with the necessary escape character. Not being a proper database dump it's not a simple matter of flicking a switch to get it to include the escape character. The way the extracts are written would require a few dozen lines of code to each extract, and theres about 40ish extracts. Plus I don't maintain that side of our code, and those that do can be a bit lazy and I'd likely be waiting months to get it done - if they even decide to do it. Pipe it through sed and replace the Carets with TABS? sed 's/^/\t/g' c:/temp/autodrs_deal_lines.txt > c:/temp/autodrs_deal_lines.tab Then use copy like so: \copy table from 'c:/temp/autodrs_deal_lines.tab' delimiter E'\t' null '' Cheers, Andrej ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] We all are looped on Internet: request + transport = invariant
On 4/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Problem consist of transportation data, received by SQL, into external world. As i already wrote, it's very difficalt for users (in practice) to use libraries of additional language (php , perl, etc) for that. I offer to put one feature into SQL to avoid mentioned libraries. And you offer latex and all other kind of things, too? That's the whole issue: you don't understand. SQL is for information retrieval, presentation thereof is and should (for a clean design) be independent of the retrieval. There's already a highly popular database out there that breaks and "enhances" standards. And there are tools available that will take tabular textual data and convert it into other file formats; no need to turn postgres into an egg-laying wool-bearing milk-sow. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] need help
On 5/14/07, Penchalaiah P. <[EMAIL PROTECTED]> wrote: Any one can help in this Operating system? Postgres version? How does psql behave? Anything in the logs? Cheers, Andrej ---(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
Re: [SQL] yet another simple SQL question
On 6/27/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: While self-admittedly grumpy, I believe John was trying to encourage better posting behavior from Joshua which will benefit him by receiving more answers. If John had remained silent (as I'm sure others who share his sentiment have), being (apparently) new, Joshua probably wouldn't know he's potentially limiting the number of answers he'd receive. Perhaps John could have phrased his email differently, but I think he was trying to help Joshua. Which makes this a fine opportunity to post the admirable http://www.catb.org/~esr/faqs/smart-questions.html :) -- Cheers, Andrej Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] yet another simple SQL question
On 7/2/07, John Summerfield <[EMAIL PROTECTED]> wrote: Andrej: I don't post links; I often find them unhelpful because I can't read them at the time I'm reading email. esr's article contains good advice, but there's room for argument on some of his points. Probably, not everyone will agree with me on Which_ points:-) I guess that's quite likely ... after all, there's humans who aren't ESR reading ;} involved ... I, too, think that a few points might need alteration, but over all I think it's quite valid; and it definitely offers some food for thought, and I believe it nudges people into the direction of at least AWARENESS of netiquette on varied lists. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(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
Re: [SQL] linux
On 7/12/07, Mohd Ghalib Akhtar <[EMAIL PROTECTED]> wrote: how to download linux 7.3 image file(means os) ? Please, for the sake of people who use mailing systems that support threading start a new mail for a new topic. Don't just respond to one you spotted and change the subject. Highly irritating. Cheers, Andrej ---(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
Re: [SQL] Converting from MS Access field aliases
On 7/13/07, Nis Jørgensen <[EMAIL PROTECTED]> wrote: Don't attribute to malice what can be adequately explained by incompetence. He didn't :) Nis Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(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
Re: [SQL] Restricting columns by users
On 8/8/07, Ranieri Mazili <[EMAIL PROTECTED]> wrote: > Exist something like it for postgresql? I thought that's what views are for > Thanks Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 1: 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] Extracting hostname from URI column
On 9/12/07, Paul Lambert <[EMAIL PROTECTED]> wrote: > > substring( href from '.*://\([^/]*)' ); > Ok, your solution looks better than mine... but I have no idea how to > interpret that, time to consult some manuals. Plain regex The key are the parenthesis () ... basically it will omit ANYTHING + two slashes at the beginning of a string. Then it will match everything BUT a slash, and as much of that as possible since regex are greedy by default (hence the host name he was looking for) ... and everything AFTER a slash will be omitted. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Extract interdependent info from one table
Hi Guys, I can't quite wrap my head around this one ... I have \d docmaster Table "public.docmaster" Column | Type | Modifiers ++--- alias1 | integer| alias2 | character varying(25) | subclass_alias | character varying(25) | docnum | integer| not null version| integer| docname| character varying(255) | Indexes: "docmaster_docnum_key" UNIQUE, btree (docnum) with the following data: # select * from docmaster ; alias1 | alias2 | subclass_alias | docnum | version | docname +---+++-+- 3589 | Completed | Inquiry| 653218 | 1 | My greater doc2 3587 | Pending | Post | 653216 | 3 | My great doc1 3588 | Draft | Reply | 653217 | 1 | My great doc2 3587 | Draft | Reply | 653219 | 2 | My greater doc4 (4 rows) Now I want to find inquiries (subclass_alias = 'Inquiry'), list their status and also (if there's another row that a) has the same alias1, a subclass of Reply and a status (alias2) of pending or redraft how do I achieve this? What I have is select docnum, alias1, alias2, subclass_alias from docmaster where (alias1 = ( select alias1 from docmaster where subclass_alias = 'Post' and ( alias2 = 'Pending' or alias2 = 'Redraft' ))) and ( alias2 = 'Pending' or alias2 = 'Draft' ) and subclass_alias <> 'Post' ; docnum | alias1 | alias2 | subclass_alias ++-+ 653219 | 3587 | Redraft | Reply (1 row) What I'd really like is to BOTH Post AND reply, with the alias2 for both. Hope this was as clear as mud? :) Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 1: 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] Extract interdependent info from one table
On 25/01/2008, Phillip Smith <[EMAIL PROTECTED]> wrote:
> Absolutely clear as mud :P
sorry ... I'm not too good with expressing my SQL needs, I'm afraid,
Thanks for trying :}
In the end I got it sorted with the following
select
dm.docnum,
dm.alias1,
dm.docname,
dm.alias2 as "Status",
dm2.alias2 as "Reply"
from docmaster as dm
left outer join
( select
alias1,
subclass_alias,
alias2,
entrywhen
from docmaster
where subclass_alias in ('Reply','MinTR')
and alias2 in ('PENDING','COMPLETED')
) as dm2
on dm2.alias1 = dm.alias1
where dm.subclass_alias='Post'
and alias2 in ('PENDING','REDRAFT');
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] select across two database
On 17/06/2008, Jorge Medina <[EMAIL PROTECTED]> wrote: > hi guys. > I want know if it's possible create a select from 2 database or create > a view in one of them. The short answer is no. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Encrytion in postgres field in table
On 29/07/2008, Chris Preston <[EMAIL PROTECTED]> wrote: > How do I setup a password table that I only want to encrypt 1 field > "password" http://www.postgresql.org/docs/8.3/interactive/pgcrypto.html -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
