I'm new to Postgres and trying to create code that inserts data from several tables into a single table. All source tables have a name starting with "B3_HIST_", differing only in terms of year (they are named from "B3_HIST_2015" to…
I created person table, then inserted 2 rows into it as shown below: CREATE TABLE person ( id INT, name VARCHAR(20), age INT ); INSERT INTO person (id, name, age) VALUES (1, 'John', 27), (2, 'David', 32); Then with an…
I'm Newbie in postgres. I wrote a function, which insert into table a data by portions(because there is too much data in the source table to do it all at once) from another table: CREATE OR REPLACE FUNCTION data_load(p_src_schema character…
I have several views on the same table containing overlapping data. By dynamically retrieving the views from the information_schema and unioning them I can create a text query result. However I still need to manually run that query to get…
I need to create a Materialized view to loop all worklist tables from pg_tables. First I create a query and after tried to create a for loop. This my first code without the for loop. SELECT count(visit_num) AS total_claim_count, count(user_id)…
I need to monitor the ongoing flow of data using the latest created date for a set of tables. Basically, I need to batch run SELECT MAX(z_date_creation) FROM table_schema.table_name on a set of tables I retrieve with SELECT c.table_schema, c.table_name…
May I know on how to call an array in stored procedure? I tried to enclosed it with a bracket to put the column_name that need to be insert in the new table. CREATE OR REPLACE PROCEDURE data_versioning_nonull(new_table_name VARCHAR(100),column_name VARCHAR(100)[],…
The following function identifies columns with null values. How can I extend the where clause to check null or empty value? coalesce(TRIM(string), '') = '' CREATE OR REPLACE FUNCTION public.is_column_empty(IN table_name varchar, IN column_name varchar) RETURNS bool LANGUAGE plpgsql AS…