Menu
I downloaded the shape data from OSM. I have imported data from Shapefile into PostgreSQL without any problem but I got an error when I do the select statement.
Select addr:city From location;
Error: syntax error at or near ":"
The problem is because of the column name contains a colon. Could anyone help me with this issue? Should I reject this shapefile in the importing process? Is the shapefile normal?
334
The colon (:) is used to select "slices" from arrays. (See Section 5.12.) In certain SQL dialects (such as Embedded SQL), the colon is used to prefix variable names. The asterisk (*) has a special meaning when used in the SELECT command or with the COUNT aggregate function.
The type 'string' syntax is a generalization of the standard: SQL specifies this syntax only for a few data types, but PostgreSQL allows it for all types. The syntax with :: is historical PostgreSQL usage, as is the function-call syntax.
First, specify the table, which contains the column you want to rename, after the ALTER TABLE clause. Second, provide the column name after the RENAME COLUMN clause. Third, give the new column name after the TO keyword.
It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name. The alias_name is only valid within the scope of the SQL statement.
If you enclose addr:city with quotes it should work:
SELECT "addr:city" FROM "location";
And if you want to use OpenStreetMap data, you don't have to import shapefiles. Instead, you can import planet.osm (or a regional subset) directly with osm2pgsql.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
