Good Morning & Good Evening All,
As Migration, I need to convert one table from Oracle (Source) to Postgres(Target)The Oracle Table is created as below:
The oracle table is external table. What is external table : We will keep files(CSV/ flat files) in drive, we will create a one physical table in oracle and when we select * from table, The oracle external table will go to the CSV/Flat file and it will pull the data from the table and will share results to the user.
FYI : it won’t store any physical data in the oracle database.
Requirement : we need similar kind of external concept in POSTGRESQL RDS. The data ( CSV/Flat) is storing in S3 Bucket.
Could some one share doc/ URL .
Thxs,Deva
Well, the same feature in Postgres is called file_fdw. The documentation is here: https://www.postgresql.org/docs/14/file-fdw.html
Here is an example: https://dbaclass.com/article/how-to-access-csv-files-on-file-system-using-file_fdw_in_postgres/
Good luck!
PS:
I hate foreign tables in Oracle and I don't use them in Postgres.
You cannot index them and about the only thing you can do with
them is sequential scan. The problem with that is that the larger
file gets, the more time it takes to process it. External tables
in Oracle were meant as a shortcut for loading them, bypassing
SQL*Loader. You are the first person in my 30 years long career of
an Oracle architect/DBA/consultant who uses external tables.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
