Your reasons are very valid however so obviously you have to think about the consequences before using non-standard features. However, not using advanced or novel features (which non-standard features tend to be) can also lead to stagnation and loss of competitiveness so you have to also be careful with being careful, if that makes sense.

For example: {"a": 1, "b": 2} (in psuedocode)

User 1: select JSON

User 2 select JSON

User 1: update JSON set "a" = 11

User 2: update JSON set "b" = 22

What would the value of the JSON column be after that. In other systems I've worked in (where it's JSON in a database column), the answer is {"a": 1, "b", 22} or a failure on the second update letting the client know that their copy is out of date.

I'd love to know if postgres can deal with this in the normal relational way so that the result is actually {"a": 11, "b": 22}

You could still achieve what you want by using read committed transaction isolation and keeping your values in separate rows, e.g. a row with a/1 and a second row with b/2. But basically you would need a separate record for every property in your object graph that you want this behaviour to apply to, which could get pretty messy. Does that make sense to you?

> Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available

SQL Server has had this feature for quite some time as well, in the ability to store and query on XML documents. It's awful. The few times that it has "saved the day" on certain queries just because the data was already stored as XML, it turned out to be a false profit and became a serious issue down the road.

This seems to be one of the most common mistakes regarding understanding 1NF I come across.

The atomicity requirement doesn't say anything about structured text, or internally atomic data types. That a data type could be further decomposed doesn't impact first normal form at all. Otherwise, using a data type like DATETIME would be a total no-no. Neither does storing arrays, and so forth.

What 1NF requires is that for the domain the value is atomic. If I store in a text field "192.168.1.1/24" that is structured text but it is still atomic. It may include both a netmask and an IP address, but the data taken as a whole represents one domain value. Similarly storing '1-509-555-1212" in a text field contains an area code number, an exchange number, an end terminal number, and a country-code designation, but it represents a single phone number and that's all that is required. I could represent the phone number as ARRAY[1,5,0,9,5,5,5,1,2,1,2] and it would still be atomic as long as the array was properly ordered.

What I am getting at is that all 1NF requires is that the item represents a single value in a domain.

There's no reason why JSON would necessarily violate that iether. Consider {ip: '192.168.1.1', netbits: '24'}

That's no different from storing '192.168.1.1/24' in a text field. And for my phone number, I could store it as {ccode: '1', areacode: '509', exchange: '555', terminal: '1212'} which again is no different from storing '1-509-555-1212' in a text field.

This isn't to say that JSON doesn't often violate 1NF, but 1NF is not as restricting as people sometimes think.

You can argue whether or not the Normal Forms are a good idea, but you can't argue that this tool--in extending the SQL syntax to be able to give access to fields within the JSON document--is not meant for violating 1NF.

How is that different from, say, pulling area codes from text phone numbers, subnet masks from CIDR designations, or dates from DATETIME fields?

Where first normal form is sometimes (productively) violated is something like storing a list of tags in a varchar[] array. Since the ordering doesn't matter, it is merely a set of tags (not a field which stores a single value as a whole), and violates 1NF, but is can be a reasonable tradeoff in certain cases.

You want a logging service that records events in an SQL table. Each event has base attributes common to all events such as date, event name, component name, user id ... these parameters become columns in the SQL table. Some events can also include arbitrary custom data particular to that kind of event, and you want that data to also be in the database but you don't want to play whack-a-mole with the table schema to make sure every possible custom parameter has a column in the SQL table, in this case just stash the custom data as JSON, and hopefully the database server offers JSON comprehensions that let you access any fields within the JSON data via SQL.

  create table misc_attrs (maintable_id foreign key, name text, value text)

See https://news.ycombinator.com/item?id=6574269 for anecdotal evidence.

CREATE TABLE event ( "references" text[], (was 2 tables, references and reference_on_event) "entities" text[], (ditto) "actors" text[], (ditto) "targets" text[], (ditto) "payload" json, (EAV) )

Gin index on all the text arrays. There are about 150k events saved per day and PostgreSQL is running on the same kinds of hardware as SQL Server was. The application is quite read heavy.

Maybe not, but you significantly edited your post after I answered. I was just replying to, "Why would I use this over a nullable column?" (paraphrasing)

Well, for example, they make a good way to store user defined fields.

> So complicating the SQL syntax is a significant issue for me. Sticking to as much standard, ANSI SQL as possible makes my programs more portable across RDBMSes. With some of the tools I've written, I can make a full transition from MySQL to MS SQL Server and back again and the application doesn't care. Having that sort of power makes upgrading your database a technology decision, not a business one.

I think the tradeoff is between having a database for multiple apps and having an app for multiple RDBMS's. If you are going to go with the former, absolutely avoid every PostgreSQL-ism. But if you want to make a more generally useful to an organization, you need a more intelligent database and that means going to things like user defined functions, complex triggers and more.

PostgreSQL has had an extraordinarily extensible dialect of SQL since they moved from Quel in 1995. This is because the db was designed so you can plug whatever programming languages you want into the back end.

For example, if you have a database of images tagged with location data, and you want to make it so you can run an SQL query that gives you all images matching a photo recognition algorithm for spotting sunsets within 20 miles of Sacramento California, you can build in the spacial and image recognition functions into the database and you can tune it so that the least expensive filters are run first, so that the image recognition algorithm outputs are indexed, and all kinds of other things.

Since it was founded in the 1980's, PostgreSQL has been aimed at those sorts of problems.

> It's not really any different of code, though.

Yes and no. On one hand, you are right. It is all code.

On the other hand, what it gives you is an ability to write code that can work in a set-based paradigm, and be subject to the various optimizations that PostgreSQL can give you in that regard. And because inside such extensions you have no transaction control, there are hard limits to what one should do or try to do inside the database.

For example, some people decide they want to send emails out of the database back-end MS SQL-style. This is a bad idea because it necessitates mixing transactional and non-transactional code in dangerous ways. You can, however, raise a message on commit and have another process process it, and handle transactions accordingly.

TL;DR: PostgreSQL has always been a development platform, and a very good one, but one which is also quite limited in what kinds of problems you can solve with it.

I generally view it as a good thing unless you care about cross-platform. If you do, then don't use the non-standard features.

PostgreSQL has been doing this for a long time. It started, actually, as a research project for how to manage very complex forms of data in a relational-like database (many people use it as a purely relational database but that doesn't even begin to scratch the surface of what PostgreSQL can do).

Now new data types are pretty much C-only, but you can also create a lot of the same functionality using composite types and sql. Composite types (and even arrays of composite types) can be tuple elements.

Add to this the object-oriented-inspired features, allowing you to use multiple inheritance to mix in tables and create common interfaces to data (both features older than PostgreSQL's use of SQL).

What this means is you can do some extremely advanced db-stuff with PostgreSQL. This can include:

- I want to create a common interface for notes but avoid a global notes table.

- I want to scan a series of images stored in the db for those matching an algorithm in a Java library showing which are likely images of sunsets.

- I want to add support for a third party Java full text search engine into the back-end so I can call its functions from inside SQL queries.

It's OSS. That alone makes it different. Anybody can continue work on it or even fork it.

Postgres is an awesome database and an awesome project. I think the core developers have a good blend of being committed to supporting the SQL standard and adding new non-standard features. If you read through the docs they usually specify if a given feature is standard SQL or a Postgres extenstion. If you want to use something new that isn't standardized yet you have that option. If you don't, you have the info that it's not. The choice is yours.

> It's the "extinguish" part I'm concerned about.

I'm not sure I understand this. Are you worried about Postgres extinguishing its competitor databases (MySQL, Oracle, SQL Server, ... etc)? I'll be honest that I do see that happening but it's not malicious. It's just a better product. For transactional/persistent storage of data I don't see myself recommending anything else.

It may not be a great idea for everyone to use it. But it doesn't strike me as a really bad idea, either.

Postgres offers among the closest to standard SQL you can get if that's what you want.