Tag Archives: PostgreSQL

Automatically create index on all Foreign Keys (PostgreSQL)

Postgres does not automatically create indexes on Foreign Keys.

The above query generates the create index statement for all foreign keys with no indexes:

select 'create index ' || conname || '_idx on ' || conrelid ||
' (' || array_to_string(column_name_list, ',') || ');' as create_script
from (select distinct
conrelid,
conname,
array_agg(attname) column_name_list,
array_agg(attnum) as column_list,
nspname
from pg_attribute
join (select conrelid::regclass,
conname,
unnest(conkey) as column_index,
nspname
from (select distinct
conrelid, conname, conkey, nspname
from pg_constraint
join pg_class on pg_class.oid = pg_constraint.conrelid
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where contype = 'f' and nspname !~ '^pg_'
and nspname <> 'information_schema'
) fkey
) fkey
on fkey.conrelid = pg_attribute.attrelid
and fkey.column_index = pg_attribute.attnum
group by conrelid, conname, nspname
) candidate_index
join pg_class on pg_class.oid = candidate_index.conrelid
left join pg_index on pg_index.indrelid = conrelid
and indkey::text = array_to_string(column_list, ' ')
where
nspname = 'your_schema' and
indexrelid is null
order by relname;

Advertisements

Function to generate insert statements from table (PostgreSQL)

This function is useful to return insert statements from a table:

CREATE OR REPLACE FUNCTION public.generate_inserts(varSchema text, varTable text) RETURNS TABLE(resultado text) AS $$

DECLARE CODE TEXT;

BEGIN
CODE :=
(SELECT
'SELECT ''INSERT INTO '
|| table_schema || '.'
|| table_name ||' ('
|| replace(replace(array_agg(column_name::text)::text,'{',''),'}','') || ') VALUES (''||'
|| replace(replace(replace(array_agg('quote_nullable(' || column_name::text || ')')::text,'{',''),'}',''),',',' || '','' || ')
|| ' || '');'' '
|| 'FROM ' || table_schema || '.' || table_name || ';'
FROM information_schema.columns
WHERE table_schema = varSchema
AND table_name = varTable
GROUP BY table_schema, table_name);

RETURN QUERY
EXECUTE CODE;
END;
$$ LANGUAGE plpgsql;

Calling the function:

SELECT public.generate_inserts('YourSchema','YourTable');