Обсуждение: table name

ALTER TABLE "regions-20180101" rename to regions_20180101;


> On Jun 11, 2020, at 11:54 AM, Marc Millas <marc.millas@mokadb.com> wrote:
> 
> sorry if my question is tooo simple :-)
> 
> I got a shapefile from the french gov.
> I import it with postgis 3.01 utility.
> fine !
> the table created by this utility is named regions-20180101
> with the dash in the middle.
> I see that table name in pg_class, and, also, in the geometry_columns view.
> 
> 
> obviously if I ask:
> select * from regions-20180101;
> I get a syntax error.
> if I try select * from $$regions_20180101$$;
> I get another syntax error.
> If I try to rename that table, same thing.
> if I try a cte, same thing.
> 
> What should I do ?
> 
> thanks,
>  
> 
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
> 

Hi Marc,

> On 11. Jun, 2020, at 20:54, Marc Millas <marc.millas@mokadb.com> wrote:
> sorry if my question is tooo simple :-)

it's not. :-)

> obviously if I ask:
> select * from regions-20180101;
> I get a syntax error.
> if I try select * from $$regions_20180101$$;
> I get another syntax error.
> If I try to rename that table, same thing.
> if I try a cte, same thing.
> 
> What should I do ?

you can just quote its name:

select * from "regions-20180101";

Cheers,
Paul

> On 11 Jun 2020, at 20:58, Paul Förster <paul.foerster@gmail.com> wrote:
>
> Hi Marc,
>
>> On 11. Jun, 2020, at 20:54, Marc Millas <marc.millas@mokadb.com> wrote:
>> sorry if my question is tooo simple :-)
>
> it's not. :-)
>
>> obviously if I ask:
>> select * from regions-20180101;
>> I get a syntax error.
>> if I try select * from $$regions_20180101$$;
>> I get another syntax error.
>> If I try to rename that table, same thing.
>> if I try a cte, same thing.
>>
>> What should I do ?
>
> you can just quote its name:
>
> select * from "regions-20180101";
>
> Cheers,
> Paul

The background here is that ‘’ and $$ are quoting of literals (strings, integers, JSON objects, etc.), while “” is
identifierquoting (tables, indices, types, etc.). 

Identifier quoting not only allows to include special symbols, it also makes the identifier case-sensitive. That’s
probablywhy Paul suggested to rename the table to no longer require identifier quoting - many people consider it a
PITA,but it can be used to get out of trouble like yours - some people insist on it, for example because it makes using
camel-capsin identifiers meaningful. 

Regards,

Alban Hertroys
--
There is always an exception to always.