copy testtable1 from '/1542783012050.CSV'  with delimiter ',' csv header;

select * from pg_indexes where tablename='log';
select * from pg_statio_all_indexes where relname='log';

SELECT to_regclass('core.job');
CREATE OR REPLACE FUNCTION public.__judge_function_exist( funname text )
RETURNS text
AS $$
DECLARE
 function_result text;
BEGIN
    select proname into function_result
    from pg_proc
    where proname = funname limit 1 ;
    return function_result;
END;
$$ LANGUAGE plpgsql;
查看不同值
select distinct st_geometrytype(the_geom_webmercator) from data.t_640fd1e07b9611e9948a898c1d7e1ea0
删除某 schema 下所有表
DROP FUNCTION public.__my_del_schema_table(text);
 CREATE OR REPLACE FUNCTION public.__my_del_schema_table(
  schemaname1 text)
    RETURNS record
    LANGUAGE 'plpgsql'
     COST 100
     VOLATILE
 AS $BODY$
 DECLARE
  tabname record;
BEGIN
   FOR tabname IN (SELECT tablename FROM pg_tables WHERE schemaname = schemaname1)
  EXECUTE 'DROP TABLE IF EXISTS ' || schemaname1 || '.' || quote_ident(tabname.tablename) || ' CASCADE';
  END LOOP;
 return tabname;
END;
 $BODY$;
批量更改数据库坐标系
CREATE OR REPLACE FUNCTION __batch_srid()
returns text
AS $$
DECLARE
    alter_content text;
 v_msg     TEXT;
BEGIN
    for alter_content in SELECT 'alter table ' || f_table_schema || '.' || f_table_name || ' alter column the_geom_webmercator type geometry(' || type || ',3857) using st_setsrid(the_geom_webmercator,3857);' FROM geometry_columns where f_geometry_column='the_geom_webmercator' and srid>900000
        raise info 'alter_content:%',alter_content;
        EXECUTE alter_content;
    END LOOP;
    RETURN null;
--以下是对异常的处理
EXCEPTION
 WHEN others THEN GET STACKED DIAGNOSTICS
            v_msg     = MESSAGE_TEXT;
            RAISE INFO 'EXCEPTION:%', v_msg;
       return null;
END;
$$ LANGUAGE plpgsql;
创建只读的用户
首先，授予连接访问权限：
GRANT CONNECT ON DATABASE table_name TO username;
然后授予模式使用
GRANT USAGE ON SCHEMA public TO username;
对于特定的表格
GRANT SELECT ON table_name TO username;
对于多个表