Thread: Problem with pgadmin II and psql

Problem with pgadmin II and psql

From
ROUWEZ Stephane
Date:
Hi,

First I create a database in psql, not in PgAdmin.
After, when I create a table in PgAdmin it's not accessible in psql but if I
create it in psql, it works, it's accessible in PgAdmin but not modifiable
(I can't change the column name or drop the column).
When I create a table or a column with PGAdmin, I must always use the quoted
delimiters to access it the table and the columns.

I don't find an option who can be changed to say to PgAdmin that it don't
use the double-quote.

Does anybody have an idea ?

Stef


------------------------------
http://www.ecolo.be


Re: Problem with pgadmin II and psql

From
"Markus Brachner"
Date:
You probably used CAPITALS - I also had this problem - it's not a bug, it's
a feature ;)
I would appreciate PgAdmin using the non-quoted mode for creating objects -
or at least be user configurable, because this non SQL conformant feature
confuses many users (I think).

Best regards,
Maxx

-----Ursprüngliche Nachricht-----
Von: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org]Im Auftrag von ROUWEZ
Stephane
Gesendet: Donnerstag, 25. Juli 2002 13:42
An: 'pgadmin-support@postgresql.org'
Cc: LESNE Philippe
Betreff: [pgadmin-support] Problem with pgadmin II and psql


Hi,

First I create a database in psql, not in PgAdmin.
After, when I create a table in PgAdmin it's not accessible in psql but if I
create it in psql, it works, it's accessible in PgAdmin but not modifiable
(I can't change the column name or drop the column).
When I create a table or a column with PGAdmin, I must always use the quoted
delimiters to access it the table and the columns.

I don't find an option who can be changed to say to PgAdmin that it don't
use the double-quote.

Does anybody have an idea ?

Stef


------------------------------
http://www.ecolo.be


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Re: Problem with pgadmin II and psql

From
"Dave Page"
Date:

> -----Original Message-----
> From: ROUWEZ Stephane [mailto:stephane.rouwez@ecolo.be]
> Sent: 25 July 2002 12:42
> To: pgadmin-support@postgresql.org
> Cc: LESNE Philippe
> Subject: [pgadmin-support] Problem with pgadmin II and psql
>
>
> Hi,
>
> First I create a database in psql, not in PgAdmin.
> After, when I create a table in PgAdmin it's not accessible
> in psql but if I create it in psql, it works,

pgAdmin will quote the identifiers in case you use mixed case (1.3.60
will only quote them if required), psql will not. So, if you create a
table called MyTable in pgAdmin, from psql you must manually quote:

SELECT * FROM "MyTable"

The easiest option is to use lowercase names with no special characters
other than _ in them.

> it's accessible
> in PgAdmin but not modifiable (I can't change the column name
> or drop the column).

You can rename columns by selecting the Column properties from the
treeview, *not* in the Table properties.

PostgreSQL cannot yet drop columns, hence neither can pgAdmin.

> When I create a table or a column with
> PGAdmin, I must always use the quoted delimiters to access it
> the table and the columns.
>
> I don't find an option who can be changed to say to PgAdmin
> that it don't use the double-quote.

If you use mixed case names, they *must* be quoted. Stick to lower case
and you can lose the quotes.

Regards, Dave.

Re: Problem with pgadmin II and psql

From
"Dave Page"
Date:

> -----Original Message-----
> From: Markus Brachner [mailto:m.brachner@screensavergold.com]
> Sent: 25 July 2002 12:58
> To: ROUWEZ Stephane; pgadmin-support@postgresql.org
> Cc: LESNE Philippe
> Subject: Re: [pgadmin-support] Problem with pgadmin II and psql
>
>
> You probably used CAPITALS - I also had this problem - it's
> not a bug, it's a feature ;) I would appreciate PgAdmin using
> the non-quoted mode for creating objects - or at least be
> user configurable,

Current development versions of pgAdmin will only use quotes where
required, though this still leaves you with the "problem" that if you
create a table called MyTable it is MyTable and not mytable.

> because this non SQL conformant feature
> confuses many users (I think).

If PostgreSQL (note, *not* pgAdmin) followed the spec, then this problem
would still remain. To quote from the spec, and Tom Lane:

>>>

         13)A <regular identifier> and a <delimited identifier> are
equiva-
            lent if the <identifier body> of the <regular identifier>
(with
            every letter that is a lower-case letter replaced by the
equiva-
            lent upper-case letter or letters) and the <delimited
identifier
            body> of the <delimited identifier> (with all occurrences of
            <quote> replaced by <quote symbol> and all occurrences of
<dou-
            blequote symbol> replaced by <double quote>), considered as
            the repetition of a <character string literal> that
specifies a
            <character set specification> of SQL_TEXT and an
implementation-
            defined collation that is sensitive to case, compare equally
            according to the comparison rules in Subclause 8.2,
"<comparison
            predicate>".

The spec expects unquoted identifiers to be made case-insensitive by
folding them to upper case.  We do it by folding to lower case, instead.
While this isn't 100% standard, it's unlikely to be changed.  Too many
applications would break...

>>>

In other words, you would still get the case where MyTable != mytable !=
MYTABLE.

Regards, Dave.