Skip to content

Instantly share code, notes, and snippets.

@miguelff
Forked from AlexeyKupershtokh/dump.sql
Last active June 14, 2021 16:06
Show Gist options
  • Save miguelff/0c79b0ae76a0abe1f7771191149a2d3c to your computer and use it in GitHub Desktop.
Save miguelff/0c79b0ae76a0abe1f7771191149a2d3c to your computer and use it in GitHub Desktop.
Postgres DDL to Clickhouse converter
select
concat(
'create table ',
table_name,
'(',
string_agg(
concat(
column_name,
' ',
CASE when is_nullable = 'YES' THEN 'Nullable(' END,
CASE
when data_type = 'integer' then 'Int32'
when data_type = 'smallint' then 'Int16'
when data_type = 'bigint' then 'Int64'
when data_type = 'numeric'
then concat('Decimal64(12)')
when data_type = 'double precision' then 'Float64'
when data_type = 'character varying' then 'String'
when data_type = 'character' then 'String'
when data_type = 'text' then 'String'
when data_type = 'boolean' then 'UInt8'
when data_type = 'date' then 'Date'
when data_type = 'timestamp without time zone' then 'Datetime'
when data_type = 'timestamp with time zone' then 'Datetime'
when data_type = 'time without time zone' then 'String'
when data_type = 'uuid' then 'UUID'
when data_type = 'json' then 'TEXT'
else concat('###################', data_type)
END,
CASE when is_nullable = 'YES' THEN ')' END
),
', ' order by ordinal_position
),
') ',
'ENGINE = MergeTree() '
'PARTITION BY id '
'ORDER BY [id] '
) as table_ddl
from information_schema.columns
where true
and table_schema = 'public'
and table_name = 'raw_logs'
group by table_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment