Thread: Without schemas
I made a mistake and sent my post personally to Chander Ganesan, here it is:
Have you used PHP with PostgreSQL?? (Especifically the Doctrine
Framework??). Because my question arises from the fact that I've had
problems when rying to use Doctrine with a PostgreSQL database and I thought
that the reason could be the lack of the schema name. However, according to
your answer, it is not necessary to put the schema name before the table's
(when it comes to the public schema). I'm trying some code right now and
I've figured out that it's not the same to use 'SELECT NICK FROM USUARIOS'
vs. 'SELECT "Nick" from "Usuarios"', it only works the second way. So, my
problem could have something to do with it??
Framework??). Because my question arises from the fact that I've had
problems when rying to use Doctrine with a PostgreSQL database and I thought
that the reason could be the lack of the schema name. However, according to
your answer, it is not necessary to put the schema name before the table's
(when it comes to the public schema). I'm trying some code right now and
I've figured out that it's not the same to use 'SELECT NICK FROM USUARIOS'
vs. 'SELECT "Nick" from "Usuarios"', it only works the second way. So, my
problem could have something to do with it??
Félix Sánchez Rodríguez wrote: > Have you used PHP with PostgreSQL?? Yep - daily. :) > (Especifically the Doctrine > Framework??). Not familiar with that one. I don't have a framework (well, I do, but I wrote it), but I use PDO (PHP Data Objects) as the interface. > Because my question arises from the fact that I've had > problems when rying to use Doctrine with a PostgreSQL database and I > thought > that the reason could be the lack of the schema name. However, > according to > your answer, it is not necessary to put the schema name before the > table's > (when it comes to the public schema). I'm trying some code right now and > I've figured out that it's not the same to use 'SELECT NICK FROM > USUARIOS' > vs. 'SELECT "Nick" from "Usuarios"', it only works the second way. So, my > problem could have something to do with it?? It's been my experience that just leaving the name out and not quoting in the SQL is the most straightforward way to go. I always use lowercase (in table definition and in the code) which seems to work well. Also, with schemas, the "search path" defaults to the user name, then public. The way I have coded my applications is to have a service user who owns the schema, and that's the user that the application uses. If you have a similar setup, that may work - if not, public is your best option. My advice would be to get it working with no punctuation in the SQL (i.e., "select nick from usarios"), and set the rest of the tables up that way. -- Daniel J. Summers *Owner, DJS Consulting* Support <http://support.djs-consulting.com/> • Tech Blog <http://www.djs-consulting.com/linux/blog> daniel@djs-consulting.com <mailto:daniel@djs-consulting.com> • http://www.djs-consulting.com <http://www.djs-consulting.com/> GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w !O M-- V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e h---- r+++ y++++
>>> Félix Sánchez Rodríguez <fesanch@ciego.cult.cu> wrote: > I've figured out that it's not the same to use 'SELECT NICK FROM > USUARIOS' vs. 'SELECT "Nick" from "Usuarios"' In PostgreSQL the first is interpreted the same as: SELECT "nick" from "usuarios"; Note that this is different from the ANSI & ISO standards, which require it to be interpreted as: SELECT "NICK" FROM "USUARIOS"; Neither of the above would find a column "Nick" or a table "Usuarios". For maximum portability, you might want to always quote your identifiers, regardless of whether they are all uppercase, all lowercase, or mixed case. An alternative, which has worked for some, is to never quote your identifiers and always use lower case. I've seen software which breaks without the quotes on either capitalization, but the lowercase is currently more popular in more quarters, and seems to break less often. It's certainly safe it you're planning to target PostgreSQL and don't care about portability. -Kevin
Thanks to all for your answers, I converted my tables and fields to lowercase and now everything works perfectly. ----- Original Message ----- From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov> To: "Félix Sánchez Rodríguez" <fesanch@ciego.cult.cu>; <pgsql-admin@postgresql.org> Sent: Thursday, April 02, 2009 12:51 AM Subject: [?? Probable Spam] Re: [ADMIN] Without schemas >>> Félix Sánchez Rodríguez <fesanch@ciego.cult.cu> wrote: > I've figured out that it's not the same to use 'SELECT NICK FROM > USUARIOS' vs. 'SELECT "Nick" from "Usuarios"' In PostgreSQL the first is interpreted the same as: SELECT "nick" from "usuarios"; Note that this is different from the ANSI & ISO standards, which require it to be interpreted as: SELECT "NICK" FROM "USUARIOS"; Neither of the above would find a column "Nick" or a table "Usuarios". For maximum portability, you might want to always quote your identifiers, regardless of whether they are all uppercase, all lowercase, or mixed case. An alternative, which has worked for some, is to never quote your identifiers and always use lower case. I've seen software which breaks without the quotes on either capitalization, but the lowercase is currently more popular in more quarters, and seems to break less often. It's certainly safe it you're planning to target PostgreSQL and don't care about portability. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin