Tag Archives: PostgreSQL

Table partitioning in PostgreSQL 10

It became much easier to implement table partitioning in PostgreSQL 10.

In the previous versions it was necessary to implement check contraints, triggers and trigger functions to redirect inserted rows to the corresponding partitions (child tables).

In PostgreSQL 10 all we need to do is create a parent table specifying the PARTITION BY clause and also create child tables specifying the bounds that correspond to the partition key of the parent table.

Example:

Creating the table to be partitioned (parent table)
CREATE TABLE sales (
saleid serial,
salevalue numeric,select * from sales;
saledate date
) PARTITION BY RANGE (saledate);

Creating the child tables partitioned by year/month
CREATE TABLE sales_y2018m01 PARTITION OF sales
FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');

CREATE TABLE sales_y2018m02 PARTITION OF sales
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');

Inserting some data into sales
insert into sales (salevalue,saledate) values (100.50,'2018-01-02');
insert into sales (salevalue,saledate) values (200,'2018-01-04');
insert into sales (salevalue,saledate) values (580.70,'2018-02-20');
insert into sales (salevalue,saledate) values (350,'2018-02-25');

Querying table sales
select * from sales;

saleid | salevalue | saledate
1 | 100.50 | 2018-01-02
2 | 200 | 2018-01-04
3 | 580.70 | 2018-02-20
4 | 350 | 2018-02-25

* It is not possible to create constraints (primary key, foreign key, unique, etc) or indexes in the parent table. If it is necessary to create indexes or constraint, it must be done in each child table.

Advertisements

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;

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');