[B! PostgreSQL] spelunker1972ã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

spelunker1972 id:spelunker1972

PostgreSQLã«é–¢ã™ã‚‹spelunker1972ã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (6)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

PL/pgSQLãƒ‡ãƒãƒƒã‚¬ã‚’ä½¿ã£ã¦ã¿ã‚ˆã†
PostgreSQLã®PL/pgSQLã¯ãƒ—ãƒã‚°ãƒ©ãƒ ãƒã‚¸ãƒƒã‚¯ã‚’ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å´ã§å®Ÿè¡Œã•ã›ã‚‹éžå¸¸ã«å¼·åŠ›ãªæ©Ÿèƒ½ã§ã™ã€‚ä»Šå›žã¯ã€PL/pgSQLã¨ãã®ãƒ‡ãƒãƒƒã‚¬ã«ã¤ã„ã¦ã”ç´¹ä»‹ã—ã¾ã™ã€‚ â– PostgreSQLã®ã€ŒPL/pgSQLã€ã¨ã¯ PostgreSQLã®PL/pgSQLã¯ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å´ã«ãƒ—ãƒã‚°ãƒ©ãƒ ã®ãƒã‚¸ãƒƒã‚¯ã‚’åŸ‹ã‚è¾¼ã‚€ãŸã‚ã®ä»•çµ„ã¿ã§ã€é€šå¸¸ã®SQLï¼ˆDDLã€DMLç‰ï¼‰ã«åˆ¶å¾¡ã®ãŸã‚ã®æ§‹æ–‡ãŒè¿½åŠ ã•ã‚Œã¦ãŸã‚ˆã†ãªè¨€èªžä»•æ§˜ã«ãªã£ã¦ã„ã¾ã™ã€‚ PL/pgSQL - SQLæ‰‹ç¶šãè¨€èªž http://www.postgresql.jp/document/9.0/html/plpgsql.html ç§è‡ªèº«ã€ã“ã®PL/pgSQLã‚’ã‚ˆãä½¿ã†ã®ã§ã™ãŒã€PL/pgSQLã®ã²ã¨ã¤ã®é›£ç‚¹ã¯ã€ãƒã‚¸ãƒƒã‚¯ãŒè¤‡é›‘ã«ãªã£ã¦ãã‚‹ã¨ãƒ‡ãƒãƒƒã‚°ãŒé›£ã—ã„ã€ã¨ã„ã†ã“ã¨ã§ã—ãŸã€‚ ãã®ãŸã‚ã€ä¼çµ±çš„ãªãƒ‡ãƒãƒƒã‚°ã®æ–¹æ³•ã€ã„ã‚ã‚†ã‚‹ã€Œprintfãƒ‡ãƒãƒƒã‚°ã€ã«é ¼ã‚‹ã“ã¨ã«ãª
spelunker1972 2016/05/30
ãƒ‡ãƒãƒƒã‚¬æƒ…å ±ã€plpgsql ã®ãƒ‡ãƒãƒƒã‚°

PostgreSQL
ãƒªãƒ³ã‚¯
psql (ãƒãƒƒã‚¯ã‚¹ãƒ©ãƒƒã‚·ãƒ¥ã‚³ãƒžãƒ³ãƒ‰ï¼‰ Postgresqlãƒ¡ãƒ¢
ï¼”ï¼Žpsql (ãƒãƒƒã‚¯ã‚¹ãƒ©ãƒƒã‚·ãƒ¥ã‚³ãƒžãƒ³ãƒ‰ï¼‰ # psql [DbName] ã§ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã¸ã‚¢ã‚¯ã‚»ã‚¹å¾Œã«åˆ©ç”¨ â– ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ä¸€è¦§è¡¨ç¤º \l â– ãƒ†ãƒ¼ãƒ–ãƒ«ä¸€è¦§è¡¨ç¤º \d â– ãƒ†ãƒ¼ãƒ–ãƒ«è¡¨ç¤º \d [TableName] â– psqlçµ‚äº† \q â– ãã®ä»–ä¸»è¦ã‚³ãƒžãƒ³ãƒ‰ \? --- ãƒãƒƒã‚¯ã‚¹ãƒ©ãƒƒã‚·ãƒ¥ã‚³ãƒžãƒ³ãƒ‰ãƒ˜ãƒ«ãƒ—è¡¨ç¤º \h --- SQLãƒ˜ãƒ«ãƒ—è¡¨ç¤º \df --- é–¢æ•°ä¸€è¦§è¡¨ç¤º \dn --- ã‚¹ã‚ãƒ¼ãƒžä¸€è¦§è¡¨ç¤º \dp --- ãƒ†ãƒ¼ãƒ–ãƒ«ã®ã‚¢ã‚¯ã‚»ã‚¹æ¨©è¡¨ç¤º \dS --- ã‚·ã‚¹ãƒ†ãƒ ã®ãƒ†ãƒ¼ãƒ–ãƒ«ä¸€è¦§è¡¨ç¤º \dt --- ãƒ¦ãƒ¼ã‚¶ãƒ¼ãƒ†ãƒ¼ãƒ–ãƒ«ä¸€è¦§è¡¨ç¤º \dT --- åž‹ã®ä¸€è¦§è¡¨ç¤º \lo_export [OID] [filename] --- [OID]ãƒ©ãƒ¼ã‚¸ã‚ªãƒ–ã‚¸ã‚§ã‚¯ãƒˆã‚’[filename]ã«æ›¸ãå‡ºã™ \lo_import filename --- ãƒ•ã‚¡ã‚¤ãƒ«ã‚’ãƒ©ãƒ¼ã‚¸ã‚ªãƒ–ã‚¸ã‚§ã‚¯ãƒˆã¨ã—ã¦ä¿å˜ \lo_list ---
spelunker1972 2014/05/01
ã‚ˆãä½¿ã†

PostgreSQL

ã‚³ãƒžãƒ³ãƒ‰
ãƒªãƒ³ã‚¯
ã€ŽPostgreSQL8 å¤–éƒ¨ã‹ã‚‰ã®ã‚¢ã‚¯ã‚»ã‚¹åˆ¶å¾¡ã‚ã‚Œã“ã‚Œã€‚ã€
PostgreSQLã¯ã€ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«å¾Œã®çŠ¶æ…‹ã§ã¯ã€ãã®ã‚µãƒ¼ãƒãƒ¼å†…ã‹ã‚‰ã—ã‹ã‚¢ã‚¯ã‚»ã‚¹ã§ãã¾ã›ã‚“ã€‚ ãƒ¦ãƒ¼ã‚¶ãƒ¼ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã€ã¾ãŸã¯ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã‚µãƒ¼ãƒãƒ¼ã‹ã‚‰ã‚¢ã‚¯ã‚»ã‚¹ã•ã›ã‚‹ãŸã‚ã«ã¯ã€ãã‚Œã‚‰ã®ã‚µãƒ¼ãƒãƒ¼ï¼ˆã¾ãŸã¯ãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚¯ï¼‰ã‹ã‚‰ã®æŽ¥ç¶šè¨±å¯ã®è¨å®šã‚’ã™ã‚‹å¿…è¦ãŒã‚ã‚Šã¾ã™ã€‚ ã¾ãšã¯ã€ãã®ã‚µãƒ¼ãƒãƒ¼ã¨ã®é€šä¿¡ã®éš›ã«ä½¿ç”¨ã•ã‚Œã‚‹ãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚¯ã‚¤ãƒ³ã‚¿ãƒ¼ãƒ•ã‚§ãƒ¼ã‚¹ã‚’æŒ‡å®šã—ã¾ã™ã€‚ æŒ‡å®šã®æ–¹æ³•ã¯ã€è¨å®šãƒ•ã‚¡ã‚¤ãƒ«ã®postgresql.confã‚’ç·¨é›†ã—ã¾ã™ã€‚ #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '192.168.0.100' ä¸Šè¨˜ã®å ´åˆã¯ã€ãã®ã‚µãƒ¼ãƒãƒ¼ã®ãƒãƒƒãƒˆãƒ¯ãƒ¼ã‚¯ã‚¤ãƒ³ã‚¿ãƒ¼ãƒ•ã‚§ãƒ¼ã‚¹ã®ã†ã¡ã€ã€Œ192.168.0.100ã€ã®IPãŒè¨å®šã•ã‚Œã¦ã„ã‚‹ã»ã†ã®ã‚¤ãƒ³ã‚¿ãƒ¼ãƒ•ã‚§ãƒ¼ã‚¹ã§ã®ãƒªãƒƒã‚¹ãƒ³ã‚’è¨±å¯ã—ã¦ã„ã¾ã™ã€‚ ã¤ã¾ã‚Š
spelunker1972 2014/04/03
å¤–éƒ¨ã‹ã‚‰ã®ã‚¢ã‚¯ã‚»ã‚¹ã«ã¤ã„ã¦

PostgreSQL
ãƒªãƒ³ã‚¯
PostgreSQLã§selectçµæžœã‚’ãƒ•ã‚¡ã‚¤ãƒ«ã«å‡ºåŠ›ã™ã‚‹ : ã¾ãã€ãƒ¢ã‚«ã§ã‚‚ç‰‡æ‰‹ã«ã€‚
ã‚³ãƒžãƒ³ãƒ‰ãƒ©ã‚¤ãƒ³ã‹ã‚‰å‡ºåŠ›ã€€ï¼ˆ-A æ¡ãã‚ãˆã‚’ã—ãªã„ã€åˆ—åŒºåˆ‡ã‚Šã¯ã‚¿ãƒ–ï¼‰ $ psql db_name -c 'SELECT id FROM users;' -A -F \t psqlã‹ã‚‰å‡ºåŠ› å…¨ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’å‡ºåŠ› (åˆ—åŒºåˆ‡ã‚Šã¯ã‚¿ãƒ–ï¼‰ psql# COPY table_name TO '/home/tmp/data.txt' DELIMITERS '\t'; â€»COPYã‚³ãƒžãƒ³ãƒ‰ã¯importã«ã‚‚ä½¿ãˆã‚‹ã‚ˆã†ã€‚ COPY table_name FROM ''/home/tmp/data.csv' DELIMITER ',' selectæ–‡çµæžœã‚’å‡ºåŠ› psql# \o /home/tmp/data.txtã€€ã€€ã€€ã€€â€¦å‡ºåŠ›å…ˆã‚’ãƒ•ã‚¡ã‚¤ãƒ«ã« psql# \aã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€ã€€â€¦æ¡ãã‚ãˆã‚’ã—ãªã„ Output format is unaligned. psql# \pset fieldsep
spelunker1972 2014/04/03
select çµæžœã‚’ãƒ•ã‚¡ã‚¤ãƒ«å‡ºåŠ›(ã‚ˆãå¿˜ã‚Œã‚‹)

PostgreSQL
ãƒªãƒ³ã‚¯
PostgreSQL/ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆå´ã®ã¿ã®ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«æ–¹æ³• - èª¿ã¹ã‚‹.DB
CentOS 6ã®å ´åˆ â€ CentOS6ã§yumã‚³ãƒžãƒ³ãƒ‰ã‚’ä½¿ã£ã¦èª¿ã¹ãŸçµæžœã§ã™ã€‚ ä½¿ç”¨ã—ãŸCentOS6ã¯64bitç‰ˆã§ã™ã€‚ $ yum search postgresql Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: ftp.iij.ad.jp * extras: ftp.iij.ad.jp * updates: centos.ustc.edu.cn =========================== N/S Matched: postgresql ============================ freeradius-postgresql.x86_64 : Postgresql support for freeradius postgresql.i686 :
spelunker1972 2013/12/15
ã“ã‚Œã¯è¦ãƒã‚§ãƒƒã‚¯

PostgreSQL
ãƒªãƒ³ã‚¯
Windowsã§PostgreSQLã‚’ä½¿ã£ã¦ã¿ã‚ˆã† â€” Let's Postgres
NTT ã‚ªãƒ¼ãƒ—ãƒ³ã‚½ãƒ¼ã‚¹ã‚½ãƒ•ãƒˆã‚¦ã‚§ã‚¢ã‚»ãƒ³ã‚¿ æ¿åž£ è²´è£• Windows Vista ã¸ PostgreSQL ã‚’ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã—ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’åˆ©ç”¨ã™ã‚‹ãŸã‚ã®ç’°å¢ƒã‚’æ•´ãˆã¾ã™ã€‚ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã«ã¯ EnterpriseDB ç¤¾ã®ãƒã‚¤ãƒŠãƒªãƒ»ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã‚’ä½¿ã„ã¾ã™ã€‚è§£èª¬ã®æœ€å¾Œã§ã€åˆæœŸè¨å®šã‚„ãƒ‡ãƒ¼ã‚¿ã®ç§»å‹•ã«é–¢ã™ã‚‹ã€ã‚ˆãã‚ã‚‹è³ªå•ã«ã¤ã„ã¦ã‚‚è£œè¶³ã—ã¾ã™ã€‚ ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã®æµã‚Œ æœ€åˆã«ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ãŒä½•ã‚’è¡Œã†ã®ã‹ã¨ã€ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã•ã‚Œã‚‹ãƒ•ã‚¡ã‚¤ãƒ«ã‚’æ•´ç†ã—ã¦ãŠãã¾ã™ã€‚PostgreSQL ã‚µãƒ¼ãƒæœ¬ä½“ã«é–¢ã—ã¦ã¯ã€ã‚¦ã‚£ã‚¶ãƒ¼ãƒ‰ã«å¾“ãˆã°ä¸€æ‹¬ã§ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã•ã‚Œã¾ã™ã€‚ ãƒ€ã‚¦ãƒ³ãƒãƒ¼ãƒ‰ ãƒãƒ¼ã‚¸ãƒ§ãƒ³ 8.4 ã§ã¯ã€Windows ç”¨ã®ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ã¯ EnterpriseDB ç¤¾ã«ã‚ˆã‚Šãƒ¡ãƒ³ãƒ†ãƒŠãƒ³ã‚¹ã•ã‚Œã¦ã„ã¾ã™ã€‚ã‚³ãƒŸãƒ¥ãƒ‹ãƒ†ã‚£ã®ãƒ€ã‚¦ãƒ³ãƒãƒ¼ãƒ‰ãƒšãƒ¼ã‚¸ã‹ã‚‰è¾¿ã‚Œã‚‹ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ©ã‚’åˆ©ç”¨ã™ã‚‹ã®ãŒæ‰‹è»½ã§ã™ã€‚postgresql-8.4.0-1-windows.exe
spelunker1972 2013/07/26
PostgreSQL8.4 ã®ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«

PostgreSQL

windows

ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«
ãƒªãƒ³ã‚¯
1