Re: Need doc for PostgreSQL RDS External Table

Thanks Mladen,

Getting this error, when tried to create extension in AWS RDS .

postgres=> create extension file_fdw;
ERROR: Extension "file_fdw" is not supported by Amazon RDS
DETAIL: Installing the extension "file_fdw" failed, because it is not on
the list of extensions supported by Amazon RDS.
HINT: Amazon RDS allows users with rds_superuser role to install supported
extensions. See: SHOW rds.extensions;
postgres=>

On Sun, May 22, 2022 at 8:23 PM Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>
wrote:

> 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-1217https://dbwhisperer.wordpress.com
>
>