[GENERAL] Wanted: Trick for granting permissions to users with hyphens
Hello I like to grant some permissions to the user www-data (default user for apache under debian) GRANT SELECT, UPDATE, INSERT, DELETE ON customer TO herbie, www-data; The statements throws the following error: ERROR: parser: parse error at or near - at character 64 Seems to me that hyphens are not allowed for user names. But a »createuser www-data« is working correct an I can access the database with that user. I tried to quote the www-data with 'www-data', www\-data with the same result. Any hints? I'm using 7.3.2 Thanks and best regards Herbie -- Herbert Liechti http://www.thinx.ch The content management company. Visit http://www.contentx.ch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Storing a chain
Am Mittwoch, 19. November 2003 16.08 schrieben Sie: Hi, I'm seeking some opinion on the following problem May be this article is interesting for you: http://www.dbmsmag.com/9603d06.html Best regards Herbie -- Herbert Liechti http://www.thinx.ch The content management company. Visit http://www.contentx.ch ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] prefer (+) oracle notation
Tom Lane wrote: "Edmar Wiggers" [EMAIL PROTECTED] writes: I'm not sure about the standard, but I really like Oracle's notation for foreign keys: select a.item_number, b.group_code_description from items a, group_codes b where a.group_code = b.group_code (+); I beg to differ --- IMHO, Oracle's notation is brain dead. I agree. In my opinion the best syntax for outer joins was brought up by informix. Ex: SELECT a.f1, b.f1, c.f1 FROM a, OUTER( b, OUTER c ) WHERE a.key = b.fkkey AND b.key = c.fkkey Where b is outer from a and c outer from b. Precedence and hierachical order is given by the parenthesis. Same example FROM a, OUTER ( b, c) b and c are outer from a. I find this syntax clear and logic without any danger of missinterpretation Best regards Herbie
Re: [GENERAL] How I can undelete recodes?
[EMAIL PROTECTED] wrote: How I can recover this table. Please help me. Put in your backup tape and restore. Unfortunately, I have not the backup tape. :( see http://www.rocksoft.com/taobackup/ my heartfelt sympathy! -- Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] postgres perl DBI
"Fetter, David M" wrote: I'm having issues with installing the postgres DBI for perl. Here is the output of make test and a verification postmaster is running: [~/DBD-Pg-0.95] dmfetter@mwabs505! make test PERL_DL_NONLAZY=1 /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.00503/sun4-solaris -I/usr/local/lib/perl5/5.00503 test.pl OS: solaris Use of uninitialized value at test.pl line 53. DBI-data_sources .. not ok: DBI-connect(dbname=template1) failed: PQconnectPoll() -- connect() failed: Connection refused Is the postmaster running (with -i) at 'mwabs504' and accepting connections on TCP/IP port '5432'? at test.pl line 59 Is the postmaster running? Do you run the make test routine with a user who can access the database. Try the same with the postgres user. I usually do only the make install step as root. Regards Herbie -- ~~~~~~~~ Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] How to dump from Postgre
Thomas Lockhart wrote: Yeah, it just vanished. Not my fault. The bitbucket ate it. And what is a bitbucket? My dictioniary knows nothing about this word. Just for all the people who's mother tongue is not english. :-) - Herbie
Re: [GENERAL] Lock rekord
"Pawe³ Dubin" wrote: Hello I was listening Your discusion. I have practical problem for which I write my own locking system: Now A change zip and write all B change adress and write so zip is unchanged. In pgsql I can solve it by SELECT FOR UPDATE but if user A goes for caffe during his update user B hangs for several minutes ... Ther is no solution to check if record is in transaction ? I usually prefer the following trick for preventing long locking times. On every table I define a timestamp field which is updated every time the record is written to the database. If a user edits a record (without locking) and commit his changes the timestamp is returned from the client program unchanged. The program reads the record again for update and compares the timestamp from the database and the timestamp from the user program. If the timestamp has changed in the meantime the record was updated from someone else and the transaction will be rejected. Regards Herbie -- ~~~~~~~~ Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] postgresql and perl?
Peter Landis wrote: How do you sort by the variable? $sqh = $dbh-prepare(q{select name from company order by $sort_selection;}); $sqh-execute(); Your problem is not Perl specific. The order by column must be in the result set (projection). Why not doing it this way: my @fields = qw/name zip location/; push @fields, $sort_selection; $sqh = $dbh-prepare( "SELECT " . join(', ', @fields) . " FROM company ORDER BY $sort_selction; ); $sqh-execute(); Greetings Herbie -- ~~~~~~~~ Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] Accelerating subqueries
Fabrice Scemama wrote: Hi fellow PostgreSQL users. Here's a query: DELETE FROM Annonces WHERE Id_Ag IN ( SELECT Id FROM Installs WHERE Si_Compte_Actif = 'f' ); The IN Clause is known to be very very slow. Use the EXISTS Clause instead. Regards Herbie -- Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] Empty String Field
Robert Esser wrote: How can I check an empty string field with a query? If I make a select ... where fieldname ='' then I get no result!! What is the right way? If you mean a field with a null value (nichts drin) then use where fieldname IS NULL Mit freundlichen Grüßen / Best regards Auch einen Gruss /Greetings Herbie -- Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] PostgreSQL 6.5.3 on SuSE 6.1
Hi list members: I am currently using PostgreSQL 6.4.2 on SuSE 6.1. This version came with the SuSE disks. Recently, I downloaded and compiled the source from the pgsql ftp site, but postgres does not run and says that a file called libreadline.so is needed to run. This file is not on my computer. Is there a way to make postgres run without this file on this distribution?. Are there RPMs for SuSE 6.1? Any help is appreciated. I setted up postgres 6.5.2 on Suse 6.1 without problems. Here my configuration options: ./configure --prefix=/home/db/postgres/ --with-template=linux_i386 \ --enable-locale After compiling postgres you need to edit /etc/ld.so.conf. With the above configuration I added the following line to ld.so.conf: /home/db/postgres/lib Run /sbin/ldconfig after editing ld.so.conf. Of course you have to set up you environment correctly as described in the INSTALL doucment of postgres: PATH=$PATH:/home/db/postgres/bin MANPATH=$MANPATH:/home/db/postgres/man PGLIB=/home/db/postgres/lib PGDATA=/home/db/postgres/data export PATH MANPATH PGLIB PGDATA Greetings Herbie -- Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
Re: [GENERAL] null and =
Slavica Stefic wrote: is null = null true or also null? A null value represents either the absence of data or an unknown value. A null value is not the same as a zero or a blank. Thus two tuples with null values are never the same and can not be joined. it appears that two null records are not equal if they are both null. First question: is this correct? is this SQL conformant? yes 2: if I change the sources for the operator to compare nulls as they where values will it have too many side effects? Don't do that. You will get a desaster ;-) Herbie -- Herbert Liechti E-Mail: [EMAIL PROTECTED] ThinX networked business servicesStahlrain 10, CH-5200 Brugg
[GENERAL] sql question
I have a sql problem which I can't solve. The following table is defined create table AdressGroup ( AdrGroup_Id INTEGER NOT NULL DEFAULT NEXTVAL('adrverw_id_seq'), ZeitDATETIME NOT NULL, Group_IdINTEGER NOT NULL, Adress_Id INTEGER NOT NULL ); The table assigns adresses to adressgroups. I'd like to select the adresses which belongs to one specific adressGroup and to no other group. If an adress has more than one entry in the AdressGroup table it should not be in the projection. I tried the following: SELECT * FROM adressGroup WHERE Group_Id = 6 AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id GROUP BY AdrGroup_Id HAVING COUNT(*) == 1 ); When I do this I get the following error: ERROR: rewrite: aggregate column of view must be at rigth side in qual ERROR: rewrite: aggregate column of view must be at rigth side in qual Does anybody knows the solution? Thanks -- ~~~ Herbert LiechtiE-Mail: [EMAIL PROTECTED] ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~
Re: [GENERAL] next steps
Carsten Huettl wrote: Hello, I have set up a postgres installation on a freeBSD box and am now ready to take the next steps. Where can I find some Webpages with postgres applications or sample. How do I create a "user front end"/data input mask/menu system? Can anyone point me to a webside to learn more about postgres (tutorial)? We made a framework to build easily a web front end for a postgres database. The framework is completely written in Perl. It takes some 10 minutes for each table to define the attribute and how they should be displayed in the browser. Easy addition of all constraints (parent / child tables) to enforce referential integrity and automatical lookups in parent is supported. The framework is complete and documented(a real tutorial is missing). If you are interested in that framework contact me. We are planning to license the framework under GPL. The framework should be available on our website in the end of november and hopefully in the CPAN archive. Greetings Herbie -- ~~~~~~~ Herbert LiechtiE-Mail: [EMAIL PROTECTED] ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~
[GENERAL] urgent: problems with query_limit
Hi We upgraded our system from 6.4 to the new 6.5 version. The set query_limit function is not working anymore in 6.5. db = set query_limit to '100'; SET VARIABLE db = select * from table; statement is returning all records from the table. What's wrong here? Herbie -- ~~~ Herbert LiechtiE-Mail: [EMAIL PROTECTED] ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~
Re: [GENERAL] 6.4.2 and 6.5 date usertype difference
"Colin Price (EML)" wrote: I have just replicated a db in 6.5 from 6.4.2. The table selects the date differently from the identical table. I obviously set a parameter in 6.4.2 which I now cannot find (from mail list archive and doc as well). Please aid someone who is tearing his hair out. Thank you in advance, Colin. I think you should set the datestyle environment variable $PGDATESTYLE or executing the sql statement SET DateStyle TO 'whateverYouNeed'. For further information see the Reference manual in chapter Data Types. You will find in the section Date/Time Types an explanation how to use the variable or sql statement. Greetings Herbie -- ~~~~~~~ Herbert LiechtiE-Mail: [EMAIL PROTECTED] ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~