Обсуждение: 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