Thread: Postgres : Queries
Dear Sir/ Madam, I have been facing some problem when using Postgres. I am using Postgres 7.2 beta release 4. The reason I am using this is that it was easy to install in windows 2000 and I was also able to get a download of the interface from Peerdirect. The problems that I am facing are as follows :- 1. Using the psql or the aqua Query analyser, if I create a table or an object, it always creates it in lower case, no matter what case I specify. but I tried porting a table from sql server 7 to postgres using DTS, and the case for the table was the one that I had specified in SQL Server. But now the problem is that I am not being able to accessthis table as the case is different. And when ever I try to access the table, it tries to fnid one in lower case and does not consider the case that I specify in the select statement. 2. I am working in a particular database catalog and need to execute a query on a table in another DB. How can I do this without having to create a fresh connection. 3. I had created a Function and now I need to view the description of the function. but I have not come across anything on the net or the help using which I could view the contents, like we have the sp_helptext in SQL server. 4. In SQL we have the concept of clustered and nonclustered indexes. but I was not able to find any information on clustered indexes nor any details that the index that are created are of type clustered or nonclustered. Can you kindly help me out these these Queries. I have been searching for the solutions on the net for a while now and still no results and I need to find the solutions soon. Thank you in advance for your help. Thank you, Jyoti Manchanda _________________________________________________________________ Xerox Multifunction devices�that print,copy,scan and fax. http://go.msnserver.com/IN/44797.asp Get affordable printing solutions that fit your needs .
Hello Jyoti Manchanda, On 19.03.2004 at 2:22pm jyoti manchanda wrote: > I have been facing some problem when using Postgres. I am using > Postgres 7.2 beta release 4. The reason I am using this is that it was > easy to install in windows 2000 and I was also able to get a download > of the interface from Peerdirect. The problems that I am facing are as > follows :- > > 1. Using the psql or the aqua Query analyser, if I create a table or > an object, it always creates it in lower case, no matter what case I > specify. Did you try enclosing the table name in double quotes: create table "MyMultiCaseTableName" ... select ... from "MyMultiCaseTableName" ... This makes SQL coding generally a bit more tedious (so it's a good idea to have it all lowercase), but as far as I recall that complies to the spec. > 2. I am working in a particular database catalog and need to execute a > query on a table in another DB. How can I do this without having to > create a fresh connection. You cannot. What's wrong with having another connection? > 3. I had created a Function and now I need to view the description of > the function. but I have not come across anything on the net or the > help using which I could view the contents, like we have the > sp_helptext in SQL server. Have you tried PG-Admin? It's free, it's multi-platform, it lives here: http://www.pgadmin.org/ With psql, try this query: select proname, prosrc from pg_catalog.pg_proc where proname = "function_name"; > 4. In SQL we have the concept of clustered and nonclustered indexes. > but I was not able to find any information on clustered indexes nor > any details that the index that are created are of type clustered or > nonclustered. I'm not sure if I understand this concept of clustered indexes, but as far as I know Postgres supports clustering your data in index order, that is the on-disk order of the tables rows is brought into index order (and the commands name is 'cluster'). It also supports indexes which cover only a part of the table by specifying a where clause on index creation, like if you want to exclude all the rows where the index field is NULL from the index. Hope that helps a bit, Best regards, Christof Glaser -- -- gl.aser . software engineering . internet service -- Doelitzer Strasse 37 . D-04277 Leipzig . fon +49-341-303 20 51 -- http://gl.aser.de/ . sms +49-177-779 28 43 . fax +49-0341-303 20 52
Hi Jyoti, [also posted to the list] On 08.04.2004 at 15:08 J M wrote: > I have created some users without any password and granted them > permissions for the various DB's and the tables. > > after that i have modified the password of the users usign the ALter > Table Command. but even after this I am still being able to logon > without specifying the password, though when I executed the Alter > table command, success was returned. Login authentication is set in the file pg_hba.conf. Probably the password authentication is switched off (ie authentication mode is "trust") for users from local host. So something like this will force password checks: # Host Database Users authentication mode local mydb all passwd There might be a line like: local all all trust which should stay intact. It must come after the line above, as Postgres goes through the file line by line and stops on first match for host/database/user. This basically means that more specific lines must come first to have any effect. See also the docs on authentication, as well as the comments in pg_hba.conf. There are a lot of options, allowing very fine grained access control. > Can you help me on this. As a reminder I am working on the version 7.2 > of postgres. > > Thank you in advance, > Jyoti Best regards, Christof -- -- gl.aser . software engineering . internet service -- Doelitzer Strasse 37 . D-04277 Leipzig . fon +49-341-303 20 51 -- http://gl.aser.de/ . sms +49-177-779 28 43 . fax +49-0341-303 20 52