table name
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
ALTER TABLE "regions-20180101" rename to regions_20180101;
Show quoted text
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
damn..
thanks
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Thu, Jun 11, 2020 at 8:55 PM Paul Ramsey <pramsey@cleverelephant.ca>
wrote:
Show quoted text
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_columnsview.
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 identifier quoting (tables, indices, types, etc.).
Identifier quoting not only allows to include special symbols, it also makes the identifier case-sensitive. That’s probably why 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-caps in identifiers meaningful.
Regards,
Alban Hertroys
--
There is always an exception to always.