SQL色々 - ehatoovo @ ウィキ

---

テーブル情報取得

PostgreSQL

select
   A.table_catalog,
   A.table_schema,
   A.table_name,
   A.ordinal_position,
   A.column_name,
   A.data_type,
 case A.data_type 
   when 'numeric' then coalesce(A.numeric_precision, -1) 
   when 'character varying' then A.character_maximum_length
   when 'character' then A.character_maximum_length
 end AS data_length,
 B.constraint_type
 from information_schema.columns A
 left outer join
 ( select
     ccu.table_catalog,
     ccu.table_schema,
     ccu.table_name,
     ccu.column_name,
   case tc.constraint_type when 'PRIMARY KEY' then 'P' when 'UNIQUE' then 'U' else '' end as constraint_type
   from
     information_schema.table_constraints tc
    ,information_schema.constraint_column_usage ccu
   where
         tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
     and tc.table_catalog=ccu.table_catalog
     and tc.table_schema=ccu.table_schema
     and tc.table_name=ccu.table_name
     and tc.constraint_name=ccu.constraint_name
 ) B
 on  A.table_catalog = B.table_catalog
 and A.table_schema = B.table_schema
 and A.table_name = B.table_name
 and A.column_name = B.column_name
 where A.table_schema = 'public'
   order by A.table_name, A.ordinal_position;
 

Oracle

SELECT 
A.OWNER,
A.TABLE_NAME,
A.COLUMN_ID,
A.COLUMN_NAME,
A.DATA_TYPE,
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE,
B.CONSTRAINT_TYPE
FROM ALL_TAB_COLUMNS A
LEFT JOIN 
(SELECT CONS.OWNER, CONS.TABLE_NAME, COLS.COLUMN_NAME, CONS.CONSTRAINT_TYPE
 FROM ALL_CONSTRAINTS CONS, 
      ALL_CONS_COLUMNS COLS
 WHERE CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
   AND CONS.OWNER = COLS.OWNER
   AND CONS.CONSTRAINT_TYPE IN ('P','U')
) B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE 1=1
ORDER BY A.TABLE_NAME, A.COLUMN_ID
 

日付

PostgreSQL

• 現在時刻より2年前の1秒前

select to_timestamp(to_char(current_timestamp + '-2 years -1 days','yyyy/mm/dd 23:59:59'),'yyyy/mm/dd HH24:MI:SS');

• 現在時刻より2年前ちょっきし

select to_timestamp(to_char(current_timestamp + '-2 years','yyyy/mm/dd 00:00:00'),'yyyy/mm/dd HH24:MI:SS');

• 現在時刻より2年前の1秒後

select to_timestamp(to_char(current_timestamp + '-2 years','yyyy/mm/dd 00:00:01'),'yyyy/mm/dd HH24:MI:SS');