No, you should not be able to add such a constraint.
The PRIMARY KEY
must be a simple or composite index. It may not be a partial or expression index.
The index acts as a constraint, and functionally they're much the same, but it can't appear as the PRIMARY KEY
in the table metadata and cannot be used as the target of a foreign key constraint. The same is true for UNIQUE
constraints.
The issue here is that the SQL-standard definitions of PRIMARY KEY
and UNIQUE
constraints do not allow for expressions or for row matching predicates. So if PostgreSQL lists an expression index or partial index as a constraint, it's breaking the standard and lying to applications about what it's doing. Apps that understand PostgreSQL's features can look the index up from Pg's own catalogs, and there's also info in information_schema
, but it can't go in as a listed constraint.
As you have multiple rows inside the JSON column, you need a function that returns a set. This can be done using the json_to_recordset()
function:
select j.*
from json_test,
json_to_recordset(json_data) as j(name text, country text, hobby text, address text, sex text);
Because this is an anonymous record type, you must explicitly define each column. Probably not a big thing if you want to put that into a view. Note that your sample data would not return a value for the Country
keys in your JSON data, only for the one named country
because the matching between column names and JSON keys is case-sensitive.
If you don't want to create a view, you can shorten the above using a custom type:
create type person_t as (name text, country text, hobby text, address text, sex text);
select j.*
from json_test,
json_populate_recordset(null::person_t, json_data) as j;
SQLFiddle: http://sqlfiddle.com/#!15/91b85/1
Best Answer
I don't think there is any such feature in MariaDB or MySQL themselves at the moment (MariaDB 10.3, MySQL 5.7, 8.0).
So the only way I can imagine actually detecting support for specific data types and built-in functions on-the-fly would be to try using the data type or funtion, and then catching the exception, if any.
It's also possible that certain software libraries can give you a hint as to what features the underlying RDBMS can offer. If so, this is probably done by storing a list of features associated with each RDBMS version inside the software library.