Thread: PgAdmin problem

Thanks very much Frank, I will try what you suggested. 

1. Will this also be true for column names. So, if I have a column named
Last_Name, should I use SELECT "Last_Name" from "Members";

Do you then recommend that all tables and views be labeled in lowercase, so
that all queries will be consistent.

2. On another note, is there a tool like the MS Access front end that lets
you create constraints graphically by simply dragging and dropping columns
between tables and views by simply dragging columns from the respective
tables into the design window. 

3. Also, updatable views would make Postgresql so much easier to use.

The reason I ask is that if that were the case, PostgreSQL would be
enormously popular beyond expectations. At the very least, introductory
database courses in business schools could so easily use PostgreSQL instead
of MS Access.

Thanks
Manish

_________________________________
Manish Agrawal
Asstt. Prof., Dept. of ISDS, Univ. of South Florida
4202 E. Fowler Ave., CIS 1040, Tampa, Fl 33620 - 7800
(813)-974-6716 (W) 974-6749 (FAX)
magrawal@coba.usf.edu
______________________________________
My default office suite is Openoffice, the simple-to-use, opensource Office
Suite for Windows, Linux and Macs. http://www.openoffice.org

-----Original Message-----
From: Frankie Lam [mailto:frankie@ucr.com.hk] 
Sent: Tuesday, February 18, 2003 8:52 PM
To: Agrawal, Manish
Subject: Re: [INTERFACES] PgAdmin problem

Hello Manish,

try 'DROP VIEW "qMembers"'.

Unlike MySQL, Postgres fold all table and column names to lowercase except
if the identifier is enclosed in quotes then the case is preserved. But don
't get it wrong that PostgreSQL is a case insensitive, "SELECT 'apple'=
'Apple'" will still return a false value.

Thus it is possible to have three tables all named test but differing in
their case(not suggest you to do so):

"TEST"
"Test"
"test"

can all exist at the same time. However to reference TEST or Test
the identifier would need to be quoted (i.e. select * from "TEST").
All unquoted references are folded to lowercase.

Regards, Frankie

----- Original Message -----
From: "Agrawal, Manish" <MAgrawal@coba.usf.edu>
To: <pgsql-interfaces@postgresql.org>
Cc: <pgsql-cygwin@postgresql.org>
Sent: Wednesday, February 19, 2003 7:25 AM
Subject: [INTERFACES] PgAdmin problem


> I have a peculiar problem with PgAdmin. I converted an Access database
using
> pgMigration in pgadmin. I then created a view from within pgadmin.
>
>
>
> But though this view is available as an object in the pgsql database, I am
> unable to use it in an application. It also appears to be unavailable from
> psql.
>
> I have a link to a screenshot that shows what is happening. qMembers is
the
> view, I can see it by using \d in psql but I cannot drop the view.
>
>
>
> http://faculty94100.coba.usf.edu:8081/view_problem.bmp
> <http://faculty94100.coba.usf.edu:8081/view_problem.bmp>
>
>
>
> Can anyone help me with this issue. This problem is preventing me from
being
> able to use pgadmin to create views.
>
>
>
> Thanks
>
> Manish
>
> _________________________________
>
> Manish <http://coba.usf.edu/departments/isds/faculty/agrawal/index.html>
> Agrawal
>
> Asstt. Prof., Dept. of ISDS, Univ. of South Florida
>
> 4202 E. Fowler Ave., CIS 1040, Tampa, Fl 33620 - 7800
>
> (813)-974-6716 (W) 974-6749 (FAX)
>
> magrawal@coba.usf.edu <mailto:magrawal@coba.usf.edu>
>
> ______________________________________
>
> My default office suite is Openoffice <http://www.openoffice.org> , the
> simple-to-use, opensource Office Suite for Windows, Linux and Macs.
> http://www.openoffice.org <http://www.openoffice.org>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On Wed, 19 Feb 2003, Agrawal, Manish wrote:

> 3. Also, updatable views would make Postgresql so much easier to use.
> 
> The reason I ask is that if that were the case, PostgreSQL would be
> enormously popular beyond expectations. At the very least, introductory
> database courses in business schools could so easily use PostgreSQL instead
> of MS Access.

Views are updateable. You need to
CREATE RULE blah AS ON UPDATE TO myview DO INSTEAD  UPDATE mytable SET acol = NEW.acol

for example.

I think there are other issues with updateable views from MSAccess though.


--
Nigel J. Andrews

Agrawal, Manish wrote:

> 2. On another note, is there a tool like the MS Access front end that lets
> you create constraints graphically by simply dragging and dropping columns
> between tables and views by simply dragging columns from the respective
> tables into the design window. 

You might be pleased wit OpenOffice : The 643 version, with the newer 
("snapshot") ODBC drivers by Hiroshi Inoue is quite efficient at emulating 
the "query" interface of MS-Access.

But keep in mind that MS-Access (and OO's) abilities are limited in this 
respect. For one thing, they won't accept subselects as column sources. If 
you need that, you have to define a view, and use it as a column source, or 
define your query/view in server-side SQL (you may fool around with cut and 
paste and many "query definition" windows.

One thing that you won't be able to do with OOo is to define 
crosstabulation queries/views. Fortunately, the spreadsheet allows you to 
define "data pilot" zones that give you "almost the same" functionality, as 
far as you don't need crosstab *views*.

I think that in 7.3, one may define a function writind dynamycally a big 
"left outer join" query and returning the corresponding set of rows, which 
would be a tad closer. But what's the point ?

> 3. Also, updatable views would make Postgresql so much easier to use.

You may do that, but yu'll have to define the update function by yourself. 
The reason is that determining what should be the "update" rule is 
intrinsically *hard* in the general case. See "The rule system" chapter and 
following in the Postgres server programming manual.

The limited abilities of MS-Acces views (no subselects, for example) makes 
easier the building of a "reasonable" update rule. No such luck in Postgres !

> The reason I ask is that if that were the case, PostgreSQL would be
> enormously popular beyond expectations. At the very least, introductory
> database courses in business schools could so easily use PostgreSQL instead
> of MS Access.

<Rant>

Huh ?

The only reasonable database systems course in a buisness school should be 
given with a M-16 ... ;-] More seriously : any "manager" I've seen trying 
to tackle a database problem ended up with his/her head so up his/her a*se 
that one had to call for a *thoracic* surgeon.

Every time.

The reason seems to be that they seem to be allergic to Codd's algebra (or 
any kind of algebra past elementary linear algebra used in accounting and 
elementary calculus). Even physicians, of all types, seem more suited to 
database design and use ...

</Rant>

Hope this helps,
                Emmanuel Charpentier

-- 
Emmanuel Charpentier