How to reference a DB with a period in its name ?

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 individual components 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).

Thanks David !

On Fri, Mar 29, 2024 at 5:22 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

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 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.