pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to reference a DB with a period in its name ?
8+ messages / 6 participants
[nested] [flat]

* How to reference a DB with a period in its name ?
@ 2024-03-29 21:16 David Gauthier <dfgpostgres@gmail.com>
  2024-03-29 21:20 ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
  2024-03-29 21:24 ` Re: How to reference a DB with a period in its name ? Christophe Pettus <xof@thebuild.com>
  2024-03-29 21:50 ` Re: How to reference a DB with a period in its name ? negora <public@negora.com>
  2024-03-30 14:06 ` Re: How to reference a DB with a period in its name ? Jan Wieck <jan@wi3ck.info>
  0 siblings, 4 replies; 8+ messages in thread

From: David Gauthier @ 2024-03-29 21:16 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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 !

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
@ 2024-03-29 21:20 ` David G. Johnston <david.g.johnston@gmail.com>
  2024-03-29 21:21   ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
  3 siblings, 1 reply; 8+ messages in thread

From: David G. Johnston @ 2024-03-29 21:20 UTC (permalink / raw)
  To: David Gauthier <dfgpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
  2024-03-29 21:20 ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
@ 2024-03-29 21:21   ` David G. Johnston <david.g.johnston@gmail.com>
  2024-03-29 21:36     ` Re: How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: David G. Johnston @ 2024-03-29 21:21 UTC (permalink / raw)
  To: David Gauthier <dfgpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
  2024-03-29 21:20 ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
  2024-03-29 21:21   ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
@ 2024-03-29 21:36     ` David Gauthier <dfgpostgres@gmail.com>
  2024-03-29 22:52       ` Re: How to reference a DB with a period in its name ? Rob Sargent <robjsargent@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: David Gauthier @ 2024-03-29 21:36 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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:

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
  2024-03-29 21:20 ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
  2024-03-29 21:21   ` Re: How to reference a DB with a period in its name ? David G. Johnston <david.g.johnston@gmail.com>
  2024-03-29 21:36     ` Re: How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
@ 2024-03-29 22:52       ` Rob Sargent <robjsargent@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Rob Sargent @ 2024-03-29 22:52 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



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?






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
@ 2024-03-29 21:24 ` Christophe Pettus <xof@thebuild.com>
  3 siblings, 0 replies; 8+ messages in thread

From: Christophe Pettus @ 2024-03-29 21:24 UTC (permalink / raw)
  To: David Gauthier <dfgpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org



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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
@ 2024-03-29 21:50 ` negora <public@negora.com>
  3 siblings, 0 replies; 8+ messages in thread

From: negora @ 2024-03-29 21:50 UTC (permalink / raw)
  To: David Gauthier <dfgpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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 !

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: How to reference a DB with a period in its name ?
  2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
@ 2024-03-30 14:06 ` Jan Wieck <jan@wi3ck.info>
  3 siblings, 0 replies; 8+ messages in thread

From: Jan Wieck @ 2024-03-30 14:06 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.






^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2024-03-30 14:06 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-29 21:16 How to reference a DB with a period in its name ? David Gauthier <dfgpostgres@gmail.com>
2024-03-29 21:20 ` David G. Johnston <david.g.johnston@gmail.com>
2024-03-29 21:21   ` David G. Johnston <david.g.johnston@gmail.com>
2024-03-29 21:36     ` David Gauthier <dfgpostgres@gmail.com>
2024-03-29 22:52       ` Rob Sargent <robjsargent@gmail.com>
2024-03-29 21:24 ` Christophe Pettus <xof@thebuild.com>
2024-03-29 21:50 ` negora <public@negora.com>
2024-03-30 14:06 ` Jan Wieck <jan@wi3ck.info>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox