duckdb / duckdb/duckdb-postgres

how to create table in greenplum add other info?

Open
#210 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
372
Forks
105
Avg merge
10h 19m
Merged PRs (30d)
18

Description

### 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

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.