CODEWITHSUNDEEP
postgresqlpartitioning
leas
leas

Reputation: 301

How to find columns of the partition of a master table in postgresql 9.4?

In postgresql I have created a master table

CREATE TABLE IF NOT EXISTS table_master(
column_1 text NOT NULL,
column_2 text NOT NULL,
column_3 text NOT NULL
);

and a partition table based on columns: column_1, column_2

CREATE TABLE table_col1_col2 (
CONSTRAINT table_col1_col2_pk PRIMARY KEY (column_1, column_2),
CHECK ( column_1 = 'value_1' AND column_2 = 'value_2')
) INHERITS (table_master);

Is there any select query in order to get the columns of the partition (column_1, column_2)?

UPDATE

In case I know a child table, I may use the below query:

WITH table_id AS (
SELECT oid 
FROM pg_class
WHERE relname = 'child_table_name'
),
con AS (
SELECT c.conname,
c.contype,
c.conkey,
c.consrc
FROM pg_constraint c INNER JOIN table_id ON c.conrelid = table_id.oid
WHERE c.contype = 'c'
)
SELECT a.attnum,
a.attname
FROM pg_attribute a INNER JOIN table_id ON a.attrelid = table_id.oid
INNER JOIN con s ON a.attnum = ANY(conkey)

FINAL QUERY

WITH table_id AS (
SELECT pg_class.oid 
FROM pg_class INNER JOIN pg_inherits ON pg_class.relname::text = pg_inherits.inhrelid::regclass::text
WHERE pg_inherits.inhparent = 'master_table'::regclass
),
con AS (
SELECT c.conname,
c.contype,
c.conkey,
c.consrc
FROM pg_constraint c INNER JOIN table_id ON c.conrelid = table_id.oid
WHERE c.contype = 'c'
)
SELECT a.attnum,
a.attname
FROM pg_attribute a INNER JOIN table_id ON a.attrelid = table_id.oid
INNER JOIN con s ON a.attnum = ANY(conkey)

Upvotes: 0

Views: 1113

Answers (1)

Evgeny Nozdrev
Evgeny Nozdrev

Reputation: 1566

You can get columns of primary key.

This returns the names and data types of all columns of the primary key for the tablename table:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tablename'::regclass
AND    i.indisprimary;

(source)

Here used the fact that primary key's uniqueness is handled by Postgres via index, so you can use pg_index table.

Upvotes: 0

Related Questions