Resshiftでスキーマとテーブルを作成した時のSQLメモ
テーブル
テーブル一覧の表示
select * from pg_tables
where schemaname = '<スキーマ名>'
テーブルのカラム一覧の表示
select *
from information_schema.columns
where
table_catalog = 'dev'
and table_schema = '<schema_name>'
and table_name = '<TABLE_NAME>'
order by
table_catalog, table_schema, table_name, ordinal_position
;
https://dataedo.com/kb/query/amazon-redshift/list-columns-names-in-specific-table
https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TABLE.html
https://stackoverflow.com/questions/50209790/retrieving-column-names-from-table-redshift
https://qiita.com/kazuho39/items/ebf4c69c91cedb655460
テーブルの所有者を変更
ALTER TABLE <schemaname>.<tablename> OWNER TO <new_user>;
テーブルの所有者変更SQLを一括作成
SELECT
'ALTER TABLE ' || schemaname || '.' || tablename || ' OWNER TO new_user;'
FROM pg_tables
WHERE tableowner ='old_user'
スキーマ
スキーマ一覧の表示
select
n.nspname as "schema"
,pg_catalog.pg_get_userbyid(n.nspowner) as "owner"
,n.nspacl
from pg_catalog.pg_namespace n
where n.nspname !~ '^pg_' and n.nspname <> 'information_schema' order by 1;
スキーマ名の変更
ALTER SCHEMA name RENAME TO new_name
スキーマ所有者の変更
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
Comments
Let's comment your feelings that are more than good