Thread: Probably a stupid question

Hi all, and tx for all the help you give to the community
I have a question, really stupid, but i'd like to understand the postgres
behavior.
I installed dbmirror, and there is a file script creating a table, something
like this:
CREATE TABLE "MirrorHosts" ..... etc etc

Ok, no prob with the syntax (3 years i use postgreSQL), but when i use psql
i have this problems:
1) the command \d MirrorHosts doesn't work (table MirrorHost not found),
   i have to use \d "MirrorHosts"
2) when i do SELECT * FROM MirrorHosts;   (the same prob)
   i have to use "MirrorHosts"

I'd like to know if there is a way to bypass this problem.
Tx in advance and ..... PostgreSQL is really GREAT!

Giorgio Ponza

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1




> CREATE TABLE "MirrorHosts" ..... etc etc
>
> 1) the command \d MirrorHosts doesn't work (table MirrorHost not found),
>    i have to use \d "MirrorHosts"
> 2) when i do SELECT * FROM MirrorHosts;   (the same prob)
>    i have to use "MirrorHosts"
>
> I'd like to know if there is a way to bypass this problem.
> Tx in advance and ..... PostgreSQL is really GREAT!

When you created the table using the quotes, it forced the name to use
mixed-case. Therefore, you must use the exact same case afterwards.
The simple way to get around this is to not use quotes around the
tablename, or simply lowercase the name in your initial create:

CREATE TABLE MirrorHosts ...

or

CREATE TABLE "mirrorhosts"...


If you have already created the "MirrorHosts" table and do not want to
lose the data inside it, you could do this:

CREATE TABLE mirrorhosts AS SELECT * FROM mirrorhosts;

DROP TABLE mirrorhosts;

(this assumes the table is simple and has no foreign keys, triggers, etc.)


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200306270912

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+/ERSvJuQZxSWSsgRAqSRAJ9Te5Nln3IQGoXSA/OAoT+z7sLw/ACfXjXu
ZbXB5apFwC0OiOJrFomR57g=
=noEp
-----END PGP SIGNATURE-----

greg@turnstep.com wrote:

> ...
>
>
>If you have already created the "MirrorHosts" table and do not want to
>lose the data inside it, you could do this:
>
>CREATE TABLE mirrorhosts AS SELECT * FROM mirrorhosts;
>
>DROP TABLE mirrorhosts;
>
>
>
Shouldn't this be


CREATE TABLE mirrorhosts AS SELECT * FROM "MirrorHosts";

DROP TABLE "MirrorHosts";

?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



> Shouldn't this be
>
> CREATE TABLE mirrorhosts AS SELECT * FROM "MirrorHosts";
>
> DROP TABLE "MirrorHosts";

Yes it should. Dani Oderbolz is correct, my previous example is wrong.
Sorry about that.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200306270930
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+/EeZvJuQZxSWSsgRAhtjAKDJKpgfs3c47IuCFsHlJd+klu6bHACeNGi+
jmidq8oFr7FJZZL132DrCVc=
=u1Xk
-----END PGP SIGNATURE-----

Am Fre, 2003-06-27 um 15.29 schrieb Dani Oderbolz:
> greg@turnstep.com wrote:
>
> > ...
> >
> >
> >If you have already created the "MirrorHosts" table and do not want to
> >lose the data inside it, you could do this:
> >
> >CREATE TABLE mirrorhosts AS SELECT * FROM mirrorhosts

ALTER TABLE products RENAME TO items;

At least with 7.3.2

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Am Fre, 2003-06-27 um 15.29 schrieb Dani Oderbolz:
> greg@turnstep.com wrote:
>
> > ...
> >
> >
> >If you have already created the "MirrorHosts" table and do not want to
> >lose the data inside it, you could do this:
> >
> >CREATE TABLE mirrorhosts AS SELECT * FROM mirrorhosts

ALTER TABLE products RENAME TO items;

At least with 7.3.2

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de