I have a database (postgresql) with 10 schema and every schema has 10 tables. For some reasons, some tables do not have a primary key.

Is it possible to create a function/trigger that can look for all tables and

  • add a column id_pk serial (if the column does not exist)
  • and make it a primary key (if the table has not any primary key)?
  • 1
    Postgresql has INFORMATION_SCHEMA catalog. It contains a view called tables. postgresql.org/docs/9.1/static/infoschema-tables.html Use it to get a list of tables. Another view, called table_constraints will give you a list of all constraints for the tables. Using these you will be able to build a dynamic SQL statements to generate keys
    – cha
    Jun 26 '14 at 0:48
  • 1
    Yep, and you can use PL/PgSQL's EXECUTE with the format function to generate an appropriate ALTER TABLE ... ADD PRIMARY KEY ... for each. Jun 26 '14 at 1:50
  • If a table doesn't have a PK, a person understanding the design should make a conscious decision about adding one or not. An artificial ID that would never going be used by any query is worthless and worse than that, a useless drag on write performance and a threat to previously-working code. Jun 26 '14 at 11:09
  • This related question has some answers demonstrating how to automate: dba.stackexchange.com/questions/66387/… Jun 27 '14 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.