SELECT R.TABLE_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
ON U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
AND U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
AND U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE
-- THIS IS WHERE YOUR INTEREST IS (YOUR TABLE)
U.COLUMN_NAME = 'THE_COLUMN_NAME'
AND U.TABLE_CATALOG = 'THE_DB_NAME'
AND U.TABLE_SCHEMA = 'THE_SCHEMA_NAME'
AND U.TABLE_NAME = 'THE_TABLE_NAME'
Postgres function to devide-and-conquer (iterate) your big query
Create a postgres function to devide-and-conquer (iterate) your big query
TRUNCATE TABLE YOUR_NEW_BIG_TABLE;
DROP FUNCTION IF EXISTS pg_iterator();
CREATE OR REPLACE FUNCTION pg_iterator()
RETURNS void AS
$BODY$
DECLARE
vOffsetRecord INT;
vTotal INT;
vLimit INT;
vOffset INT;
BEGIN
-- How many?
-- select count(*) INTO currentRecord from YOUR_BIG_TABLE; -- 346,879,848
vTotal := 346879848;
-- vTotal := 5000;
vLimit := 100000;
vOffset :=0;
RAISE NOTICE 'DEBUG vTotal : %', vTotal;
RAISE NOTICE 'DEBUG vLimit : %', vLimit;
RAISE NOTICE 'DEBUG vOffset: %', vOffset;
LOOP
IF (vOffset >= vTotal) THEN
EXIT;
END IF;
EXECUTE format('INSERT INTO YOUR_NEW_BIG_TABLE (id, language, hash) SELECT id, language, hash FROM YOUR_BIG_TABLE ORDER BY id LIMIT %s OFFSET %s;', vLimit, vOffset);
RAISE NOTICE 'DEBUG vOffset: %', vOffset;
vOffset := vOffset + vLimit;
END LOOP;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION pg_iterator()
OWNER TO postgres;
select pg_iterator();
Crontab header
# minute (0-59),
# | hour (0-23),
# | | day of the month (1-31),
# | | | month of the year (1-12),
# | | | | day of the week (0=Sunday 1=Monday 2=Tuesday 3=Wednesday 4=Thursday 5=Friday 6=Saturday).
# | | | | | commands