query greenplum error

[wonb168]

What happens?

select * from gp.tbl; --ok
select * from postgres_query('gp','select * from tbl;');--wrong

raise error:

SQL 错误: Invalid Error: Failed to prepare COPY "
    COPY (SELECT "id", "name" FROM (select * from tbl;) AS __unnamed_subquery ) TO STDOUT (FORMAT binary);
    ": ERROR:  syntax error at or near ";"
LINE 2:  COPY (SELECT "id", "name" FROM (select * from tbl;) AS __un...
                                                          ^

  Invalid Error: Failed to prepare COPY "
    COPY (SELECT "id", "name" FROM (select * from tbl;) AS __unnamed_subquery ) TO STDOUT (FORMAT binary);
    ": ERROR:  syntax error at or near ";"

To Reproduce

CREATE TABLE gp.tbl(id INTEGER, name VARCHAR); INSERT INTO gp.tbl VALUES (42, 'DuckDB');

OS:

mac14

PostgreSQL Version:

6.12.1(gp,pg:9.4)

DuckDB Version:

0.9.2

DuckDB Client:

java

Full Name:

wonb168

Affiliation:

Linezone

Have you tried this on the latest main branch?

• [X] I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] I agree

[wonb168]

I GUESS is permission。 if the table created in gp，then cannot query by gp.tbl . if the gp table created by duckdb,then cannot query by postgres_query(), But,how to solve this?

[Mytherin]

Thanks for the report! The issue is not permissions but rather the semicolon in the query. postgres_query('gp','select * from tbl') without the semicolon should work. I've pushed a fix in #169 that strips any semicolons that are present.