iterate over partitions

Hi All, I have a large table partioned by month. I would like to run a query - which adds derived data to the current data and inserts the data into a new table. The new table is the target for users. How can I iterate over the partition tables to insert data and build the new table? This is do

Help with insert query

All, The query below is designed to insert into a table. This works when I have a single loan which I insert. However, if remove the part of the where clause of a single loan the insert does not work. The table fnmloan is a large table with 500mm + rows and the query runs for about 4 hours. An

Re: query with regular expression

Thanks for the tip! On Thu, Jan 3, 2019 at 12:58 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, January 3, 2019, Glenn Schultz wrote: > >> All, >> >> I am writing a query to select * from where ~ '[regx] an example of the &

query with regular expression

All, I am writing a query to select * from where ~ '[regx] an example of the sting that I am matching is below FHLG16725 The first two alpha characters to match are FN, FH, GN any alpha characters between those and the numeric don't matter as the first two alpha + numeric will create a unique.

Recursive CTE

All, Following my earlier post on variable instantiation, I rethought how I was working with dates and realized I can fix the date and use static interval. I came up with this recursive CTE which is the end goal. However, the problem is that the convexity query cannot be used as a subquery. So I

Re: initialize and use variable in query

Thanks! On Sat, Dec 29, 2018 at 10:06 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Saturday, December 29, 2018, Ray O'Donnell wrote: >> >> A couple of things off the top of my head: > > > Sorry but, no. > >> >> (i) I think you need "language plpgsql" (or whatever) after the DO

initialize and use variable in query

All, I need to initialize a variable and then use it in query. Ultimately this will part of a recursive CTE but for now I just need to work this out. I followed the docs and thought I needed something like this. But does not work-maybe I have misunderstood. Is this possible? SET max_parallel_

Amazon Aurora

I have a Postgres database of about 1.5 terabytes on amazon aurora. It runs super slow. Has anyone experienced this and if so how was the problem addressed? Glenn Sent from my iPhone

using a function in where

All, I am using the function below to convert a continuous variable to a binned value. Sometimes a value other than zero is passed through the query. For example -.5 result value is passed to the query result. The basic of the query is below. select incentivebin(wac, rate, .25) from my_table whe

Creating a function

Hi, I am trying to create a function to bin based on user value and I am stuck. I followed the postgres create function tutoriall but I am missing something. Any help would be appreciated as I think I am just going further off course at this point Glenn CREATE FUNCTION "IncentiveBin"(in Gwac d

Re: table value function help

Thanks! I googled this for 3-days before coming here. I see what you mean. Thank you so much will make the recommended changes. Glenn On Thu, Nov 22, 2018 at 11:02 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, November 22, 2018, Glenn Schultz wrote: &

table value function help

Hello, I have a table value function and would like the first and second input to take multiple arguments (array or list) I suppose. Like the below: create or replace function myfunction(sector, agency, term) returns table (cusip char(9), sector char(12)) language sql stable as $function$ sele