What is the order of keys defined in the primary key list for partition table in postgresql? - Stack Overflow

I am partitioning a table in Postgresql15. I have a partition table which has country as the partition key. Each child table has id as the primary key. My application talks with the master partition table only.

I have some SQL includes CONFLICT ON against the primary key id. This doesn't work for partition table since id is not primary key in the partition table so I will get error like: there is no unique or exclusion constraint matching the ON CONFLICT specification.

In order to solve this issue, I will need to create the primary key index on the partition table. But the partition table needs to have the same primary key index as each child table, I will have to drop the existing id primary key from child table, then create a new primary key including both id and country on both child and partition table.

My question is does the order of id and country on the primary index matter? What is the right order I should use?

I am partitioning a table in Postgresql15. I have a partition table which has country as the partition key. Each child table has id as the primary key. My application talks with the master partition table only.

I have some SQL includes CONFLICT ON against the primary key id. This doesn't work for partition table since id is not primary key in the partition table so I will get error like: there is no unique or exclusion constraint matching the ON CONFLICT specification.

In order to solve this issue, I will need to create the primary key index on the partition table. But the partition table needs to have the same primary key index as each child table, I will have to drop the existing id primary key from child table, then create a new primary key including both id and country on both child and partition table.

My question is does the order of id and country on the primary index matter? What is the right order I should use?

• postgresql

Share Improve this question Follow asked Feb 4 at 2:45 Joey Yi ZhaoJoey Yi Zhao 42.8k8888 gold badges358358 silver badges662662 bronze badges 2

• 1 Postgres 15 provides the MERGE statement. It is much more flexible within the when matched condition than is the insert ... on conflict. I do not know your specific requirement but merge may allow resolution of your problem within your existing structure. – Belayer Commented Feb 4 at 5:31
• I am using a library typeorm which uses on conflict internally. so I can't change the library easily. that's why I stick with on conflict. Do you know if the order of index matter? – Joey Yi Zhao Commented Feb 4 at 5:32

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The order of the column in a primary key definition matters, since the order of columns in an index matters. An index on (a, b) can be used to speed up statements with WHERE a = ..., but is useless for WHERE b = ... (there are exceptions, but let's keep it simple). Here are some guidelines:

• if you predominantly use one of the columns in WHERE clauses, but not the other, put the one column first

• if you need to support ORDER BY a, b, define the index in that same order

• if both columns are used in the WHERE clause (and the conditions are combined with AND), put the column first that is compared with = — if both comparisons are with =, the order doesn't matter

If several of these cases apply and suggest different orderings, you may have to define a second index.