Ansible postgresql_table: how to create a foreign key constraint?-CodePudding

Creating databases and tables using the postgresql module in Ansible is pretty straight forward and works great, but I can´t really figure out how to create a foreign key constraint.

Eg. I create a table in Ansible like this:

    - name: "Create foo table"
      become: yes    
      become_user: postgres
      community.postgresql.postgresql_table:
        db: test_db
        owner: postgres
        name: foo
        columns:
          - id bigserial PRIMARY KEY NOT NULL
          - bar_id bigint

Now I would like to create a foreign key constraint that references the id column in the table bar. In Postgresql I could add a foreign key constrain this like this:

ALTER TABLE foo ADD CONSTRAINT bar_id_fkey FOREIGN KEY (bar_id) REFERENCES bar (id) MATCH FULL;

How can this be done in Ansible?

CodePudding user response：

You can create a foreign key constraint when you create the table by adding a CONSTRAINT to the columns list, like this:

    - name: "Create foo table"
      become: yes    
      become_user: postgres
      community.postgresql.postgresql_table:
        db: test_db
        owner: postgres
        name: foo
        columns:
          - id bigserial PRIMARY KEY NOT NULL
          - bar_id bigint
          - CONSTRAINT bar_id_fkey FOREIGN KEY (bar_id) REFERENCES bar (id) MATCH FULL;

Or just include the constraint in the column definition:

    - name: "Create foo table"
      become: yes    
      become_user: postgres
      community.postgresql.postgresql_table:
        db: test_db
        owner: postgres
        name: foo
        columns:
          - id bigserial PRIMARY KEY NOT NULL
          - bar_id bigint REFERENCES bar(id) MATCH FULL

But you cannot add a constraint after the fact using the postgresql_table module: it doesn't support the use of ALTER TABLE to synchronize the database table with changes in your playbook.

From the documentation, the postgresql_table module can be used to "change some table attributes", but looking at the source that appears to be limited to:

Renaming the table
Changing the table owner
Changing the tablespace
Changing store parameters