public inbox for [email protected]  

help / color / mirror / Atom feed
From: Devendra Postgres <[email protected]>
To: Mladen Gogala <[email protected]>
Cc: [email protected]
Subject: Re: Need doc for PostgreSQL RDS External Table
Date: Sun, 22 May 2022 21:36:21 +0530
Message-ID: <CAL1PVBa=sYb51ObgLA+gF8_B=t9O9q5QDVtKaSLZL8cdorSa0Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAN0SRDFydu12pW+XV9KanakZwd9psbg9jA+7fMJuUDiB7iENQA@mail.gmail.com>
	<[email protected]>
	<CAN0SRDGP5itL1N7hy3n8pSXxeP5Gjuou-dM1SqAZ_08_3K+2bg@mail.gmail.com>
	<[email protected]>
	<CAN0SRDFNZ1ht1OHmr=h55S60fn+5c7BC9YSUcYkHA+svhwKXig@mail.gmail.com>
	<CAL1PVBaEKR+mWuusxA0w=B-ALo-shzjugNSZJPpXAwJ7M=N-AQ@mail.gmail.com>
	<[email protected]>

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 <[email protected]>
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
>
>


view thread (14+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Need doc for PostgreSQL RDS External Table
  In-Reply-To: <CAL1PVBa=sYb51ObgLA+gF8_B=t9O9q5QDVtKaSLZL8cdorSa0Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox