Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Postgres: Table names with spaces.

roger_ackroyd's profile image roger_ackroyd posted 5 years ago in General
I created a new table in the public schema using a create table tableName as ...
Fine, however I had used a space instead of an underscore in the name.
The next time I connected to the database, it gave an 'Invalid name syntax' message with nothing shown in the public schema.
Opened in PGAdminIII no error and everything looked fine. I dropped the table with the space from PGAdmin and back in Heidi everything back to normal.
This did puzzle me for some hours.

ansgar's profile image ansgar posted 5 years ago
r4821 fixes that. The problem was the unquoted schema and table name in this query:
SELECT *, pg_table_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS data_length, pg_relation_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS index_length, c.reltuples, obj_description(c.oid) AS comment FROM "information_schema"."tables" ...

With my fix in r4821, HeidiSQL applies QUOTE_IDENT() on the schema and table names on PostgreSQL 7.3 and up, and does a manual quoting on older servers:
SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME)) AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME)) AS index_length, c.reltuples, obj_description(c.oid) AS comment FROM "information_schema"."tables" ...

Please login to leave a reply, or register at first.