digoal
2023-09-28
PostgreSQL , PolarDB , duckdb , 语法糖
duckdb增加了很多SQL语法糖, 目标是要让用户可以随心所欲的写SQL.
https://duckdb.org/2023/08/23/even-friendlier-sql.html
Union data type 支持不同类型存储在同一个字段内, 同时保留每个字段值类型
SELECT 'The Motion Picture' AS movie UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 'First Contact';
得到放大的类型, 自动将数值转换为varchar
movie
varchar
The Motion Picture
First Contact
6
5
4
3
2
However, if a UNION type is used, each individual row retains its original data type. A UNION is defined using key-value pairs with the key as a name and the value as the data type. This also allows the specific data types to be pulled out as individual columns:
CREATE TABLE movies (
movie UNION(num INT, name VARCHAR)
);
INSERT INTO movies
VALUES ('The Motion Picture'), (2), (3), (4), (5), (6), ('First Contact');
FROM movies
SELECT
movie,
union_tag(movie) AS type,
movie.name,
movie.num;
有点类似复合类型, 但是duckdb会自动根据类型写入到对应的子类型中
| movie union(num integer, name varchar) |
type varchar |
name varchar |
num int32 |
|---|---|---|---|
| The Motion Picture | name | The Motion Picture | |
| 2 | num | 2 | |
| 3 | num | 3 | |
| 4 | num | 4 | |
| 5 | num | 5 | |
| 6 | num | 6 | |
| First Contact | name | First Contact |
