I have a "comment"-type column that is rarely used -- around 6% non-null in a population of 3 million records. The average length (when used) is 6 characters, and the max so far is around 3KB. A maximum of 4000 characters is reasonable for this field. I have two options:

comments varchar(max) NULL -- this is the current column definition
comments varchar(4000) SPARSE NULL


My current understanding is that in both cases, a NULL value would require no storage -- just the column's NULL bit set and a length of 0 in the row metadata.

But for the non-null cases, does one have a clear advantage over the other?

The extra 4-byte pointer for sparse columns with values suggests they are always stored off-row like text or very large varchar(max) fields. Is that the case?

If so, I'd lean toward using varchar(max), since it only stores values off-row if the total row length exceeds 8KB, and the majority of my values are short and unlikely to put a row over the limit.

I haven't seen this particular situation addressed in the BOL, so I'm hoping someone here knows enough about the innards of MSSQL to give some insight.

(If it matters, I'm currently using 2008R2, but hoping to upgrade soon to 2014.)

Try here for a basic intro to PostgreSQL and JSON.

Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.

Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...


I didn't have a running instance of PostgreSQL so I downloaded, compiled and installed 9.4. 

To answer this, I first CREATEed a table (fred)

CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));

INSERT INTO fred VALUES (2,    43, 'asfasfasfd'      );
INSERT INTO fred VALUES (3,   435, 'ererere'         );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');


Then, to check:

test=# select * from fred;

 mary | jimmy |      paulie      
------+-------+------------------
    2 |    43 | asfasfasfd
    3 |   435 | ererere
    6 | 43343 | eresdfssfsfasfae


Then I issued this command

test=# COPY (SELECT ROW_TO_JSON(t) 
test(# FROM (SELECT * FROM fred) t) 
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=# 


I then quit psql and listed the file myfile.

test=# \q
[pol@polhost inst]$ more myfile 
{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
{"mary":3,"jimmy":435,"paulie":"ererere"}
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
[pol@polhost inst]$


(you can experiment with the output from

COPY (SELECT ROW_TO_JSON(t, TRUE)  -- <-- Note addition of "TRUE" here!


at your leisure).

It was pointed out by @offby1 that the output (while corresponding to the OP's question) is not correct JSON. @EvanCarroll pointed out that \o is also a way of outputting to a file, so I combined the solutions to these two niggles in this statement (with help from here):

test=# \o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
                                     -- <-- "TRUE" here will produce plus
                                        ("+) signs in the output. "FALSE"
                                        is the default anyway.
test=# \o


gives:

[pol@polhost inst]$ more out.json 
                                                                   ok_json                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"},{"mary":3,"jimmy":435,"paulie":"ererere"},{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}]
(1 row)
[pol@polhost inst]$ 


FINALLY, there is the backslash (\) problem alluded to by @AdamGent in his post. This was a bit tricky, but it is possible without resorting to post-query processing. Voilà:

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\\sfs\\gf');


And using REGEXP_REPLACE thus (note the cast ::TEXT) removes the excess blackslashes.

test=# \o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g') 
test=# FROM (SELECT * FROM fred) AS t;  -- I found that using a CTE was helpful for legibility
test=# \o
test=# \q


gives:

[pol@polhost inst]$ more slash.json 
                    regexp_replace                    
------------------------------------------------------
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
(5 rows)
[pol@polhost inst]$ 


(p.s. As for @Zoltán 's comment - this may be a version thing - unable to reproduce!).
