PostgreSQL some operation memo

1. Left JOIN or RIGHT Join

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

2. Processing JSON

Official explanation address https://www.postgresql.org/docs/9.5/static/functions-json.html

--: is to get the JSON object

--: Is the string to get

#>: Obtaining a JSON object based on the path

#>>: Get string based on path

Give me a chestnut.

There is a JSON string {"A": {"B": "C"}}

Select ' {"a": {"B": "C"}} ':: JSON, ' A '->> ' B ' as result

return results

C

If I use a->> when I get a, that is, execute the statement select ' {"a": {"B": "C"} ':: JSON->> ' a '->> ' B ' as result then an error will be made

Execution information:

[Sql]select ' {"a": {"B": "C"}} ':: JSON->> ' a '->> ' B ' as result

[ERR] Error: operator does not exist: text->> unknown
Line 1:select ' {"a": {"B": "C"}} ':: JSON->> ' a '->> ' B ' as result
^
HINT: There is no operator matching the specified name and parameter type. You may need to increase the explicit type conversions.

The cause of the error is that ->> returned is text is not a JSON object so behind the->> depressed on the error.

What if I want to write this?

Simple, just turn the text into JSON.

Select (' {"a": {"B": "C"}} ':: JSON->> ' a ')::json->> ' B ' as result

Right, here's () is to be noted if:: JSON before the things are not used to be in parentheses, then the error will be

Select ' {"a": {"B": "C"}} ':: JSON->> ' a '::json->> ' B ' as result

[Sql]select ' {"a": {"B": "C"}} ':: JSON->> ' a '::json->> ' B ' as result

[ERR] Error: JSON type uses invalid input syntax
Line 1:select ' {"a": {"B": "C"}} ':: JSON->> ' a '::json->> ' B ' as result
^
DETAIL: Token "A" is invalid.
Context:json data, line 1:a

This can be inferred:: Higher priority

3.

PostgreSQL some operation memo