18 lines
746 B
SQL
18 lines
746 B
SQL
-- PostgreSQL treats unquoted identifiers as case-insensitive, converting them to lowercase. If your column's actual name includes uppercase letters or mixed case, you should quote it when referencing it in your SQL query
|
|
|
|
create table instances as select
|
|
id as rowid
|
|
, host as domain
|
|
, config->>'name' as name
|
|
, '' as slogan
|
|
, config->>'description' as description
|
|
-- won't caputure long description, not used yet
|
|
, languages
|
|
, CASE WHEN config->'registrationsOpen' = 'true' THEN 1 ELSE 0 END as open
|
|
, config->>'version' as version
|
|
, "connectivityStats"->>'country' as location
|
|
, 0 as failure
|
|
, CAST(extract(epoch from "createdAt") as integer) as "createdAt"
|
|
, CAST(extract(epoch from "updatedAt") as integer) as "updatedAt"
|
|
from instance;
|