Re: Need doc for PostgreSQL RDS External Table

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