SQL memo

sql_memo.md

Generate table as SQL

Presto:

with t1 as (select sequence(1,10) as c1)
select c from t1 
CROSS JOIN UNNEST (c1) as t(c)

BigQuery:

SELECT *
FROM UNNEST(ARRAY<STRUCT<sku STRING , description STRING, quantity INT64, price FLOAT64>>[
    ('GCH635354', 'Chair', 4, 345.7),
    ('GRD828822', 'Gardening', 2,9.5),
    ('ABC123456', 'Furniture', 3, 36.3),
    ('TBL535522', 'Table', 6, 878.4),
    ('CHR762222', 'Chair', 4, 435.6)
])
;

PostgreSQL

with t1 as (select id, 'abcd-' || id as txt from generate_series(1,100) as id) 
select * from t1;

 id  |   txt
-----+----------
   1 | abcd-1
   2 | abcd-2
   3 | abcd-3
   4 | abcd-4
   5 | abcd-5
   6 | abcd-6
   7 | abcd-7
...

Genrate long string

PostgreSQL

select repeat('a', 100);