2

Does anyone have any good references to read regarding whether PostgreSQL conforms to all 12 of Codd's rules?

If not, are there any veteran PostgreSQL users that have an opinion on this topic?

Thank you.

user1568128
  • 43
  • 2

2 Answers2

1

PostGreSQL do not comply to the 12 rules. Rules that PG is unnable to respects are :

2 - The information rule : because you can create table without a PRIMARY KEY. But most of the RDBMS (Oracle, DB2, SQL Server... ) provides tables without PKs, except SQL Azure.

3 - Systematic treatment of null values : the one and only marker that is accepted for unknown or inapplicable is the NULL one. PostGreSQL add some extra marker like infinity or -infinity (other RDBMS like SQL Server, does not use such ugly thing that cannot combine in algebraic operations nor functions...)

6 - The view updating rule : PostGreSQL does not accepts INSERT, UPDATE or DELETE in view containing JOINs. Most professionnal RDBMS accept this (Oracle, DB2, SQL Server... ) but limits it to one table in the view as a general standard rule of SQL about INSERTs/UPDATEs/DELETEs.

7 - Relational Operations Rule (for high-level insert, update, and delete) : PostGreSQL is unnable to UPDATE properly keys (PRIMARY or UNIQUE). This because, when updating a batch of rows, PostGreSQL acts row by row and checks the constraint each time it has updated the row and not like all other RDBMS do once all rows have been updated. The theory of relational databases being based on the fact that updates must be set-based and not row by row... Of course there is a possible ugly "rustine" for PostGreSQL (deferrable constraints...) but this affects some other logics.

11 - Distribution independence. When partitionning a table or an index (and more generally for any storage management operation) there must be no logical aspect likely to affect applications... More generally, Codd's theory is based on the fact that logical objects and logical processing must in no case be dependent to physical objects and vice versa. Physical/logical separation is essential and all major RDBMSs use it (Oracle, SQL Server, IBM DB2), but PostGreSQL requires the creation of additional tables to be able to divide a table into partitions...

Of course RDBMS that respects all those rule are very rare. I have work with about 20 differents RDBMS, but the only one I think thats respects plainely all the 12 Codd's rules is Microsoft Azure SQL

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • 2 **wrong!** - PostgreSQL **allows** PKs, but also allows you to not have them. It's up to you to make sure your system complies with Codd, but it **can** therefore it is compliant! 3 **wrong!** Again, you could use `COALESCE(NaN, NULL)` - so it **can** comply! 6... err, **wrong-ish** - you can do an `ON UPDATE DO INSTEAD`. 7... err, don't really understand what you're saying - expand, but it's probably wrong! PostgreSQL 11 - **wrong**. Declarative partitioning goes back to at least PostgreSQL 11! Your answer is from 2022 - you're an SQL Server fan-boi - fine, just don't knock other systems! – Vérace May 17 '23 at 10:41
  • @Vérace Rule 2 says that EVERY table must have a key. So a RDBMS that create tables without PK violate rule 2. Rule 3 says that the only marker for anything else as as real value must be NULL. PG violate this rule with infiny markers. Rule 6 all PG is unnable to do direct updates, insert... when the is a join in the view.... – SQLpro May 19 '23 at 12:44
0

My answer is based on other answers via Google.

In 1986 was created a first ANSI standard of the SQL language. The last ANSI standard is ANSI SQL:2003. It's requirements are nevertheless implemented only in few RDBMSs. So far the most widespread RDBMSs respect ANSI SQL:1999, respectively older ANSI SQL:1992. PostgreSQL supports completely ANSI SQL:1999 and partly ANSI:2003.

If strictly speaking Codd's rules:

Codd's 12 rules aren't all that is to the relational model. In fact, he expanded these 12 to 40 rules in his 1.990 book _The_Relational_Model_for_Database_Management.

But furthermore, if you care to read Christopher J Date's 1.999 _An_Introduction_to_Database_Systems_ you will see that the relational model comprises some basic elements and some principles.

The basic element is the domain, or data type. PostgreSQL does not really enforces domains because it accepts NULL, which by definition is not part of any domain. Thus the triplet domain, name and value called attribute breaks down, and so the tuple -- because it represents a proposition, and a proposition with missing information is another proposition, not the one declared in the relation's header --, and so also the relation breaks down.

Furthermore, the relation is a set, not a bag. A bag accepts duplicates, but not a relation. So because PostgreSQL does not enforce the necessity of declaring a candidate key for each and every table, its tables are not necessarily relations, but quite possibly and commonly bags of not tuples as shown above, but simply rows.

Also, the first principle is The Information Principle, where all the database must be represented by data. Object IDs violate this, with serious consequences about data independence, which by the way is necessary to another relational model sine qua non, namely the separation between user, logical and physical schemas. Also not properly supported by PostgreSQL.

Othya
  • 390
  • 1
  • 3
  • 18
  • [Postgres adds system columns like `ctid`](http://stackoverflow.com/questions/17500013/in-order-sequence-generation/17503095#17503095) effectively making *every* row unique, even if there is no unique key. – Erwin Brandstetter Jun 18 '14 at 17:06