Re: Postgres : Queries

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