Unable to export Postgres database to another server (syntax error)

Mogzol's profile image Mogzol posted 5 years ago in General Permalink

I'm trying to export some tables to another Postgres server. When I used to use MySQL, I could do this by right clicking the desired table/database in the sidebar, choosing "Export Database as SQL", and choosing my target server as the output.

With two Postgres servers though, when I try to run the export, I get a syntax error:

ERROR:  syntax error at or near "USE"
LINE 1: USE "public"
        ^

Exporting to a SQL file first and then running that file in the new server works, but is less convenient, would be nice if this issue could be fixed.

1 attachment(s):
  • export-error
Mogzol's profile image Mogzol posted 5 years ago Permalink

Still getting this on the latest nightly (11.0.0.5995), any help would be greatly appreciated. My postgres version is 11 by the way, didn't mention that in my initial post.

ansgar's profile image ansgar posted 5 years ago Permalink

I suppose the CREATE statements generated in the exports are anyway incompatible to PG? But this particular USE ... is probably hardcoded, I should make that server specific, so PG gets a SET search_path TO ... instead.

rpass's profile image rpass posted 4 years ago Permalink

I have the same problem, can't export table to another database directly : error syntax with USE ...

Icarules's profile image Icarules posted 4 years ago Permalink

Any update on this?

Code modification/commit f8e4b35 from Ansgar Becker <anse@heidisql.com>, 4 years ago, revision 11.0.0.6104
Make "USE xyz" query dependent on the target server system, when exporting data to another server. Fixes incompatible SQL for PostgreSQL, and most probably for SQLite. See https://www.heidisql.com/forum.php?t=36231
ansgar's profile image ansgar posted 4 years ago Permalink

I just made that hardcoded USE xyz query specific to the target server, so on PG HeidiSQL now fires SET search_path TO xyz instead. Next build will have that fix.

I suppose you will run right into the next issue, especially when having checked one of the "Create" or "Drop" options. But give it a try first.

Isn't there an issue report on Github for PG exports?

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