SQL functions and statements

INFER_TABLE_DDL supports JSON

You can now use the INFER_TABLE_DDL function to produce candidate table definitions from JSON files. Because a JSON file does not have an explicit schema, the function inspects the data itself. Because JSON data can vary from record to record or file to file, the function can return more than one candidate definition. In the following example, differences are highlighted:

=> SELECT INFER_TABLE_DDL ('/data/*.json'
    USING PARAMETERS table_name='restaurants', format='json',
max_files=3, max_candidates=3);
WARNING 0:  This generated statement contains one or more float types which might lose precision
WARNING 0:  This generated statement contains one or more varchar/varbinary types which default to length 80

                INFER_TABLE_DDL
------------------------------------------------------------------------
 Candidate matched 1/2 of total files(s):
  create table "restaurants"(
    "cuisine" varchar,
    "location_city" Array[varchar],
    "menu" Array[Row(
      "item" varchar,
      "price" float
    )],
    "name" varchar
  );
Candidate matched 1/2 of total files(s):
  create table "restaurants"(
    "cuisine" varchar,
    "location_city" Array[varchar],
    "menu" Array[Row(
      "items" Array[Row(
        "item" varchar,
        "price" numeric
      )],
      "time" varchar
    )],
    "name" varchar
  );

(1 row)

Immutable tables

Immutable tables are insert-only tables in which existing data cannot be modified, regardless of user privileges. Updating row values and deleting rows are prohibited. Certain changes to table metadata—for example, renaming tables—are also prohibited, in order to prevent attempts to circumvent these restrictions.

You set an existing table to be immutable with ALTER TABLE:

ALTER TABLE table SET IMMUTABLE ROWS;

For details, see Immutable tables.