1

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)?
peterh
  • 2,077
  • 8
  • 28
  • 40
Milad
  • 11
  • 1
  • 1
    Postgresql has INFORMATION_SCHEMA catalog. It contains a view called tables. http://www.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 00: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. – Craig Ringer Jun 26 '14 at 01: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. – Daniel Vérité Jun 26 '14 at 11:09
  • This related question has some answers demonstrating how to automate: http://dba.stackexchange.com/questions/66387/change-column-type-from-varchar-to-text-in-all-tables-at-once – Erwin Brandstetter Jun 27 '14 at 00:02

0 Answers0