Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Access Exasol from postgres

MaxPeters
Contributor

We have a fairly small table (<10K rows) in an Exasol cluster and would like to integrate the table in a Postgres database as a foreign table.

Is there any official way of doing it?

Google only told me how to integrate Postgres in Exasol but we are trying to query Exasol from Postgres.

 

Of course we could ETL the table on a regular basis but I'm looking for a more elegant way.

 

Thanks!

5 REPLIES 5

mgasperi_work
SQL-Fighter

Hi!

You need to create a JDBC connection and afterwards:

I'm using query_wrapper from Exasol's github repo....

suc, stmt = wrapper:query([[
EXPORT ( SELECT FIELD1, FIELD2 FROM SOME_EXA_TABLE)
INTO JDBC AT JDBC_PG_CONNECT
STATEMENT 'INSERT INTO pg_table (field1, field2) SELECT ? AS field1, ? AS field2' ]])

Note: TO_NUMBER(?,''99999999999999.99'') AS "FIELD1" ... conversion of data types
postgresql-virtual-schema/capabilities.md at main · exasol/postgresql-virtual-schema · GitHub
Check data types here: postgresql-virtual-schema/postgresql_user_guide.md at main · exasol/postgresql-virtual-schema · GitH...

exa-Uwe
Moderator
Moderator

Dear MaxPeters,

thank you for your interest in our database! You ask how an Exasol table can be integrated into a Postgres database. Fair question but wrong forum, I'd say 😄 A Postgres forum might be a better candidate to come up with a good answer, while this forum can tell you how a Postgres table can be integrated into an Exasol database.

Best regards

Uwe

MaxPeters
Contributor

Thanks, fair point 😉

I'll try it in another place and if I find a solution, I'll post it here.

MaxPeters
Contributor

@mgasperi_work: If I understand your answer correctly, this would enable accessing Postgres from Exasol?
I'm looking to integrate an Exasol table into my Postgres database.

mgasperi_work
SQL-Fighter

Hi @MaxPeters!
Sorry for my initial confusion with IMPORT statement, so I'll try to clarify now:

1. You can IMPORT a Postgres table in Exasol using the IMPORT statement and a JDBC connection
2. You can EXPORT an Exasol table into Postgres using the EXPORT statement also using a JDBC connection

... both directly from Exasol. That's what I'm doing - because I prefer pushing whenever ETL is finished and not pulling.

The little bit tricky part is the data type "conversion" - but besides that, it's pretty straight forward. 

KR

Michael