리눅스:: psql로 DDL문 추출하기, 데이터 건수 확인하기

:: psql로 DDL문 추출하기

데이터베이스 테이블에서 geom 컬럼의 데이터 타입이 geometry여야 하는데 character varying으로 설정되어있었다. 원인을 분석하기 위해 DDL을 추출해보았다.

 

1. ssh에서 psql로 접속한다.

https://dev-h2.tistory.com/6

꼭 필요한 psql로 리눅스에서 데이터베이스 연결, 확인하기

 

2. generate_create_table_statement 함수를 생성한다.

복잡해 보이지만 밑에 코드를 모두 복붙하고 엔터치면 된다.

CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
  RETURNS text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT c.oid,
                n.nspname,
                c.relname
              FROM pg_catalog.pg_class c
                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;

        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;

    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

3. 함수를 이용하여 DDL문을 추출한다

SELECT generate_create_table_statement('테이블명');

 

결과:

postgres=# SELECT generate_create_table_statement('테이블명');

                          generate_create_table_statement
------------------------------------------------------------------------------------
 CREATE TABLE 스키마.테이블명 (                                     +
     column1 integer DEFAULT NOT NULL,+
     column2 character varying  NULL,                                                 +
     column3 character varying  NOT NULL,                                              +
     column4 character varying  NULL,                                                +
     column5 character varying  NULL,                                                 +
     column6 bigint  NULL,                                                         +
     column7 character varying  NULL);
(1 row)

create문이 추출되었다.

4. 함수를 삭제해준다.

수정없이 밑 코드를 그대로 복사 붙여넣기 해준다.

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

 

 

:: 데이터 건수 확인하기

select count(*) from 테이블명;

리눅스에서도 count(*)함수를 이용하여 확인할 수 있다.

 

결과:

  count
---------
 2332814
(1 row)

총 2332814개의 데이터가 있는 것을 확인하였다.

 

 

 

 

출처:

https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr