Thread: [MASSMAIL]How to reference a DB with a period in its name ?
Hi:
I have a DB named "thedb", which has a schema called "theschem" which has a table called "thetab". In a perl/DBI script, I am able to work with the table by referencing it as... "thedb.theschem.thetab" as in... "delete from thedb.theschem.thetab where col1=1"
No problem (so far...)
New DB now has a name "thedb.v1" (notice the "."), the schem and table are the same as above. But now I'm getting a syntax error presumably because of that "." introduced in the DB name.
I tried encapsulating the DB name in double quotes (no good), single quotes (still no good) escaping with '\' (no good), escaping with ".." (no good).
Is there a way I can reference the DB which has the "." in it's name ?
v11.5 on linux (I know, way past time to upgrade)
Thanks in Advance !
v11.5 on linux (I know, way past time to upgrade)
Thanks in Advance !
On Fri, Mar 29, 2024 at 2:16 PM David Gauthier <dfgpostgres@gmail.com> wrote:
I tried encapsulating the DB name in double quotes (no good)
This is what the documentation says you are supposed to do for non-simple identifiers so you need to show your work to understand where you went wrong.
David J.
On Fri, Mar 29, 2024 at 2:20 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Mar 29, 2024 at 2:16 PM David Gauthier <dfgpostgres@gmail.com> wrote:I tried encapsulating the DB name in double quotes (no good)This is what the documentation says you are supposed to do for non-simple identifiers so you need to show your work to understand where you went wrong.
Actually, you really aren't supposed to put the database name in there at all; it's pointless since there is no ability to reference a database other than the one you are connected to.
David J.
> On Mar 29, 2024, at 14:16, David Gauthier <dfgpostgres@gmail.com> wrote
> I tried encapsulating the DB name in double quotes (no good), single quotes (still no good) escaping with '\' (no
good),escaping with ".." (no good).
This is probably more about the string handling in the API you are using than PostgreSQL per se, because enclosing the
individualcomponents in double quotes does indeed work:
x.y=# select * from "x.y".s.t;
i
---
(0 rows)
Ya, I kind of agree on the >1 DB connections not allowed. It (perl/DBI) does allow for >1 active DB handles (objects). But of course those handles/objects have different names and that's how to work with the different ones (not a DB prefix like what I have). Still, it accepted the name in the syntax if it didn't have a ".". I kinda liked the db prefix because I'm doing these sweeping deletes and I wanted to make extra-sure that I was deleting from the correct DB.
I'm going to ask them to just create another DB without the "." in the name. All the code has the prefix and I don't want to uproot that (because it's working).
I'm going to ask them to just create another DB without the "." in the name. All the code has the prefix and I don't want to uproot that (because it's working).
Thanks David !
On Fri, Mar 29, 2024 at 5:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Mar 29, 2024 at 2:20 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Fri, Mar 29, 2024 at 2:16 PM David Gauthier <dfgpostgres@gmail.com> wrote:I tried encapsulating the DB name in double quotes (no good)This is what the documentation says you are supposed to do for non-simple identifiers so you need to show your work to understand where you went wrong.Actually, you really aren't supposed to put the database name in there at all; it's pointless since there is no ability to reference a database other than the one you are connected to.David J.
Hi:
I think your problem is in the Perl side more than in PostgreSQL's. When you said you tried escaping with \, Did you mean exactly this?
"delete from \"thedb.v1\".theschem.thetab where col1 = 1"
I think this should work.
Or you can use single quotes to enclose the query, so that you don't need to escape the double quotes:
'delete from "thedb.v1".theschem.thetab where col1 = 1'
Best regards.
On 29/03/2024 22:16, David Gauthier wrote:
Hi:I have a DB named "thedb", which has a schema called "theschem" which has a table called "thetab". In a perl/DBI script, I am able to work with the table by referencing it as... "thedb.theschem.thetab" as in... "delete from thedb.theschem.thetab where col1=1"No problem (so far...)New DB now has a name "thedb.v1" (notice the "."), the schem and table are the same as above. But now I'm getting a syntax error presumably because of that "." introduced in the DB name.I tried encapsulating the DB name in double quotes (no good), single quotes (still no good) escaping with '\' (no good), escaping with ".." (no good).Is there a way I can reference the DB which has the "." in it's name ?
v11.5 on linux (I know, way past time to upgrade)
Thanks in Advance !
On 3/29/24 15:36, David Gauthier wrote: > Ya, I kind of agree on the >1 DB connections not allowed. It > (perl/DBI) does allow for >1 active DB handles (objects). But of > course those handles/objects have different names and that's how to > work with the different ones (not a DB prefix like what I have). > Still, it accepted the name in the syntax if it didn't have a ".". I > kinda liked the db prefix because I'm doing these sweeping deletes and > I wanted to make extra-sure that I was deleting from the correct DB. > > I'm going to ask them to just create another DB without the "." in the > name. All the code has the prefix and I don't want to uproot that > (because it's working). Or is it working, really? Or are you working around it?
On 3/29/24 17:16, David Gauthier wrote: > I tried encapsulating the DB name in double quotes (no good), single > quotes (still no good) escaping with '\' (no good), escaping with ".." > (no good). SELECT * FROM "thedb.v1".theschem.thetab; Just as documented.