Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need a constraint similar to:

node UUID NOT NULL CHECK (SELECT true FROM this_table WHERE node = NEW.node HAVING COUNT(*) <= 2)

Except that this syntax is illegal. I also have the following problem since this applies to four of our tables:

  • I don't know the table name up front
  • I don't know the column name up front
  • I don't know the allowed number of occurrences up front

I almost have it working, but I can't figure out how to extract the value of the NEW.node. I have written a trigger that's close, but no cigar:

CREATE DOMAIN uint2 AS int4 CHECK(VALUE >= 0 AND VALUE < 65536);

    total       integer;
    this_table  text := TG_TABLE_NAME::regclass::text;
    this_column text := TG_ARGV[0]; -- first argument (triggers cannot define arguments directly)
    this_limit uint2 := TG_ARGV[1]; -- second argument
    IF cardinality(TG_ARGV) <> 2 THEN
        RAISE EXCEPTION 'limit_count() trigger expects a column name and a limit';
    END IF;

    EXECUTE FORMAT('SELECT COUNT(%I) FROM %I WHERE %1$I = $1', this_column, this_table)
       USING NEW.quote_literal(this_column)
       INTO total;

    IF total + 1 > this_limit THEN
        RAISE EXCEPTION '%.% has % occurences, but must not have more than %', this_table, this_column, total, this_limit;
    END IF;

$$ LANGUAGE plpgsql;

CREATE TRIGGER fibre_route_edge_start_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
    FOR EACH ROW EXECUTE PROCEDURE limit_count('start_node', 2);
CREATE TRIGGER fibre_route_edge_end_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
    FOR EACH ROW EXECUTE PROCEDURE limit_count('end_node', 2);

The above fails with:

ERROR:  schema "new" does not exist
LINE 1: SELECT NEW.quote_literal(this_column)
QUERY:  SELECT NEW.quote_literal(this_column)

Happy to solve this problem without a trigger, but I'm kinda stumped right now. And examples I've seen using hstore and other tools have been incomprehensible to me.

  • Unrelated, but: why do you use count(%I)? If you think that is faster than count(*) then you are wrong - it's actually slower (assuming you do not intend to only count non-null values)
    – user1822
    Jun 2, 2020 at 12:46
  • Thank, a_horse_with_no_name. Fixed!
    – Curtis Poe
    Jun 2, 2020 at 13:00

1 Answer 1


You can't access the fields of the new record dynamically using names stored in a variable.

The only way I can think of, is to convert the NEW record into a JSON value, then you can access the value "dynamically" through the name:

this_column_value := (to_jsonb(new) ->> this_column)::int; 
EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE %I = $1', this_table, this_column)
  USING this_column_value
  INTO total;
  • Thank you. That's giving me ERROR: operator does not exist: bigint = text ... LINE 1: ...CT COUNT(end_node) FROM fibre_route_edge WHERE end_node = $1 for the query QUERY: SELECT COUNT(end_node) FROM fibre_route_edge WHERE end_node = $1. However, it's not clear to me what's in $1. It's probably not the value of the column because that's a BIGINT. I tried casting it (::integer) in both the quoted string and the USING section and both times I get ERROR: invalid input syntax for integer: "end_node", so it looks like that string is passed in, not the value of that column.
    – Curtis Poe
    Jun 2, 2020 at 12:58
  • Ah, I didn't see that this_column is a string that contains a column name. See my edit for a possible workaround.
    – user1822
    Jun 2, 2020 at 13:11
  • Thank you! That appears to have done the trick. Tests are now in place. Thank you!
    – Curtis Poe
    Jun 2, 2020 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.