Postgresql – Is the double dollar sign ($$) quotation method in SQL standard

postgresqlsql-standard

In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example,

SELECT $f$ Hi $f$;

Is this quoting mechanism a part of any SQL standard, or is it postgres specific?

Best Answer

NO its postgresql specific many programing languages offer a way to write text that avoids having to be escaped

consider

Select $$";"'\e\r\n$$

select '\";\"\'\\e\\r\\n'

Also keep in mind Postgtesql from version 9.1 by default no longer allows passing escape string in a query, it has to have a leading E

select E'\r\n'

to be escaped now see standard conforming string

Related Solutions

Postgresql – SQL: SELECT All columns except some

Such a feature exists in neither Postgres nor the SQL Standard (AFAIK). I think this is a quite interesting question so I googled a little bit and came across an interesting article on postgresonline.com.

They show an approach that selects the columns directly from the schema:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = 'officepark' 
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM officepark As o' As sqlstmt

You could create a function that does something like that. Such topics were also discussed on the mailing lists, but the overall consensus was pretty much the same: query the schema.

I'm sure that there are other solutions but I think they will all involve some kind of magic schema-queriying-foo.

BTW: Be carefull with SELECT * ... as this can have performance penalties

SQL ISO Standard for group concatenation

The wiscorp.com page SQL Standards has several older revisions and drafts of SQL:20nn (zip):

The 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 has at page 289:

If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value.

Best Answer

Related Solutions

Postgresql – SQL: SELECT All columns except some

SQL ISO Standard for group concatenation

Related Question