[SQL] pg_dump
I've install postgres in linux. but pg_dump its not working at all. if i execute the pg_dump to the file like this pg_dump -Upostgres -dpostgres > file.dmp the file file.dmp is empty and if i execute the psql with list database option, the result is error -bash-3.00$ psql -l ERROR: relation "pg_catalog.pg_user" does not exist can any one help me Thanx tosa === PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Postgres regexp matching failure?
I have an regular expression, wich works fine in Java or python, but I
don't seem to be able to make it work in postgres. Regular expression
needs to match everything begining with '+' (plus sign) or letters
'STOP', 'stop', 'StoP', or any other combination pronounced 'stop'.
Here is the python example:
>>> import re
>>> p = re.compile(r'^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$')
>>> p.match('+mario').group(0)
'+mario'
>>> p.match('+mario works').group(0)
'+mario works'
>>> p.match('mario works').group(0)
Traceback (most recent call last):
File "", line 1, in ?
AttributeError: 'NoneType' object has no attribute 'group'
>>> p.match('stop works').group(0)
'stop works'
>>>
Now, here is what happens if I try this in postgres:
pulitzer2=# select '+mario' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*
$';
?column?
--
t
(1 row)
This one is ok.
pulitzer2=# select '+mario works' ~ '^\s*(?:[\
+|-]|(?:[sS][tT][oO][pP]\b)).*$';
?column?
--
t
(1 row)
This one is also ok.
pulitzer2=# select 'mario works' ~ '^\s*(?:[\
+|-]|(?:[sS][tT][oO][pP]\b)).*$';
?column?
--
f
(1 row)
Same as this one, also ok.
pulitzer2=# select 'stop works' ~ '^\s*(?:[\
+|-]|(?:[sS][tT][oO][pP]\b)).*$';
?column?
--
f
(1 row)
Here, postgres should return true, but it gives me false.
I'd appreciate any hints on what is wrong here.
Thank you in advance,
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] pg_dump
On Tue, Sep 05, 2006 at 02:22:29PM +0700, M. Santosa wrote: > and if i execute the psql with list database option, the result is error > > -bash-3.00$ psql -l > ERROR: relation "pg_catalog.pg_user" does not exist This looks to me like you have more than one postmaster installed, and you've got a mismatch in versions. Be my first guess, anyway. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgres regexp matching failure?
On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote: pulitzer2=# select 'stop works' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$'; ?column?-- f(1 row)Here, postgres should return true, but it gives me false. \b is a back-space - is that what you are wanting there? If I remove it I get true.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Postgres regexp matching failure?
Mario Splivalo <[EMAIL PROTECTED]> writes: > Now, here is what happens if I try this in postgres: > pulitzer2=# select '+mario' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$'; I'm thinking you've forgotten to double your backslashes. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgres regexp matching failure?
On Tue, 2006-09-05 at 10:11 -0400, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > Now, here is what happens if I try this in postgres: > > > pulitzer2=# select '+mario' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$'; > > I'm thinking you've forgotten to double your backslashes. > That was the first thing I tried :) What confused me was the difference I get when using python, or perl, or Java, or any other regexp 'matcher', therefore I asked for help here. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Postgres regexp matching failure?
On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote: > On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote: > > pulitzer2=# select 'stop works' ~ '^\s*(?:[\ > +|-]|(?:[sS][tT][oO][pP]\b)).*$'; > ?column? > -- > f > (1 row) > > Here, postgres should return true, but it gives me false. > > > \b is a back-space - is that what you are wanting there? If I remove > it I get true. Actually, I'm not sure :) As I've mentioned, python/java/perl do as I expected, postgres on the other hand doesn't. If \b was the backspace, then I'd have trouble with '+mario test', and that one seems to be OK. I also tried doublebackslashing, all the same. So, I guess it's obvious that postgres doesn't treat regular expressions the same way as java/perl/pyton/php/awk/sed do... Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Postgres regexp matching failure?
Mario Splivalo wrote: > On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote: > > On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote: > > > > pulitzer2=# select 'stop works' ~ '^\s*(?:[\ > > +|-]|(?:[sS][tT][oO][pP]\b)).*$'; > > ?column? > > -- > > f > > (1 row) > > > > Here, postgres should return true, but it gives me false. > > > > > > \b is a back-space - is that what you are wanting there? If I remove > > it I get true. > > Actually, I'm not sure :) As I've mentioned, python/java/perl do as I > expected, postgres on the other hand doesn't. If \b was the backspace, > then I'd have trouble with '+mario test', and that one seems to be OK. No, because the \b is inside the "stop" arm of the |. You need to do *both*, double backslashes and get rid of \b (or at least understand what you actually mean with it ...) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] pg_dump
"M. Santosa" <[EMAIL PROTECTED]> writes: > I've install postgres in linux. > but pg_dump its not working at all. > -bash-3.00$ psql -l > ERROR: relation "pg_catalog.pg_user" does not exist IIRC, this is a symptom of selinux having interfered with initdb. Get a newer copy of the selinux policy package (or turn it off temporarily) and redo initdb. You can find more info if you search our archives for "selinux". regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Postgres regexp matching failure?
On Tue, 2006-09-05 at 10:21 -0400, Alvaro Herrera wrote: > Mario Splivalo wrote: > > On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote: > > > On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote: > > > > > > pulitzer2=# select 'stop works' ~ '^\s*(?:[\ > > > +|-]|(?:[sS][tT][oO][pP]\b)).*$'; > > > ?column? > > > -- > > > f > > > (1 row) > > > > > > Here, postgres should return true, but it gives me false. > > > > > > > > > \b is a back-space - is that what you are wanting there? If I remove > > > it I get true. > > > > Actually, I'm not sure :) As I've mentioned, python/java/perl do as I > > expected, postgres on the other hand doesn't. If \b was the backspace, > > then I'd have trouble with '+mario test', and that one seems to be OK. > > No, because the \b is inside the "stop" arm of the |. You need to do > *both*, double backslashes and get rid of \b (or at least understand > what you actually mean with it ...) > I know this might not be the right place for this question, but, how come (or better: why?) is above regexp macthed ok (ok as in 'the way I expected') when employed from java/perl/python? Mario ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Postgres regexp matching failure?
Mario Splivalo wrote: > On Tue, 2006-09-05 at 10:21 -0400, Alvaro Herrera wrote: > > Mario Splivalo wrote: > > > On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote: > > > > On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote: > > > > > > > > pulitzer2=# select 'stop works' ~ '^\s*(?:[\ > > > > +|-]|(?:[sS][tT][oO][pP]\b)).*$'; > > > > ?column? > > > > -- > > > > f > > > > (1 row) > > > > > > > > Here, postgres should return true, but it gives me false. > > > > > > > > > > > > \b is a back-space - is that what you are wanting there? If I remove > > > > it I get true. > > > > > > Actually, I'm not sure :) As I've mentioned, python/java/perl do as I > > > expected, postgres on the other hand doesn't. If \b was the backspace, > > > then I'd have trouble with '+mario test', and that one seems to be OK. > > > > No, because the \b is inside the "stop" arm of the |. You need to do > > *both*, double backslashes and get rid of \b (or at least understand > > what you actually mean with it ...) > > > > I know this might not be the right place for this question, but, how > come (or better: why?) is above regexp macthed ok (ok as in 'the way I > expected') when employed from java/perl/python? In Perl at least, \b is a word boundary. In PostgreSQL (and probably Tcl as well) it's a backslash AFAICT. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Postgres regexp matching failure?
Mario Splivalo <[EMAIL PROTECTED]> writes: > So, I guess it's obvious that postgres doesn't treat regular expressions > the same way as java/perl/pyton/php/awk/sed do... When you get into stuff as arcane as word-boundary constraints, you'll find that regexes are not NEARLY as well standardized as that flippant complaint suggests. For penance, actually try it in all six of those languages and report back. Postgres' regexp code is the same as Tcl's (it's Henry Spencer's package) and if you check TFM you will find out that \y, or possibly \M, is what you want. 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
Re: [SQL] Subquery alternatives?
I dont think you need the double-left join SELECT * FROM STORIES ST LEFT JOIN TAGS TG ON TG.tagkey = ST.storykey WHERE TG.tag = "science" "MRKisThatKid" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, i've posted this in various places but I'm really struggling to > find an answer that works for me... > > Say I've got a table of stories and a table of tags which apply to the > stories. > > Say you've been using the tags science and unfinished, and you want all > the stories which are science based and are completed.. that is, all > the stories tagged with "science" but not "unfinished". > > I could do this using sub-queries quite easily, especially the exists / > not exists function in mysql. > > Unfortunately I'm implementing this kind of query into a web > application i'm developing and I want it to be usable on as many > different server set-ups as possible. > > select * from stories > left join tags as tags1 on (tags1.tagkey = storykey) > left join tags as tags2 on (tags2.tagkey = storykey) > where tags1.tag = "science" > and not tags2.tag = "unfinished" > > It would be wonderful if the above worked, but of course it doesn't > because when the db engine makes all the possible combinations it will > always find a match where tags2 doesn't contain "unfinished". > > Can anyone think of a way of achieving this without sub-queries? > > ... The best solution I've been offered so far is to use group_concat > and find_in_set, but these are mysql specific functions and are not > portable. Do any of you guys know who I should go about this? > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Postgres regexp matching failure?
On 9/5/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: In Perl at least, \b is a word boundary. In PostgreSQL (and probablyTcl as well) it's a backslash AFAICT.More specifically, Perl, Java and Python interpret \b as a backspace in the Character class only (got that from the Regular _expression_ pocket reference) but otherwise are word boundries. I am not entirely sure what the Character class context is referring to but it does show that \b is interpreted differently under different contexts. PostgreSQL may not have a way (or at least a very good way) to make this distinction. Any other thoughts?== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] Way to reverse ordering of an IP ... ?
for Reverse DNS purposes, I'd like to reverse an IP ... ie: 200.46.204.1 would become 1.204.46.200 Is there an easy way of doing this *short* of writing a plpgsql function? I've checked the docs, and found the substring() function that allows for using a regex, which I thought might allow this, but can't seem to figure out a proper format for it ;( If I have to write a function to do it, fine ... just wanted to make sure I wasn't missing something first ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(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
[SQL] Query regarding to MS reporting services (Grand total problem)
Hi, I have a problem reagarding Grand Total in the table footer. I am showing only top 10 seeling for each department in report but i want sum of whole table seelings in table footer. not only 10 sellings which are shown in report. I want the sum of all 40 thousand rows. How i can do it. If any one have idea about this please help me. Thanks in advance. Regards Dinesh Tiwari How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Re: [SQL] Way to reverse ordering of an IP ... ?
[EMAIL PROTECTED] wrote: > for Reverse DNS purposes, I'd like to reverse an IP ... > > ie: 200.46.204.1 would become 1.204.46.200 > > Is there an easy way of doing this *short* of writing a > plpgsql function? > > I've checked the docs, and found the substring() function > that allows for using a regex, which I thought might allow > this, but can't seem to figure out a proper format for it ;( > > If I have to write a function to do it, fine ... just wanted > to make sure I wasn't missing something first ... Hi, You can use (\d+)\.(\d+)\.(\d+)\.(\d+) for the match, and \4.\3.\2.\1 for the replacement in your Regexp. Cheers, -- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76 smime.p7s Description: S/MIME cryptographic signature
