How to prevent table creation without primary key in Postgres?

Question

I would like to enforce a rule such that when people are creating table without primary key, it throws an error. Is it possible to be done from within pgdb?

Answer 1

EDIT: Someone else has answered regarding how to test the existence of primary keys, which completes Part 2 below. You will have to combine both answers for the full solution.

The logic fits inside several event triggers (also see documentation for the create command).

First point to note is the DDL commands this can apply to, all documented here.

Part 1: CREATE TABLE AS & SELECT INTO

If I am not wrong, CREATE TABLE AS and SELECT INTO never add constraints on the created table, they must be blocked with an event trigger that always raises an exception.

CREATE OR REPLACE FUNCTION block_ddl()
 RETURNS event_trigger
 LANGUAGE plpgsql AS
$$
BEGIN
    RAISE EXCEPTION 'It is forbidden to create tables using command: %', tg_tag ;
END;
$$;

CREATE EVENT TRIGGER AdHocTables_forbidden
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE AS', 'SELECT INTO')
EXECUTE FUNCTION block_ddl();

Note your could define the trigger to be ON ddl_command_start`. It makes it a little bit faster but does not go well with the full code I posted at the end.
See the next, less straightforward part for the rest of the explanations.

Part 2: Regular CREATE TABLE & ALTER TABLE

This case is more complex, as we want to block only some commands but not all.

The function and event trigger below do:

1. Output the whole command being passed.
2. Break the command into its subparts.
   To do it, it uses the pg_event_trigger_ddl_commands() (documentation here), which BTW is the reason why this trigger had to be on ddl_command_end.
   You will note that when adding a primary key, a CREATE INDEX is caught too.
3. In the case of the function below, raises an exception to block the creation in all cases (so you can test it without dropping the table you create every time).

Here is the code:

CREATE OR REPLACE FUNCTION pk_enforced()
 RETURNS event_trigger
 LANGUAGE plpgsql AS
$$
DECLARE r RECORD;
BEGIN
    RAISE NOTICE 'Caught command %', (SELECT current_query());
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'Caught inside command % (%)', r.command_tag, r.object_identity;
    END LOOP;
    RAISE EXCEPTION 'Blocking the Creation';
END;
$$;

CREATE EVENT TRIGGER pk_is_mandatory
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE')
EXECUTE FUNCTION pk_enforced();

---

Additional notes:

You can prevent these constraints from being enforced on a temporary table by tested the schema_name is not pg_temp.
The full code, including this test and with credit to jian for the function he posted:

CREATE OR REPLACE FUNCTION public.pk_enforced()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
obj RECORD;
table_name text;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands ()
LOOP
    IF obj.schema_name = 'pg_temp' THEN
        return;
    END IF;
    IF obj.object_type ~ 'table' THEN
        table_name := obj.object_identity;
    END IF;
END LOOP;
IF NOT EXISTS (
    SELECT 
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
    WHERE i.indrelid = table_name::regclass
      AND (i.indisprimary OR i.indisunique)) THEN
RAISE EXCEPTION 'A primary key or a unique constraint is mandatory to perform % on %.', tg_tag, obj.object_identity;
END IF;
END;
$BODY$;

CREATE OR REPLACE FUNCTION public.block_ddl()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands ()
LOOP
    IF obj.schema_name = 'pg_temp' THEN
        return;
    END IF;
END LOOP;
    RAISE EXCEPTION 'DDL command ''%'' is blocked.', tg_tag ;
END;
$BODY$;

CREATE EVENT TRIGGER pk_is_mandatory ON DDL_COMMAND_END
    WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE')
    EXECUTE PROCEDURE public.pk_enforced();
    
CREATE EVENT TRIGGER adhoctables_forbidden ON DDL_COMMAND_END
    WHEN TAG IN ('CREATE TABLE AS', 'SELECT INTO')
    EXECUTE PROCEDURE public.block_ddl();

Answer 2

DROP EVENT TRIGGER trig_test_event_trigger_table_have_primary_key;

CREATE OR REPLACE FUNCTION test_event_trigger_table_have_primary_key ()
    RETURNS event_trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    obj record;
    object_types text[];
    table_name text;
BEGIN
    FOR obj IN
    SELECT
        *
    FROM
        pg_event_trigger_ddl_commands ()
        LOOP
            RAISE NOTICE 'classid: % objid: %,object_type: %
    object_identity: % schema_name: % command_tag: %' , obj.classid , obj.objid , obj.object_type , obj.object_identity , obj.schema_name , obj.command_tag;
            IF obj.object_type ~ 'table' THEN
                table_name := obj.object_identity;
            END IF;
            object_types := object_types || obj.object_type;
        END LOOP;
    RAISE NOTICE 'table name: %' , table_name;
    IF EXISTS (
        SELECT
        FROM
            pg_index i
            JOIN pg_attribute a ON a.attrelid = i.indrelid
                AND a.attnum = ANY (i.indkey)
        WHERE
            i.indisprimary
            AND i.indrelid = table_name::regclass) IS FALSE THEN
    RAISE EXCEPTION ' no primary key, this table not created';
END IF;
END;
$$;


CREATE EVENT TRIGGER trig_test_event_trigger_table_have_primary_key ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE')
        EXECUTE FUNCTION test_event_trigger_table_have_primary_key ();

demo:

DROP TABLE a3;
DROP TABLE a4;
DROP TABLE a5;

CREATE TABLE a3 (
    a int
);

CREATE TABLE a4 (
    a int PRIMARY KEY
);

CREATE TABLE a5 (
    a1 int UNIQUE
);

Only table a4 will be created.

related post: PL/pgSQL checking if a row exists
https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns