how to create table in greenplum add other info?

[wonb168]

What happens?

I calculate in duckdb , and then write back to gp, for example:

create table gp.public.test as 
select * from rst

this way, the table have no zip and distributed info.

CREATE TABLE test(id int,name text)
WITH (
    appendonly=true,
    orientation=column,
    compresslevel=6,
    compresstype=zlib
)
DISTRIBUTED BY (skc_sk);

If use postgres_execute create table in gp first, and then insert into data, but raise error: no table I guess gp table into duckdb at the attach moment, and can't know the new table, then how to get the new table ? or can create table add zip and distributed info.

To Reproduce

my demo:

-- DROP FUNCTION public.test_ddl();

CREATE OR REPLACE FUNCTION public.test_ddl()
 RETURNS text
 LANGUAGE plpython3u
AS $function$ 
import duckdb 
def exesql(sql):
    plpy.notice(sql)
    dd.execute(sql)

dd=duckdb.connect('gp.duckdb')
dd=duckdb.connect()
dbname="mdmaster_hggp7_dev" 
tenant=dbname.split('_')[1]
dburl=f"dbname={dbname} user=gpadmin host=127.0.0.1 port=2345"
sql=f"load postgres;ATTACH '{dburl}' AS gp (TYPE postgres);"
exesql(sql)
sql="drop table if exists tmp_rst;create table tmp_rst(id int,name text);insert into tmp_rst values (1,'a');"
exesql(sql)
sql="""select sql from duckdb_tables() where table_name like 'tmp_rst' and schema_name = 'main';"""
sql="drop table if exists public.tmp_rst;"+(dd.execute(sql).fetchone()[0]).replace('tmp_rst','public.tmp_rst') 
plpy.notice(sql)
plpy.execute(sql) # 不执行最后的 insert，有建表，但加上就没有表

#sql=f"detach gp;ATTACH '{dburl}' AS gp (TYPE postgres);"
#sql="CALL pg_clear_cache();"
ddl=f"call postgres_execute('gp','{sql}')" #Table Function with name postgres_execute does not exist!
exesql(ddl)

sql="insert into gp.public.tmp_rst select * from tmp_rst"
exesql(sql)

return 'done' 
$function$
;

select test_ddl()

OS:

centos7

PostgreSQL Version:

pg16

DuckDB Version:

0.10.1

DuckDB Client:

python

Full Name:

wang cz

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