How do I create a table (postgresql) as select * from an odb table?

[genterminl]

I have an existing table in a native base database. I also have a databse which is a connection to a local postgresql database. I want to essentially copy the existing table to the other database. All attempts as using SQL seem to pass it through to postgres, which obviously doesn’t know about the source database. I’ve tried dragging, which leads me through questions about which columns and PK, but then does nothing when I hit “Create”. Am I missing something? What other information can I provide for someone to give me a hint or a pointer to the fine manual I missed reading.

Libreoffice 4.4.1.2, Postgresql 9.4.2, on Gentoo Linux with kernel 3.17.8, all in English.

[doug]

Concurrent access in LO Base would be great enhancement. If both are live, try building an ad hoc connection and executing UPDATE and INSERT statements that way. Have not independently verified this is possible. If dead, export to csv with SELECT * INTO TEXT ... and import directly to Postgre. Access via calc is best for cut-and-paste.

[genterminl]

I originally imported into LO Base through Calc, but would prefern not to have to redo all the column definitions. If necessary, I’ll go from Calc to Postgres. I will check on the ad-hoc connection route.

I couldn’t follow the ad-hoc connection, but found a reasonable alternative. In the source odb file, Tools → SQL and entered “SCRIPT ‘path to file’” which created a script to recreate the entire database of one table. After editing, I fed it to psql to create the table.

[doug]

Consider converting your comment to an answer.

[genterminl]

Doug - I may eventually do that, but it is a way to accomplish my task, not really an answer to the question of how to do it more directly, and why the click/drag method looks like it’s working, but then doesn’t actually seem to do anything.

[doug]

Can you give more detail about where you are clicking, dragging, and then where you are pasting in Base or Calc?

[genterminl]

Sorry for delay. With workaround, no time to go back to original question. Currently using libreoffice 4.4.4.3 and postgresql 9.4.4.

To reproduce:

• open LO Base window to a native base database with a source table and another to a linked Postgres database schema with write access
• drag the table from the first window to the tables pane of the second
• get popup “Copy table” click “Next”.
• click ‘>>’ to keep all cols. Click Next then Create. Nothing happens. (brevity for comment limit)

[genterminl]

In addition, I am able to successfully copy a table from either a native base database or from a linked PG database to a native base database. That makes me wonder if there is some permission issue with Postgres, and not a LO issue. I’ll have to go digging into the log files, and maybe increase the log verbosity.

[doug]

that is creative. For me, transfer from LO Base front end/MariaDB backend to LO Native succeeds, but reverse (LO native to LO/MariaDB) fails explicitly with a warning that the user does not have necessary permissions on the table to be copied to LO/MariaDB, SQL Status 42000, Error Code 1142, even though I am certain the named user indeed has the necessary permissions on MariaDB. The err is labeled as from the backend, but looks like LO bug, might be worth report in light of err msg.

[genterminl]

Yes, any transfer TO native seems to work. Transfer to DBMS back end fails silently instead of surfacing the back end error. To me, the LO bug is just not surfacing the error. You need to dig into the back end logs, possibly turning up verbosity. For me, it wasn’t really permission error, but trying to create a table in a non-existent schema.

[genterminl]

LO is free of any blame here, other than perhaps not surfacing any error message. It appears that by simply dragging the table to copy, it (or maybe Postgres itself) was trying to use a schema with the same name as the postgres user. A schema of that name did not exist in my database. By explicitly adding “public.” to the front of the table name, it allowed the copy to complete without error. Alternatively, I could have explicitly created the schema first.

I finally figured this out by increasing the log verbosity in Postgres, and watching the log as I tried the copy. Apologies for the noise.