SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET DEFAULT ''infinity'';', A.table_schema, A.table_name , A.column_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position; | Declare default value to the column. |
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL;', A.table_schema, A.table_name , A.column_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.ordinal_position; | Add NOT NULL constraint to the column. |
SELECT format('ALTER DOMAIN %1$I.%2$I SET DEFAULT ''infinity'';', A.domain_schema, A.domain_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NOT NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.domain_schema, A.domain_name; | Declare default value to the domain. |
SELECT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;', A.domain_schema, A.domain_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.data_type IN ('timestamp without time zone','timestamp with time zone'))
AND coalesce(column_default, domain_default) IS NOT NULL
AND domain_name IS NOT NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.domain_schema, A.domain_name; | Add NOT NULL constraint to the domain. |