sql 문

정리가 필요하지만 매번 썼던거 또 찾기도 싫고... 반복해서 사용하는 것은 저장해두기로.

ex) 테스트로 사용하는 데이터  간단히 만들때.

 

 

일단... 러프하게 적어둔다...

---

# table copy

select * into 신_테이블 from 구_테이블;

# 테이블 생성

create table 이름(
컬럼이름 자료형타입
);

# 테이블 삭제 

DROP TABLE table_name;

---

# 컬럼 추가
alter table expedia_region_union_copy add column "created_at" TIMESTAMP default now()::timestamp

# 컬럼 삭제 

alter table json_b_test_region_100_007 drop column "updated_at"

# 컬럼 복사 : https://stackoverflow.com/questions/3361768/copy-data-from-one-column-to-other-column-which-is-in-a-different-table
insert into the_reason_why_cannot_mapping_hibernate(region_id) select region_id from the_reason_why_cannot_mapping_hibernate1


# existing table에서 2개 이상의 컬럼 복사하여 테스트테이블 만들기

create table test_hibernate(
region_id text,
boundaries geometry
);

INSERT INTO test_hibernate(region_id, boundaries)
SELECT region_id, boundaries
FROM expedia_region_union as eru
where coordinates is not NULL ORDER BY region_id ASC LIMIT 10

---

# 본래 테이블에서 몇개만 복사해서 테스트 테이블 만들기

SELECT *
INTO 신_테이블
FROM 구_테이블 ORDER BY region_id asc limit 10 ;

---

# 날짜
select now()::timestamp

---

select * from expedia_region_codes_city_only order BY region_id asc limit 100;

select * from expedia_region_codes_city_only ORDER BY region_id ASC LIMIT 100;

select * from json_b_test_region_100_001

select * from json_b_test_region_100_001 where st_geometryfromtext 

INSERT INTO json_b_test_region_100_002(region_id, region_name, region_type, geojson, es, polygon) VALUES ("test", "1", "test", "", "",  ST_GeomFromText('POLYGON((-97.081857 32.917045), (-97.081003 32.895065), (-97.072896 32.884492), (-97.022236 32.954406), (-97.041477  32.954471), (-97.081857 32.917045))')

--CREATE TABLE json_b_test_region_100_003 (polygon geometry)

CREATE TABLE json_b_test_region_100_004 (name text, polygon geometry)

INSERT INTO json_b_test_region_100_004(name, polygon) values ('minji', ST_GeomFromText('POLYGON((-97.081857 32.917045, -97.081003 32.895065, -97.072896 32.884492, -97.081857 32.917045))'));

INSERT INTO json_b_test_region_100_004(name, polygon) values ('minji', ST_GeomFromText('POLYGON((50.6373 3.0750,50.6374 3.0750,50.6374 3.0749,50.63 3.07491,50.6373 3.0750))'));

select region_id FROM json_b_test_region_100_005 WHERE ST_Contains(polygon, ST_GeomFromText('POINT(-97.081857 32.907045)'));

SELECT * FROM json_b_test_region_100_005 where ST_Contains(polygon, st_geomfromtext('POINT(-97 32.917045)'));

SELECT count(*) FROM json_b_test_region_100_006


select ST_Contains(ST_GeomFromText('POINT(-97.081857 32.917045)'), polygon)
from json_b_test_region_100_005 as jbtr


SELECT ST_Distance(
  ST_GeometryFromText('POINT(0 5)'),
  ST_GeometryFromText('LINESTRING(-2 2, 2 2)'));

SELECT count(*) FROM json_b_test_region_100_006

select * from expedia_region_codes as erc where region_id = '10065'

--됨.
SELECT 
  ST_DWithin(
  'POLYGON((-97.081857 32.917045, -97.081003 32.895065, -97.072896 32.884492, -97.072738 32.875025, -97.064945 32.875418, -97.06039 32.867376, -97.058463 32.867376, -97.054987 32.8688, -97.056073 32.863226, -97.063326 32.855577, -97.065492 32.855252, -97.06472 32.837022, -97.034612 32.837046, -97.034804 32.817252, -96.993481 32.816187, -96.994171 32.778122, -96.977512 32.778057, -96.977362 32.783248, -96.960088 32.783569, -96.959706 32.787005, -96.95099 32.78707, -96.950758 32.780257, -96.943511 32.781428, -96.941722 32.777204, -96.936244 32.764759, -96.914189 32.766118, -96.923984 32.761064, -96.925603 32.747711, -96.943177 32.731892, -96.954966 32.723143, -96.95612 32.703106, -96.96267 32.689138, -96.971071 32.690242, -96.975531 32.670543, -96.985005 32.672162, -96.984697 32.667753, -96.979151 32.656159, -96.998945 32.644162, -96.971686 32.640412, -96.976228 32.626082, -96.975606 32.619986, -96.959521 32.633216, -96.952589 32.633151, -96.949283 32.620181, -96.943737 32.618692, -96.943498 32.66247, -96.926094 32.663184, -96.926251 32.677124, -96.890967 32.676998, -96.890967 32.647946, -96.865333 32.647891, -96.865169 32.633431, -96.84392 32.63337, -96.844385 32.625522, -96.831523 32.626362, -96.831981 32.633431, -96.82293 32.633529, -96.822946 32.638227, -96.822948 32.640142, -96.822961 32.642191, -96.813755 32.64222, -96.809126 32.642253, -96.804126 32.642299, -96.795842 32.644021, -96.791695 32.644907, -96.78954 32.645356, -96.78774 32.645749, -96.78363 32.638745, -96.779006 32.641211, -96.766767 32.619098, -96.736133 32.61891, -96.732199 32.629753, -96.721735 32.647013, -96.729747 32.65817, -96.71373 32.66621, -96.710028 32.66109, -96.691634 32.669853, -96.687243 32.666869, -96.688629 32.651566, -96.665447 32.639971, -96.648816 32.639708, -96.644964 32.636536, -96.622328 32.655148, -96.612391 32.649896, -96.58744 32.656804, -96.597221 32.67165, -96.587747 32.679692, -96.582973 32.674802, -96.556854 32.687212, -96.558862 32.689415, -96.553849 32.693237, -96.560324 32.698298, -96.561711 32.702256, -96.586443 32.703622, -96.61403 32.700763, -96.613948 32.696546, -96.624193 32.694661, -96.624733 32.690382, -96.628489 32.689535, -96.632492 32.690959, -96.637963 32.694142, -96.636112 32.704575, -96.639424 32.704636, -96.639493 32.755818, -96.673657 32.791612, -96.68423 32.792192, -96.682222 32.79751, -96.673507 32.79907, -96.673125 32.807178, -96.668029 32.807372, -96.667415 32.804521, -96.656459 32.809831, -96.65885 32.834803, -96.639247 32.840181, -96.635156 32.843941, -96.648365 32.856512, -96.683083 32.874319, -96.684291 32.878919, -96.690521 32.88579, -96.691949 32.903794, -96.697816 32.902172, -96.698041 32.908791, -96.700596 32.90892, -96.700828 32.916242, -96.706538 32.916372, -96.706463 32.931153, -96.717664 32.931299, -96.717972 32.925596, -96.736126 32.924818, -96.737055 32.923264, -96.748489 32.922878, -96.748482 32.939608, -96.769109 32.939738, -96.768255 32.985658, -96.756665 32.986173, -96.756583 32.997392, -96.768255 32.997457, -96.767771 33.00749, -96.794757 33.013125, -96.819024 33.013125, -96.843381 33.016373, -96.844002 33.010277, -96.865729 33.011243, -96.865572 33.001715, -96.871371 33.001589, -96.873768 32.990114, -96.854753 32.990053, -96.854746 32.987461, -96.838791 32.987392, -96.828341 32.987416, -96.828259 32.97922, -96.821572 32.973548, -96.822104 32.954227, -96.829733 32.954404, -96.827958 32.947511, -96.831257 32.946698, -96.831353 32.950847, -96.838661 32.95122, -96.839016 32.940022, -96.840642 32.939929, -96.845519 32.939858, -96.845068 32.932676, -96.855771 32.933273, -96.855798 32.917059, -96.88805 32.909419, -96.90921 32.906653, -96.91156 32.895437, -96.928485 32.896189, -96.926374 32.891845, -96.930746 32.890434, -96.93078 32.870962, -96.924489 32.859374, -96.889505 32.847039, -96.888754 32.830909, -96.904982 32.815702, -96.898309 32.797906, -96.913506 32.784495, -96.941346 32.777313, -96.943272 32.781725, -96.950444 32.780502, -96.950751 32.787374, -96.959931 32.787179, -96.960477 32.783877, -96.977594 32.783556, -96.977826 32.778303, -96.99387 32.778368, -96.993092 32.816429, -97.034517 32.817526, -97.034127 32.837801, -97.02255 32.838061, -97.021696 32.860354, -96.997989 32.888556, -96.99189 32.889202, -96.991575 32.89736, -97.016212 32.910266, -97.021314 32.923745, -97.022625 32.943775, -97.022236 32.954406, -97.041477 32.954471, -97.040466 32.934373, -97.052972 32.927683, -97.060075 32.926966, -97.062008 32.922492, -97.059768 32.9199, -97.060929 32.918022, -97.081857 32.917045))'::GEOMETRY,
  'POINT (-97.081857 32.9170445)'::GEOMETRY,
  1);
 
SELECT region_id, region_name
FROM json_b_test_region_100_005
WHERE ST_DWithin(
        polygon,
        'POINT (-97.081857 32.9170445)'::GEOMETRY,
        10
      );

SELECT *
FROM json_b_test_region_100_006
WHERE ST_DWithin(
        polygon, ST_GeometryFromText( 'POINT (-97.081857 32.9170445)'),
        0.05
      );
     
 ---97.188669, 32.910463
 
     SELECT *
FROM json_b_test_region_100_006
WHERE ST_DWithin(
        polygon, ST_GeometryFromText( 'POINT (-97.188669 32.910463)'),
        0.01
      );
     
 ---97.548019, 32.553261
 --이거잘됨.
 
SELECT *
FROM json_b_test_region_100_006
WHERE ST_DWithin(
        polygon, ST_GeometryFromText( 'POINT(127.0619248 37.4940372)'),
        0.01
      );
     
     
 select count(*) from json_b_test_region_100_006 --339,722
 
 select * from json_b_test_region_100_006 
 
 select count(*) from expedia_region_codes as erc --356,718

SELECT *
FROM json_b_test_region_100_006
WHERE ST_Contains(
        polygon, ST_GeometryFromText('POINT (-97.081857 32.9170445)')
      );
     
 select * from expedia_region_codes as erc where region_id = '100241'
 
 select * from expedia_region_codes as erc where region_id = '4347983'
 
 select * from expedia_region_codes as erc where coordinates is not null
 
 select count(*) from expedia_region_union as eru
 

 select count(*) from json_b_test_region_100_007
 
 SELECT 
  ST_Contains(
  'POLYGON((-97.081857 32.917045, -97.081003 32.895065, -97.072896 32.884492, -97.072738 32.875025, -97.064945 32.875418, -97.06039 32.867376, -97.058463 32.867376, -97.054987 32.8688, -97.056073 32.863226, -97.063326 32.855577, -97.065492 32.855252, -97.06472 32.837022, -97.034612 32.837046, -97.034804 32.817252, -96.993481 32.816187, -96.994171 32.778122, -96.977512 32.778057, -96.977362 32.783248, -96.960088 32.783569, -96.959706 32.787005, -96.95099 32.78707, -96.950758 32.780257, -96.943511 32.781428, -96.941722 32.777204, -96.936244 32.764759, -96.914189 32.766118, -96.923984 32.761064, -96.925603 32.747711, -96.943177 32.731892, -96.954966 32.723143, -96.95612 32.703106, -96.96267 32.689138, -96.971071 32.690242, -96.975531 32.670543, -96.985005 32.672162, -96.984697 32.667753, -96.979151 32.656159, -96.998945 32.644162, -96.971686 32.640412, -96.976228 32.626082, -96.975606 32.619986, -96.959521 32.633216, -96.952589 32.633151, -96.949283 32.620181, -96.943737 32.618692, -96.943498 32.66247, -96.926094 32.663184, -96.926251 32.677124, -96.890967 32.676998, -96.890967 32.647946, -96.865333 32.647891, -96.865169 32.633431, -96.84392 32.63337, -96.844385 32.625522, -96.831523 32.626362, -96.831981 32.633431, -96.82293 32.633529, -96.822946 32.638227, -96.822948 32.640142, -96.822961 32.642191, -96.813755 32.64222, -96.809126 32.642253, -96.804126 32.642299, -96.795842 32.644021, -96.791695 32.644907, -96.78954 32.645356, -96.78774 32.645749, -96.78363 32.638745, -96.779006 32.641211, -96.766767 32.619098, -96.736133 32.61891, -96.732199 32.629753, -96.721735 32.647013, -96.729747 32.65817, -96.71373 32.66621, -96.710028 32.66109, -96.691634 32.669853, -96.687243 32.666869, -96.688629 32.651566, -96.665447 32.639971, -96.648816 32.639708, -96.644964 32.636536, -96.622328 32.655148, -96.612391 32.649896, -96.58744 32.656804, -96.597221 32.67165, -96.587747 32.679692, -96.582973 32.674802, -96.556854 32.687212, -96.558862 32.689415, -96.553849 32.693237, -96.560324 32.698298, -96.561711 32.702256, -96.586443 32.703622, -96.61403 32.700763, -96.613948 32.696546, -96.624193 32.694661, -96.624733 32.690382, -96.628489 32.689535, -96.632492 32.690959, -96.637963 32.694142, -96.636112 32.704575, -96.639424 32.704636, -96.639493 32.755818, -96.673657 32.791612, -96.68423 32.792192, -96.682222 32.79751, -96.673507 32.79907, -96.673125 32.807178, -96.668029 32.807372, -96.667415 32.804521, -96.656459 32.809831, -96.65885 32.834803, -96.639247 32.840181, -96.635156 32.843941, -96.648365 32.856512, -96.683083 32.874319, -96.684291 32.878919, -96.690521 32.88579, -96.691949 32.903794, -96.697816 32.902172, -96.698041 32.908791, -96.700596 32.90892, -96.700828 32.916242, -96.706538 32.916372, -96.706463 32.931153, -96.717664 32.931299, -96.717972 32.925596, -96.736126 32.924818, -96.737055 32.923264, -96.748489 32.922878, -96.748482 32.939608, -96.769109 32.939738, -96.768255 32.985658, -96.756665 32.986173, -96.756583 32.997392, -96.768255 32.997457, -96.767771 33.00749, -96.794757 33.013125, -96.819024 33.013125, -96.843381 33.016373, -96.844002 33.010277, -96.865729 33.011243, -96.865572 33.001715, -96.871371 33.001589, -96.873768 32.990114, -96.854753 32.990053, -96.854746 32.987461, -96.838791 32.987392, -96.828341 32.987416, -96.828259 32.97922, -96.821572 32.973548, -96.822104 32.954227, -96.829733 32.954404, -96.827958 32.947511, -96.831257 32.946698, -96.831353 32.950847, -96.838661 32.95122, -96.839016 32.940022, -96.840642 32.939929, -96.845519 32.939858, -96.845068 32.932676, -96.855771 32.933273, -96.855798 32.917059, -96.88805 32.909419, -96.90921 32.906653, -96.91156 32.895437, -96.928485 32.896189, -96.926374 32.891845, -96.930746 32.890434, -96.93078 32.870962, -96.924489 32.859374, -96.889505 32.847039, -96.888754 32.830909, -96.904982 32.815702, -96.898309 32.797906, -96.913506 32.784495, -96.941346 32.777313, -96.943272 32.781725, -96.950444 32.780502, -96.950751 32.787374, -96.959931 32.787179, -96.960477 32.783877, -96.977594 32.783556, -96.977826 32.778303, -96.99387 32.778368, -96.993092 32.816429, -97.034517 32.817526, -97.034127 32.837801, -97.02255 32.838061, -97.021696 32.860354, -96.997989 32.888556, -96.99189 32.889202, -96.991575 32.89736, -97.016212 32.910266, -97.021314 32.923745, -97.022625 32.943775, -97.022236 32.954406, -97.041477 32.954471, -97.040466 32.934373, -97.052972 32.927683, -97.060075 32.926966, -97.062008 32.922492, -97.059768 32.9199, -97.060929 32.918022, -97.081857 32.917045))'::GEOMETRY,
  'POINT (-97.081857 32.9170445)'::GEOMETRY);

--POLYGON ((-97.081857 32.917045, -97.081003 32.895065, -97.072896 32.884492, -97.072738 32.875025, -97.064945 32.875418, -97.06039 32.867376, -97.058463 32.867376, -97.054987 32.8688, -97.056073 32.863226, -97.063326 32.855577, -97.065492 32.855252, -97.06472 32.837022, -97.034612 32.837046, -97.034804 32.817252, -96.993481 32.816187, -96.994171 32.778122, -96.977512 32.778057, -96.977362 32.783248, -96.960088 32.783569, -96.959706 32.787005, -96.95099 32.78707, -96.950758 32.780257, -96.943511 32.781428, -96.941722 32.777204, -96.936244 32.764759, -96.914189 32.766118, -96.923984 32.761064, -96.925603 32.747711, -96.943177 32.731892, -96.954966 32.723143, -96.95612 32.703106, -96.96267 32.689138, -96.971071 32.690242, -96.975531 32.670543, -96.985005 32.672162, -96.984697 32.667753, -96.979151 32.656159, -96.998945 32.644162, -96.971686 32.640412, -96.976228 32.626082, -96.975606 32.619986, -96.959521 32.633216, -96.952589 32.633151, -96.949283 32.620181, -96.943737 32.618692, -96.943498 32.66247, -96.926094 32.663184, -96.926251 32.677124, -96.890967 32.676998, -96.890967 32.647946, -96.865333 32.647891, -96.865169 32.633431, -96.84392 32.63337, -96.844385 32.625522, -96.831523 32.626362, -96.831981 32.633431, -96.82293 32.633529, -96.822946 32.638227, -96.822948 32.640142, -96.822961 32.642191, -96.813755 32.64222, -96.809126 32.642253, -96.804126 32.642299, -96.795842 32.644021, -96.791695 32.644907, -96.78954 32.645356, -96.78774 32.645749, -96.78363 32.638745, -96.779006 32.641211, -96.766767 32.619098, -96.736133 32.61891, -96.732199 32.629753, -96.721735 32.647013, -96.729747 32.65817, -96.71373 32.66621, -96.710028 32.66109, -96.691634 32.669853, -96.687243 32.666869, -96.688629 32.651566, -96.665447 32.639971, -96.648816 32.639708, -96.644964 32.636536, -96.622328 32.655148, -96.612391 32.649896, -96.58744 32.656804, -96.597221 32.67165, -96.587747 32.679692, -96.582973 32.674802, -96.556854 32.687212, -96.558862 32.689415, -96.553849 32.693237, -96.560324 32.698298, -96.561711 32.702256, -96.586443 32.703622, -96.61403 32.700763, -96.613948 32.696546, -96.624193 32.694661, -96.624733 32.690382, -96.628489 32.689535, -96.632492 32.690959, -96.637963 32.694142, -96.636112 32.704575, -96.639424 32.704636, -96.639493 32.755818, -96.673657 32.791612, -96.68423 32.792192, -96.682222 32.79751, -96.673507 32.79907, -96.673125 32.807178, -96.668029 32.807372, -96.667415 32.804521, -96.656459 32.809831, -96.65885 32.834803, -96.639247 32.840181, -96.635156 32.843941, -96.648365 32.856512, -96.683083 32.874319, -96.684291 32.878919, -96.690521 32.88579, -96.691949 32.903794, -96.697816 32.902172, -96.698041 32.908791, -96.700596 32.90892, -96.700828 32.916242, -96.706538 32.916372, -96.706463 32.931153, -96.717664 32.931299, -96.717972 32.925596, -96.736126 32.924818, -96.737055 32.923264, -96.748489 32.922878, -96.748482 32.939608, -96.769109 32.939738, -96.768255 32.985658, -96.756665 32.986173, -96.756583 32.997392, -96.768255 32.997457, -96.767771 33.00749, -96.794757 33.013125, -96.819024 33.013125, -96.843381 33.016373, -96.844002 33.010277, -96.865729 33.011243, -96.865572 33.001715, -96.871371 33.001589, -96.873768 32.990114, -96.854753 32.990053, -96.854746 32.987461, -96.838791 32.987392, -96.828341 32.987416, -96.828259 32.97922, -96.821572 32.973548, -96.822104 32.954227, -96.829733 32.954404, -96.827958 32.947511, -96.831257 32.946698, -96.831353 32.950847, -96.838661 32.95122, -96.839016 32.940022, -96.840642 32.939929, -96.845519 32.939858, -96.845068 32.932676, -96.855771 32.933273, -96.855798 32.917059, -96.88805 32.909419, -96.90921 32.906653, -96.91156 32.895437, -96.928485 32.896189, -96.926374 32.891845, -96.930746 32.890434, -96.93078 32.870962, -96.924489 32.859374, -96.889505 32.847039, -96.888754 32.830909, -96.904982 32.815702, -96.898309 32.797906, -96.913506 32.784495, -96.941346 32.777313, -96.943272 32.781725, -96.950444 32.780502, -96.950751 32.787374, -96.959931 32.787179, -96.960477 32.783877, -96.977594 32.783556, -96.977826 32.778303, -96.99387 32.778368, -96.993092 32.816429, -97.034517 32.817526, -97.034127 32.837801, -97.02255 32.838061, -97.021696 32.860354, -96.997989 32.888556, -96.99189 32.889202, -96.991575 32.89736, -97.016212 32.910266, -97.021314 32.923745, -97.022625 32.943775, -97.022236 32.954406, -97.041477 32.954471, -97.040466 32.934373, -97.052972 32.927683, -97.060075 32.926966, -97.062008 32.922492, -97.059768 32.9199, -97.060929 32.918022, -97.081857 32.917045))
 
 
 --drop table json_b_test_region_100_005
 
 
 
select * from json_b_test_region_100_007

select * into json_b_test_region_100_007_copy from json_b_test_region_100_007 as jbtr

select * from json_b_test_region_100_008

alter table json_b_test_region_100_007 add column "updated_at" TIMESTAMP default now()::timestamp





alter table json_b_test_region_100_007 drop column "updated_at"

select count(*) from json_b_test_region_100_008

select * from json_b_test_region_100_008 limit 10;

--alter table json_b_test_region_100_008 drop column updated_at 

--alter table json_b_test_region_100_008 add column "updated_at" TIMESTAMP default now()::timestamp







alter table json_b_test_region_100_007_copy add column "created_at"  default now()::timestamp


select now()::timestamp






 

---

# psql

# https://stackoverflow.com/a/16708680/10194999

pg_dump -t table_to_copy source_db | psql target_db
pg_dump DB이름 --table=테이블이름 --format=t --verbose --port=25432 -U 유저 > 파일이름.tar