public inbox for [email protected]  

help / color / mirror / Atom feed
From: Mladen Gogala <[email protected]>
To: [email protected]
Subject: Re: Need doc for PostgreSQL RDS External Table
Date: Sun, 22 May 2022 10:53:38 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAL1PVBaEKR+mWuusxA0w=B-ALo-shzjugNSZJPpXAwJ7M=N-AQ@mail.gmail.com>
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>

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


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]
  Subject: Re: Need doc for PostgreSQL RDS External Table
  In-Reply-To: <[email protected]>

* 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