1.列column_testが重複データ
SELECT
column_test
FROM
table_AA
GROUP BY
column_test
HAVING
COUNT(column_test) > 1

2.列column_testが重複データと重複件数
SELECT
COUNT(column_test) recordCnt
, column_test
FROM
table_AA
GROUP BY
column_test
HAVING
COUNT(column_test) > 1

形式
INSERT INTO M_STUDENT (SID,SNAME,SADDR) VALUES ( ? ,? ) RETURNING *
SQL構文例
INSERT INTO M_STUDENT(SID,SNAME,SADDR) VALUES ( 201 ,’yamada’,’山口県’ ) RETURNING *

形式
ALTER TABLE “シーケンス名” OWNER TO ownername;
testDB=# alter table d_sample_m_id_seq owner to yamada

SQL構文:
newsdb=# select setval(‘news_publish_num_seq‘,1001);
setval
——–
1001
(1 行)
^
newsdb=# select last_value from news_publish_num_seq;
last_value
————
1001
(1 行)

select now();
2017/12/18 23:51:20.017

select current_timestamp;
2017/12/18 23:51:43.548

select extract(year from now());
2017

select extract(month from now());
12

select to_char(to_date(‘2017-10-20 11:12:11′,’yyyy-mm-dd’), ‘yyyy-mm-dd’)
2017-10-20

select to_char(to_date(‘2017-10-20 11:12:11′,’yyyy-mm-dd’), ‘yyyy-mm-dd’)
2017-10-20

select age(now(), timestamp ‘1982-02-05’);
35 years 10 mons 13 days 23:55:14.634926

サンプル
create table employee(
empid SERIAL PRIMARY KEY,
empename VARCHAR(６0),
hometown VARCHAR(60),
phone VARCHAR(15),
email VARCHAR(30),
age INTEGER,
sex VARCHAR(4),
company VARCHAR(60),
regdate timestamp not null default current_timestamp
);

1.indexの作成
CREATE INDEX {index名} ON {Table名} ({Column名});
例
CREATE INDEX test_table_column1_idx ON test_table (column1);

indexが存在しない場合
CREATE INDEX IF NOT EXISTS {index名} ON {Table名} ({Column名});

2.indexの削除
DROP INDEX {index名};
例
DROP INDEX test_table_column1_idx;

3.indexの確認
\d test_table_column2_idx

1.拡張をインストール
CREATE EXTENSION postgres_fdw;

2.外部サーバーを作成
CREATE SERVER CFT_FDW FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (HOST ‘127.0.0.1’, DBNAME ‘DEMO’, PORT ‘5432’);

3.ユーザマッピング
CREATE USER MAPPING FOR public SERVER CFT_FDW
OPTIONS (USER ‘USER001’, PASSWORD ‘PWD001’);

4.外部サーバからテーブル定義をインポート
IMPORT FOREIGN SCHEMA PUBLIC
LIMIT
TO (USER_TBL, ADDRESS_TBL,TEST_TBL)
FROM
SERVER CFT_FDW
INTO PUBLIC;

参考URL
https://www.postgresql.jp/document/9.5/html/postgres-fdw.html

形式
concat(str1, str2 [, xxx])
引数（文字列）を結合する。

例1
SELECT CONCAT(ID, ‘ # ‘ || NAME) AS CFT FROM TEST_TBL;
結果
cft
—————
user001#山田001
user002#大崎001
user003#佐野001
user004#五田001

例2
SELECT
CONCAT(‘,’, TEST_TBL.*) AS CFT
FROM
TEST_TBL
結果
,(test01,data1,data2,xxxx)

SQL構文例
SELECT DISTINCT
T1.*
,T1.カラムA || ‘#’ || T1.カラムB 　TESTCD
, T2.カラムC || ‘_’ || T3.カラムC TESTNAME
FROM
TEST_TABLE1 T1
LEFT JOIN TEST_TABLE2 T2
ON T1.ID = T2.ID
LEFT JOIN TEST_TABLE3 T3
ON T1.カラム1 = T3.カラム2
ORDER BY
, T1.xxx
, T2.xxx