今だけ人気マンガ100円レンタル特集♪

PostgreSQLの配列に対して、要素の追加及び削除を行いたいのですが、下記のような動作をさせるには、※1と※2で、どのようなSQLを実行すれば良いでしょうか?

よろしくお願いいたします。

>>>
CREATE TABLE test (
id integer,
data integer[]
);

INSERT INTO test VALUES (1, '{0,1,2,3,4}');
INSERT INTO test VALUES (2, '{5,6,7,8,9}');

SELECT * FROM test;
id | data
----+-------------
1 | {0,1,2,3,4}
2 | {5,6,7,8,9}
(2 rows)

UPDATE test [data配列に10を追加] WHERE id = 1; /* ※1 */
UPDATE test [data配列から6を除去] WHERE id = 2; /* ※2 */

SELECT * FROM test;
id | data
----+-------------
1 | {0,1,2,3,4,10}
2 | {5,7,8,9}
(2 rows)
<<<

このQ&Aに関連する最新のQ&A

A 回答 (2件)

PostgreSQL での配列は使ったことが無いのでちょっと調べてみました。



配列要素の追加は下記のように出来るようです。

UPDATE test SET data = array_append(data, 10);
UPDATE test SET data = data || ARRAY[10]

しかし, マニュアルに配列要素の削除についての項目が見当たりませんでした・・・。
結構検索してみたのですがどうにも見つけられず, 配列関数としてもサポートされていないようですし, もしかしたら無いのかも? ^^;

# data - ARRAY[6] とかやってもだめでした。

参考URL:http://sirius.itfrontier.co.jp/Powergres/product …
    • good
    • 0
この回答へのお礼

array_appendという関数があったのですね。気づきませんでした。ありがとうございます^^
除去の方は、やはり関数も無いようなので、CREATE FUNCTION に初挑戦してみました。

>>>
CREATE FUNCTION array_strip(integer[], integer) RETURNS integer[] AS '
 DECLARE
  src ALIAS FOR $1;
  val ALIAS FOR $2;
  dst integer[] := ''{}'';
  i integer;
 BEGIN
  FOR i IN array_lower(src,1) .. array_upper(src,1) LOOP
   IF src[i] != val THEN
    dst := array_append(dst, src[i]);
   END IF;
  END LOOP;
  RETURN dst;
 END;
' LANGUAGE 'plpgsql';

UPDATE test SET data = array_append(data, 10) WHERE id = 1;
UPDATE test SET data = array_strip(data, 6) WHERE id = 2;
<<<

以上のようになりましたが、もっと効率の良い方法、ありますかね…。

お礼日時:2006/09/21 23:36

配列の要素を操作する関数が限られている以上, 配列の要素を全部サーチする以外に方法はないかと思います。


配列要素がそれなりに大きくなると負荷もあがりそうですし, 関数やプロシージャはデバッグが大変です ^^;

DB側ではなくフロント側で配列を操作するか, 配列データを正規化して別テーブルに分けるかにしたほうがいいかもしれません。
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QSELECT INTOで一度に複数の変数へ代入をするにはどのようにすれがよいでしょうか?

初めてこちらで質問させていただきます。
どうぞよろしくお願いします。
早速ですが、
現在ストアドファンクション(PL/pgSQL)にて以下のような形になっています。
変数d1,d2,d3に値をセットするにあたり
テーブルt1を3回スキャンしています。
これを1回のスキャンでセットするような
方法はないのでしょうか?

DECLARE
d1 integer;
d2 integer;
d3 varchar(10);

begin
select min(c1) into d1 from t1;
select min(c2) into d2 from t1;
select min(c3) into d3 from t1;
各種処理
return v;
end;

気持ち的には以下のようなSQLを書きたいのですが、
syntax error となります。
(NG例)
select min(c1) into d1,min(c2) into d2,min(c3) into d3 from t1;

何か良い案がありましたら教えてください。
「それは出来ません」と言う回答でもありがたいです。
※あきらめがつくので。。

それではよろしくお願いします。

初めてこちらで質問させていただきます。
どうぞよろしくお願いします。
早速ですが、
現在ストアドファンクション(PL/pgSQL)にて以下のような形になっています。
変数d1,d2,d3に値をセットするにあたり
テーブルt1を3回スキャンしています。
これを1回のスキャンでセットするような
方法はないのでしょうか?

DECLARE
d1 integer;
d2 integer;
d3 varchar(10);

begin
select min(c1) into d1 from t1;
select min(c2) into d2 from t1;
select min(c3) into ...続きを読む

Aベストアンサー

select min(c1),min(c2),min(c3) into d1,d2,d3 from t1;
でダメ?

Q配列に指定した値が含まれているものを抽出する方法

こんにちは。
いつもお世話になっております。

SQLにて、配列のフィールドに指定した値が含まれているものを抽出する方法を質問致します。


SQLのテーブル(テーブル名:data)に配列のフィールド(フィールド名:array[])があるとします。
Array[]フィールドの中に、1, 2, 6のいずれかが含まれている場合表示対象となるSQL文を書くには、どのように書けばよいですか。PostgreでSQL文を書くとします。


実現したい動作は以下の通りです。
array[] の値
{1,3,5} 表示対象になる
{3,8,9,10}  表示対象にならない
{2,6} 表示対象になる

以上よろしくお願い致します。


ちなみに、array[]フィールドの中に、2(指定した数値が1つだけ)を探すとき、
select * from data where 2=any (array);
とすればいいことは確認できました。

Aベストアンサー

&& 演算子 (重複する, 共通要素を持つ) を使います。

SELECT * FROM (VALUES
(ARRAY[1,3,5]),
(ARRAY[3,8,9,10]),
(ARRAY[2,6])
) T(arr)
WHERE ARRAY[1,2,6] && arr;

参考URL:http://www.postgresql.jp/document/current/html/functions-array.html

Qcastの使用法について(初心者です)

始めまして。初心者ですので基本的な質問になるかもしれませんがよろしくお願いします!

なぜかPostgreSQLを仕事に使うことになり、四苦八苦しています。

テーブルを作成しているときに、
create table syouhin (shinamono text, nedan int);
とするとします。
で、insert でデータを入れていきますが、
例:
shinamono | nedan
------------------
みかん |100
マンゴー |200

例えば、nedan のcast が今、int にしましたが、これをchar やfloatに変えたいときはどうすればよろしいのでしょうか?

どなたか御存じの方、お手数ですがお教え下さい!!!

Aベストアンサー

CAST関数の文法は、CAST(○ AS △) となります。
○:変換する値
△:変換する型

Select CAST(nedan AS char(10)) ~
とすればできると思いますよ

QpgAdminIII RAISEの結果取得について

お世話になります。

SQLからSQLを呼び出す形で以下のことを行おうとしています。

呼び出し元のSQLにRAISE文を記述し、フィールドの内容を表示させようとしているのですが、
SELECTで実行すると、「PERFROMに変更してください」とのエラーになってしまいます。

そこで、PERFORMに変更し実行しますと、RAISE文の結果が表示されません。
また、PERFORMのままで表示させるには、呼び出し先のSQLにRAISE文を記述すれば
良いようなのですが、呼び出し先のSQLに手を加えたくありません。
(呼び出し先のSQLをテストしているので・・・。)

どなたか、うまく表示させる方法をご存知の方がいらっしゃいましたら、是非とも
ご教授下さい。

よろしくお願い致します。

Aベストアンサー

PL/pgSQL から、出力パラメータ (OUT 引数) のある関数を呼び出す場合、
引数ではなく返値として値を受け取る必要があります。
PERFORM ではなく、SELECT INTO を使ってみてください。

CREATE OR REPLACE FUNCTION fn_raise(OUT o_v1 int, OUT o_v2 int) AS $$
BEGIN
o_v1 := 123;
o_v2 := 456;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
v1 int;
v2 int;
BEGIN
SELECT * INTO v1, v2 FROM fn_raise();
RAISE WARNING 'v1=%, v2=%', v1, v2;
END;
$$;

WARNING: v1=123, v2=456

Qsqlに記述できない文字

いつもお世話になっております。
WEB画面から入力された項目を対象にPostgresに検索しに行っているのですが、シングルクォート(')が入っていた場合、エラーになってしまいました。

JavaからSQLを生成し、Postgresに接続しているのですが、シングルクォートを検索させるにはどうするのが良いでしょうか。

またSQLに記述できない文字は、シングルクォートのほかに何かありますでしょうか。

よろしくお願いします。

Aベストアンサー

下記のページにPostgreSQLのSQLの文字列定数についての説明が有りますので、記述できない文字などについてはここを見ると良いでしょう。
http://www.postgresql.jp/document/pg837doc/html/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

さて、シングルクウォートを含む文字列定数への対応としては下記の3つが考えられます。
1. 文字列中のシングルクウォートとバックスラッシュ(\)を\でエスケープする。
2. ドル引用符を使用する。
3. プレースフォルダを使用する。


2は、$xxxx$ のようなドル記号で囲まれた文字列を引用符代わりに使う、PostgreSQL 独自の機能です。
'hoge' と書く代わりに $xxxx$hoge$xxxx$ の様に書けます。
詳しくは下記のページを見て下さい。
http://www.postgresql.jp/document/pg837doc/html/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

3は、SQLの定数の位置に?を書き、PreparedStatement の set~~() メソッドを使って値を指定する方法です。下記のページが参考になるでしょう。
http://www.atmarkit.co.jp/fjava/rensai2/webopt11/webopt11.html
http://sdc.sun.co.jp/java/docs/j2se/1.4/ja/docs/ja/guide/jdbc/getstart/preparedstatement.html

参考URL:http://www.postgresql.jp/document/pg837doc/html/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

下記のページにPostgreSQLのSQLの文字列定数についての説明が有りますので、記述できない文字などについてはここを見ると良いでしょう。
http://www.postgresql.jp/document/pg837doc/html/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

さて、シングルクウォートを含む文字列定数への対応としては下記の3つが考えられます。
1. 文字列中のシングルクウォートとバックスラッシュ(\)を\でエスケープする。
2. ドル引用符を使用する。
3. プレースフォルダを使用する。


2は、$xxxx$ のようなドル記号で囲...続きを読む

Qpostgresqlでのトランザクションについて

postgresqlで、「WARNING  進行中のトランザクションがありません」のエラーが出てしまいました。

これはそのままの意味だと思うのですが
直す方法がわかりません。
ワーニングなので、、、といいたいところですが、そうもいかず。

apのログで、トランザクション関係の辺りを見ましたところ、
トランザクションを開始し、
TABLEをDROPしようとしましたが、存在しなかった(ようだ)。
(PQexecがエラーでリターン)
存在しないけれど、commit してトランザクションを終わらせている。

のが原因かなと思いました。

なので、PQexec()がエラーの場合はcommitしなければいいのかと
はずしましたが結果は同じでした。

トランザクションが無いのだからrollbackもいらないですよね。


そこで質問ですが
drop文、1ステップのSQL実行も明示的にトランザクションの処理をしたほうがいいのでしょうか?

このワーニングは通常のトランザクションの処理の中で、どういう時にでるのでしょうか?


oracleとpostgresqlの明示的・暗黙的な制御に戸惑っています。
(oracleからpostgresqlに移行することになりました)


叱咤でも何でもかまいませんので、アドバイスよろしくお願いいたします。

postgresqlで、「WARNING  進行中のトランザクションがありません」のエラーが出てしまいました。

これはそのままの意味だと思うのですが
直す方法がわかりません。
ワーニングなので、、、といいたいところですが、そうもいかず。

apのログで、トランザクション関係の辺りを見ましたところ、
トランザクションを開始し、
TABLEをDROPしようとしましたが、存在しなかった(ようだ)。
(PQexecがエラーでリターン)
存在しないけれど、commit してトランザクションを終わらせている。

のが原因かなと思いま...続きを読む

Aベストアンサー

PostgreSQLではOracleと異なりDDLで自動コミットされないので、基本的にはBEGINとCOMMIT(またはROLLBACK)で囲むのが基本です。

今回のWARNINGは、すでにトランザクションが終了しているのにCOMMIT/ROLLBACKを発行したために発生しています。アプリケーションのコードパスでBEGINとCOMMIT/ROLLBACKが1:1対応になっていない箇所(ROLLBACKしているのにCOMMITもしている、など)があるのではないでしょうか?

また、今回のケースではテーブルが存在しないことが通常ケースとしてあり得るということのようなので、DROP TABLE IF EXISTS <table name>;というDDLを使うのが良いと思います。

ちなみに、PostgreSQLではトランザクション開始後にエラーが発生してもトランザクションは終了せずabort状態に遷移します。この状態では、COMMIT/ROLLBACK以外の全てのSQLがエラーになります。COMMIT/ROLLBACKを実行すると、どちらでもトランザクションはROLLBACKされて終了します。
https://www.postgresql.jp/document/9.3/html/tutorial-transactions.html

参考URL:https://www.postgresql.jp/document/9.3/html/sql-droptable.html

PostgreSQLではOracleと異なりDDLで自動コミットされないので、基本的にはBEGINとCOMMIT(またはROLLBACK)で囲むのが基本です。

今回のWARNINGは、すでにトランザクションが終了しているのにCOMMIT/ROLLBACKを発行したために発生しています。アプリケーションのコードパスでBEGINとCOMMIT/ROLLBACKが1:1対応になっていない箇所(ROLLBACKしているのにCOMMITもしている、など)があるのではないでしょうか?

また、今回のケースではテーブルが存在しないことが通常ケースとしてあり得るということのようなので、DROP...続きを読む

QFETCH した行が取り出せない

こんにちは
pgAdmin3を使用しています。

 条件に一致する行を複数取得して、その行に対してアップデートをかけたいです。アップデートの処理がややこしいので一文で書かず、カーソルを使用して一行づつ処理をしていきたいのですが、下記で カーソル名.列名で値の取得が出来ないようです。
missing FROM-clause entry in subquery for table "car"

BEGIN;
  DECLARE car CURSOR IS ~抽出条件
FETCH ALL IN car;
  UPDATE ・・・ WHERE clm_name = car.clm_name
CLOSE car;
END;

 postgresqlが不慣れで、ネットで調べては色々変えてやってみているのですが、ここで詰まってしまいました。
 カーソル内のカラムの取得方法についてご教示下さる方が居れば助かります。

Aベストアンサー

#2回答者です。

「FETCH ALL IN カーソル名」の使い方がよく分からなかったので、代替案としてPL/pgSQLを使用する方法を考えてみました。

(1)関数の定義
recという行データ受け取り変数を使い、受け取った特定列の参照は、「変数名.列名」で行えます。

create function functest1() returns int as
'
declare
car cursor for
select * from t1;
rec record;
begin
open car;
loop
fetch car into rec;
if not found then
exit;
end if;
update t2
set c2=0
where c3=(select c3 from t3 where c1=rec.c1);
end loop;
close car;
return 0;
end;
'
language 'plpgsql'
;

(2)関数の実行
select functest1();

(3)関数を削除する場合
drop function functest1();

#2回答者です。

「FETCH ALL IN カーソル名」の使い方がよく分からなかったので、代替案としてPL/pgSQLを使用する方法を考えてみました。

(1)関数の定義
recという行データ受け取り変数を使い、受け取った特定列の参照は、「変数名.列名」で行えます。

create function functest1() returns int as
'
declare
car cursor for
select * from t1;
rec record;
begin
open car;
loop
fetch car into rec;
if not found then
exit;
end if;
update t2
...続きを読む

Q複数行の結果を単一列に連結

以下のような【TABLE1】と【TABLE2】をID_SUBの列で結合した結果を
【A.結果】のように取得したいのですがうまくいきません。

【TABLE1】
ID ID_SUB
1 A1
2 B1

【TABLE2】
ID ID_SUB ID_SUB_CODE
1 A1 AAA1
2 A1 AAA2
3 B1 BBB1
4 B1 BBB2

【A.結果】
ID_SUB ID_SUB_CODE
A1 "AAA1,AAA2"
B1 "BBB1,BBB2"

いろいろ調べたのですが、MySQLにはGROUP_CONCATの
関数を使うと簡単に出来るみたいですが、PostgreSQLには
似たような関数がなく、ARRAYが使えるかな?と思ってためしてみましたが

array_to_string(ARRAY(ID_SUB_CODE, ','))

等のような感じで試した結果、ID_SUB_CODEが
全て連結されるような使い方しか出来ませんでした。

A1 "AAA1,AAA2,BBB1,BBB2"
B1 "AAA1,AAA2,BBB1,BBB2"

何か良い方法はありますでしょうか?

以下のような【TABLE1】と【TABLE2】をID_SUBの列で結合した結果を
【A.結果】のように取得したいのですがうまくいきません。

【TABLE1】
ID ID_SUB
1 A1
2 B1

【TABLE2】
ID ID_SUB ID_SUB_CODE
1 A1 AAA1
2 A1 AAA2
3 B1 BBB1
4 B1 BBB2

【A.結果】
ID_SUB ID_SUB_CODE
A1 "AAA1,AAA2"
B1 "BBB1,BBB2"

いろいろ調べたのですが、MySQLにはGROUP_CONCATの
関数を使うと簡単に出来るみたいですが、PostgreSQLには
似たような関数がなく、ARRAYが使え...続きを読む

Aベストアンサー

select id_sub,
( select array_to_string(ARRAY(select id_sub_code from table2
where table1.id_sub=table2.id_sub ), ',') )
from table1;

なんて参考になりませんか。

QPostgreSQLで外部DB内のテーブル参照方法を教えてください

PostgreSQLで別データベースのテーブルを参照することはできますか?

仮に以下のデータベースが2つあるとします
CREATE DATABASE common_db ...
CREATE DATABASE app1_db ...

app1_db に接続した状態で common_db のテーブルを参照することはできますか?

仮にcommon_dbに郵便番号テーブル postcodeがあるとします
CREATE TABLE postcode (
postcode text, --郵便番号
pref_name text, -- 県名
 ・・・
)

このpostcodeテーブルをapp1_dbに接続した状態で参照するには、どのような参照クエリになりますか?
ご教授の程よろしくお願います。
そもそもできないのでしょうか?

Aベストアンサー

PostgreSQL 本体の機能では実現できませんが、contrib モジュールの dblink を使えばできます。

dblink のインストール方法は PostgreSQL のインストール方法によって異なりますが、RPM パッケージであれば postgresql-contrib というパッケージをインストールし、以下のように dblink の関数などを定義します。

psql -f /usr/share/postgresql/contrib/dblink.sql (データベース名)

データベース名には他のデータベースにアクセスしたいデータベース名、例では app1_db を指定します。なお、dblink.sql のパスはパッケージによって異なる場合があります。

dblink をインストールすれば以下のように関数を呼び出して別のデータベース内のテーブルを参照できます。

SELECT * FROM dblink('dbname=common_db', 'SELECT * FROM postcode') AS postcode(postcode text, pref_name text);

dblink について詳しくは PostgreSQL のマニュアルを読んでください。

http://www.postgresql.jp/document/pg840doc/html/dblink.html

ただ、質問の例にあるデータベース名やテーブル名を見ていると、アプリケーションごとに名前空間を分けたいだけのようなので、それであればスキーマを使ったほうがよさそうな気もします。

参考URL:http://www.postgresql.jp/document/pg840doc/html/dblink.html

PostgreSQL 本体の機能では実現できませんが、contrib モジュールの dblink を使えばできます。

dblink のインストール方法は PostgreSQL のインストール方法によって異なりますが、RPM パッケージであれば postgresql-contrib というパッケージをインストールし、以下のように dblink の関数などを定義します。

psql -f /usr/share/postgresql/contrib/dblink.sql (データベース名)

データベース名には他のデータベースにアクセスしたいデータベース名、例では app1_db を指定します。なお、dblink.sq...続きを読む

Q複数の条件での絞り込み検索の仕方

PostgreSQLで複数の条件での絞り込み検索をしたいのです。

テーブルには
発売日、商品名、入荷日
があります。
例えば
発売日が2007年5月1日から5月5日で、商品名に「○○」もしくは「××」もしくは「△△」が含まれており、入荷日が一番新しいもの
という条件で検索したいのです。

発売日だけの絞込みならBetweenですし、商品名だけならor、入荷日の最新だったらmaxを使えば個別には検索できるのですが、これをまとめて一行でやるにはどうしたらよいのかわかりません。
それぞれでandでつなげてみましたが、orの条件がうまく反映されず、一個目の○○だけが検索に引っかかってる状態です。
××や△△もひっかかるようにするにはどう記述したらよいのでしょうか。

Aベストアンサー

データ件数がどのくらいあるのかは分かりませんが、ユニークなキーもなく、likeの任意一致をorでつなぐという方法は、性能を出せませんよ?

select *
from t1
where 入荷日 in(
select max(入荷日)
from t1
where 発売日 between '2007-05-01' and '2007-05-05'
and (商品名 like '%○○%' or 商品名 like '%××%' or 商品名 like '%△△%')
)
and 発売日 between '2007-05-01' and '2007-05-05'
and (商品名 like '%○○%' or 商品名 like '%××%' or 商品名 like '%△△%')


人気Q&Aランキング