clear markdown compare help best fiddles feedback
≡
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601395 fiddles created (48002 in the last week).
CREATE TABLE tmp_data (raw text); INSERT INTO tmp_data VALUES ('1 2 3 4') -- first row is meant as "column name" , ('1 1 0 1') -- tab separated , ('1 0 0 1') , ('1 0 1 1');
✓
4 rows affected
 hidden batch(es)
-- Create CREATE TABLE script SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)', raw FROM (SELECT raw FROM tmp_data LIMIT 1) t;
?column? raw
CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool) 1 2 3 4
…
 hidden batch(es)
-- Safer, more generic query SELECT 'CREATE TABLE tbl(' || string_agg(quote_ident('col' || col), ' bool, ' ORDER BY ord) || ' bool)' FROM (SELECT raw FROM tmp_data LIMIT 1) t , unnest(string_to_array(t.raw, E'\t')) WITH ORDINALITY c(col, ord);
?column?
CREATE TABLE tbl(col1 bool, col2 bool, col3 bool, col4 bool)
…
 hidden batch(es)
-- Execute dynamically DO $$BEGIN EXECUTE ( SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)' FROM (SELECT raw FROM tmp_data LIMIT 1) t ); END$$;
✓
 hidden batch(es)
-- INSERT data INSERT INTO tbl SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).* FROM (SELECT raw FROM tmp_data OFFSET 1) t;
3 rows affected
 hidden batch(es)
TABLE tbl;
col1 col2 col3 col4
t t f t
t f f t
t f t t
…
 hidden batch(es)