On 5/22/22 10:13, Devendra Postgres wrote:
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