SQLのSELECT SUM(CASE WHEN ~ ELSE NULL END) でNULLを文字にするやり方について

投稿者: Anonymous タイトルの通りなのですが、SUMで集計して結果がNULLの場合に「無」のように文字で結果を表示するようにしたいものの、処理がうまくできません。 今は SELECT SUM(CASE WHEN A THEN 1 ELSE NULL END) としているため、ヒットしないところはNULLとなり空欄になっています。 ↑のNULLのところを文字で置換してみたり、COALESCEやISNULLを使ってもみたのですが、どうにもうまくできませんでした。 どなたかご教授頂けると幸いです。 よろしくお願いします。 解決 bool型の列に対して、trueとなっている行をカウントしたい、という話でしょうか。 SUMの結果をCOALESCEに通せばいいとは思いますが、COALESCEに渡す値の型を揃えないといけません。というか揃えられてしまいます。例えば次のSQLではSUMの結果は int なので、’無’ も int に変換しようとします…が、できないのでエラーになります。 SELECT COALESCE(SUM(CASE WHEN A THEN 1 ELSE NULL END), ‘無’) — ERROR: invalid input syntax for integer: “無” ですから、SUMの方を明示的に文字列に変換しなければなりません。SQLで型変換を行う際には CAST( value AS type ) とします。 SELECT COALESCE(CAST(SUM(CASE WHEN A THEN…(Continue Reading)

Función recursiva en PostgreSQL

publicado por: Anonymous Tengo esta tabla: CREATE TABLE players ( winner CHARACTER VARYING(50) NOT NULL , successor CHARACTER VARYING(50) NOT NULL , data NUMERIC(6,2) NOT NULL , CONSTRAINT pk_win_succ PRIMARY KEY (winner, successor) ); Con estos datos: INSERT INTO players VALUES (‘Helen’,’Sharon’,12), (‘Helen’,’Martina’,34), (‘Martina’,’Sharon’,2), (‘Claudia’,’Steffi’,35), (‘Sharon’,’Penny’,5), (‘Meg’,’Claudia’,21), (‘Penny’,’Meg’,3), (‘Steffi’,’Helen’,230); Lo que necesito es una función…(Continue Reading)

Crear un backup de Postgres vía consola en Debian

publicado por: Anonymous Necesito sacar un backup de una base de datos mía vía consola / terminal (por motivos académicos) pero me saca estos errores, yo tengo Debian Jessie y psql (PostgreSQL) 9.5.1. solución Para ejecutar pg_dump lo debes hacer fuera de la consola psql, es decir, ejecútalo directamente en tu terminal ya que es…(Continue Reading)

phpの配列を参照してSQLでテーブルを結合したい

投稿者: Anonymous phpの配列 array(‘a’=>’1’,’b’=>2) という配列があり テーブルA id | val a | ‘aaa’ b | ‘bbb’ テーブルB num | val 1 | ‘111’ 2 | ‘222’ の2つのテーブルをsqlで下記のように結び付けたいです。 select 結果 id | val | num | val a | ‘aaa’| 1 | ‘111’ b | ‘bbb’| 2 | ‘222’ 何かいい方法はあるでしょうか。 解決 下記のようにすれば可能です。 性能的に結合条件にINDEX指定することをお勧めします。 ※バージョンによってはできないかも知れません。 SELECT * FROM…(Continue Reading)

¿Cómo reiniciar los valores autoincrementables de una columna en una tabla?

publicado por: Anonymous Tengo una tabla en postgresql con las siguientes columnas Columna | Tipo | ————-+————————+ id_empleado | integer | nombre | character varying(255) | apellido | character varying(255) | id_jefatura | integer | La columna id_empleado es autoincrementable y tiene valores que saltan entre si, por ejemplo del id 18 se salta al…(Continue Reading)

POSTGRES~DBLINKでシーケンスを取得したい

投稿者: Anonymous postgresを使用しています。 現在のシーケンス取得SQLは下記の状態になっています。 select nextval(‘seq_no’) そして、’seq_no'(シーケンス)をDBLINKで取得するようにしたいのですが、方法がわからず調査中です。 ・調査一覧 -select seq1.nextval from dual;→オラクルのみ 解決方法の分かる方がいましたらご教授をお願いします。 また、出来ればnextval内を修正するだけにとどめたいのですが、不可能でしたらSQL文自体を改変する回答でも大丈夫です。 解決 改変する場合 SELECT * FROM DBLINK( ‘dbname=db host=123 user=postgres password=postgres’ , ‘select nextval(”seq_no”)’ ) AS T( seq_no int ); これでnextvalした後のシーケンス値を取得することが出来る。 回答者: Anonymous

¿Cómo puedo obtener un número aleatorio en un rango?

publicado por: Anonymous ¿Cómo puedo hacer para obtener un número aleatorio entre dos valores? Entre un mínimo y un máximo, por ejemplo, un número entre 1 y 10 Intente haciendo un SELECT y usando RAND(): SELECT RAND() as aleatorio Pero no se como indicar el rango, porque lo siguiente no funciona: SELECT RAND(1, 10) as…(Continue Reading)

Cómo guardar caracteres en español e inglés en Postgres?

publicado por: Anonymous Tengo problemas para guardar caracteres en español en Postgres. Por ejemplo á, é, í,ó,ú, and ä, ü, ñ, Ñ. Alguna idea como solucionar esto? Ademas me gustaria no tener porblema con caracteres tipico de inglés (que creo no tiene caracteres especiales). Es decir que no haya problemas con español e ingles. Actualización:…(Continue Reading)

¿Cómo extraer el mes de una columna de tipo date en PostgreSQL?

publicado por: Anonymous Tengo la tabla anuncio_detalle de la siguiente forma. Columna | Tipo | Modificadores —————+————————+————— id_anuncio | character varying(50) | latitud | numeric | longitud | numeric | fecha_entrega | date | hora_entrega | time without time zone | estado | boolean | La columna fecha_entrega es de tipo date por lo que…(Continue Reading)

¿Como hacer INNER JOIN entre una tabla en SQL SERVER y un servidor vinculado?

publicado por: Anonymous Bueno me explico, tengo una base de datos en SQL SERVER llamada SIELAB y otra base de datos en PostgreSQL llamada reservas, logré realizar la conexión correctamente y ya logré ejecutar una consulta SELECT desde un stored procedure en SQL SERVER a una tabla que tengo en PostgreSQL. La consulta la realicé…(Continue Reading)

配列を引数としたPostgresql配列データ検索方法について

投稿者: Anonymous Ruby on Rails にてWebアプリを作成しております。初学者のため回答に必要な前提条件等が抜けておればご指摘頂けますと幸いです。 ユーザーが指定する条件に基づいてデータベースのテーブルを検索するクエリを作成しているのですが、以下の条件で最も簡潔な書き方をご教示いただけないでしょうか?? ①Postgresqlデータベースの中に配列(ex. [“a”, “b”, “c”])が格納されている列があり、この列データと②の検索条件を照合してデータを検索する ②ユーザーが指定した条件(チェックボックスを使用)は配列で取得している状況 ex. [“a”, “c”] ①②の配列に格納されるデータ数は必ずしも一致しないが、共に7つという上限あり。 クエリ抽出条件としては、②の配列に含まれる文字のどれか1つでも①の配列に含まれていれば、データを抽出する。 現状、モデル内に以下のようなscopeを記述し、データの抽出を行おうとしています。 scope :search_by_preference, ->(num) { where(“? = ANY(preference_num)”, num)} しかしながら、こちらのプレイスホルダーでは、配列を引数として渡すことができません。 なお、引数が1つ(ユーザーの選択した条件が1つ)のときは問題なくクエリは機能しております。 以上、何卒よろしくお願いします 解決 上述のコメントにもありますが、postgresqlのデータ型がcharacter varying[]であるため、引数を明示的に可変長文字列に指定することで解決しました。 where(“preference_num && ARRAY[?]::varchar[]”, num) 回答者: Anonymous

Buscar cadena dentro de un campo de texto, PostgreSQL

publicado por: Anonymous Tengo el siguiente problema: necesito hallar una subcadena exacta que comienza y termina con números dentro de un campo de texto. La query de momento es: SELECT * (…) FROM dominio AS Dom, ampliatoria AS Amp, (…) WHERE (…) AND Amp.descripcion ILIKE ‘%’ || Dom.dom_descripcion || ‘%’; El campo Dom.dom_descripcion posee cadenas…(Continue Reading)

「オープンデータ」のような半濁点を含むカタカナがうまく検索できない

投稿者: Anonymous PostgreSQL 9.2.4 + Rails 4.1を使っています。 DB内のあるカラムに「空間オープンデータを用いたソフトウェア」という文言が入っています。 このとき、以下のようなSQLを発行してもデータが返ってきません(0件)。(実際には画面からフリー入力された文字列をActiveRecordのクエリに渡しています) select * from groups g where g.overview like ‘%オープンデータ%’ しかし、「タ」だけ検索すると返ってきます。 select * from groups g where g.overview like ‘%タ%’ 「ソフトウェア」や「空間」でも返ってきます。 select * from groups g where g.overview like ‘%ソフトウェア%’ select * from groups g where g.overview like ‘%空間%’ 「テ」でも大丈夫です。 select * from groups g where g.overview…(Continue Reading)

PostgreSQLでdumpファイルのインポートができない。

投稿者: Anonymous Windows上でPostgreSQLにdumpファイルのインポートをしようとしたら、インポートできませんでした。 コマンドラインは次の通りです。 database=# lo_import c:/database.backup lo_import 16401 database=# dt リレーション一覧 スキーマ | 名前 | 型 | 所有者 ———-+———-+———-+———- public | products | テーブル | postgres (1 行) ただし、products データベースは、もともとあった1行のテーブルです。 また、pythonを使って次のようにしてもダメでした。 import psycopg2 connector = psycopg2.connect( host=’localhost’, port= ‘5432’, database=’postgres’, user=’postgres’, password=’*********’, ) cursor = connector.cursor() sql = “pg_restore -d database2 c:/database.backup” cursor.execute(sql) 何がわるいのでしょうか。…(Continue Reading)

Saber si una tabla existe o no en postgres

publicado por: Anonymous lo que pasa es que estoy intentando consultar si una tabla existe en postgres o no,logré hacer la consulta,el problema es que está consultando si existe en todo el esquema de postgres y no en la base sobre la que estoy ejecutando la consulta y me trae más registros ya que tengo…(Continue Reading)

SQLで高速に近傍検索をしたい

投稿者: Anonymous フレームワーク側で緯度経度を配列で持っていて そこから住所情報を取得したいです まず 国土地理院のサイト に国内の市区町村レベルの代表点のデータがあったので CSVをダウンロードしてテーブルを作りました geo という geometry point 型カラムと address 情報の string カラム 2カラムのテーブルを作り geo にはインデックスを貼りました 以下のような定数座標との距離でソートして1個目を取り出すようなクエリを投げたのですが 結果が返ってくるのに数十秒かかってしまいます SELECT address FROM tests ORDER BY st_Distance(ST_GeomFromText(‘POINT(139.7 35.69)’),geo) LIMIT 1; address ————————— 東京都新宿区西新宿一丁目1 (1 row) Time: 58566.859 ms (00:58.567) 次に where 文で距離が 1km 以内のものに絞ってからソートを行うと一瞬で結果が返ってきます SELECT address FROM tests WHERE st_Dwithin(ST_GeomFromText(‘POINT(139.7 35.69)’),geo,1000) ORDER BY…(Continue Reading)

PHP / PostgreSQLのメモリ解放

投稿者: Anonymous PHP / PDOドライバのキャッシュ機能について 環境 PostgreSQL9.4 IDIORM Paris PostgreSQLは検索結果をキャッシュしてしまうようで、メモリリークしてしまいます。 大規模なSelectを行った後は、どうすれば解放してもられるんでしょうか。 できればソース(参考文献)と共に、教えてください。 解決 間違いなくメモリリークしていると判断できる根拠があるのであれば、該当部分(PostgreSQL?IDIORM?Paris?)のデベロッパにその根拠を添えてバグ報告しましょう。 ここでいくら質問してもプロダクトのバグが直ることはまずありません。 回答者: Anonymous

¿Cómo pasar parámetros a un store procedure de PostgreSQL en C#

publicado por: Anonymous Necesito pasar parámetros en C# para PostgreSQL: public static IEnumerable<Articulo> SelectListFunc(UniversalExtend filter) { using (var cn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings[“default”].ToString())) { cn.Open(); using (var cmd = cn.CreateCommand()) { cmd.CommandText = “usp_selectlist”; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(“@idarticulo”, NpgsqlDbType.Varchar, string.IsNullOrEmpty(filter.Id) ? (object)DBNull.Value : $”%{filter.Id}%”); cmd.Parameters.AddWithValue(“@descripcion”, NpgsqlDbType.Varchar, string.IsNullOrEmpty(filter.Descripcion) ? (object)DBNull.Value : $”%{filter.Descripcion}%”); var _articulo = new List<Articulo>();…(Continue Reading)

¿Como puedo hacer un sql que contenga un array de objetos y que esos objetos contengan 2 datos de columnas?

publicado por: Anonymous Llevo este sql El resultado de las 2 columnas modelos_id y modelos_nombre las quisiera unir dentro de objetos, lo intento hacer como dice la documentación. Acá lo que intento hacer, la consulta es la siguiente: SELECT mar.nombre AS marcas, json_object(array_agg(mod.id), array_agg(mod.nombre)) as modelos FROM marcas AS mar INNER JOIN modelos AS mod…(Continue Reading)

Consulta por rango de fechas con muchas consultas anidadas

publicado por: Anonymous Tengo que obtener una lista con los clientes de la empresa a los que hemos facturado algo durante el presente año, indicando cuánto hemos facturado cada mes. Tengo dos tablas: la tabla de clientes (customer) y la tabla de facturas (invoice). La relación es la evidente: un cliente puede tener muchas facturas,…(Continue Reading)

Postgresqlにて指定した範囲の時間を含まないデータを取得したい

投稿者: Anonymous いつもお世話になっております。 早速本題ですが、 スケジュールといったEntityが存在し、その中に睡眠開始時刻と起床時刻(SleepStartTime/SleepEndTime)が保存されています。 このとき、睡眠時間が1:00~4:00の時間帯に重複しないレコードを取得しようとしているのですが、 中々うまく行きません。 (not inを使うのかなとは思いいろいろとチャレンジはしているのですが…) データベースはpostgresqlになります。 以下のイメージになります。(一部抜き出し) ScheduleEntity id : longintger SleepStart :time SleepEnd : time 取得したいテーブル例 id: 1 SleepStart:23:00 SleepEnd:0:30 id: 2 SleepStart:05:00 SleepEnd:12:30 取得したくないテーブル例 id: 3 SleepStart:00:00 SleepEnd:03:30 id: 4 SleepStart:02:00 SleepEnd:08:30 id: 5 SleepStart:00:30 SleepEnd:09:30 お手数ですが、わかる方教えていただければ幸いです。 解決 PostgreSQL には overlaps がありますので、 select * from ScheduleEntity where not (SleepStart,…(Continue Reading)

Sincronizar datos entre dos base de datos con la misma estructura

publicado por: Anonymous Necesito pasar los datos de una base de datos a otra con la misma estructura, el problema es que ambas base de datos están en uso, en ambas se pueden insertar registros diferentes y están en lugares diferentes. Por ej.: en la DB1 ingresamos una factura del cliente1, en la DB2 se…(Continue Reading)

Porque me aparece este error

publicado por: Anonymous ERROR: llave duplicada viola restricción de unicidad «matricula_pkey» DETAIL: Ya existe la llave (cod_est, cod_asig)=(1085, 1080). SQL state: 23505 A la hora de ingresar los datos de una tabla me aparece este error, y mi base de datos esta conformada de la siguiente manera: create table Estudiante( cod_est varchar (11) primary key,…(Continue Reading)

¿Como ejecutar función de postgresql en php con pdo?

publicado por: Anonymous Me gustaria saber como puedo llamar una funcion o procedimiento almacenado en postgresql mediante PHP con PDO. Supongamos que tengo la funcion en postgresql: buscarporcategoria(categoria character varying); Y su contenido es el siguiente: CREATE OR REPLACE FUNCTION public.buscarporcategoria( _categoria_ character varying) RETURNS TABLE(isbn character varying, titulo character varying, edicion integer, paginas integer,…(Continue Reading)

Número de filas afectadas en postgresql

publicado por: Anonymous Cuando realizo un insert, update o delete dentro de una función en postgresql desearía obtener el número de filas afectadas para que en el Lenguaje de Programación verificar si se ejecuto correctamente la query. En MySQL existe @@identity cuando hay auto incrementables y row_count() filas afectadas, quiero lo mismo pero en postgresql.…(Continue Reading)

¿Cómo realizar un Select con PDO en Postgresql / PHP?

publicado por: Anonymous Actualmente estoy probando trabajar con PDO para evitar el SQL Injection pero sinceramente estoy sumamente verde, ya hice mi archivo de conexion a la base de datos por PDO pero no tengo bien claro como puedo hacer una consulta a traves de un select y el resultado mostrarlo en un echo. Aquí…(Continue Reading)

pgAdmin3 によるリストアができない

投稿者: Anonymous お力添えをお願いします。 pgAdmin3 によるリストアができず困っています。 【操作手順】 [1] 端末AでpgAdmin3 のオブジェクトブラウザからサーバのデータベースの一つを右クリック「バックアップ」 [2] [1]で生成された watashinodb.backup ファイルを端末Bのローカルフォルダへ移動 [3] 端末BのpgAdmin3 のオブジェクトブラウザからlocalhost のデータベースの一つを右クリック「リストア」 [4] [2]のwatashinodb.backup ファイルを指定して[リストア]実行 [5] 下記のメッセージを受け取る; C:Program Files (x86)pgAdmin III1.22pg_restore.exe –host localhost –port 5432 –username "XXX" –dbname "YYY" –no-password –verbose "C:UsersZZZDocumentswatashinodb.backup" pg_restore: [archiver] unsupported version (1.13) in file header プロセスは、1 のリターンコードを返しました。 環境/インストール済みパッケージ: ■端末A: PostgreSQL:9.6.18 pgAdmin3:1.2.22 pgAdminのpg_dump.exe:9.6.xx ■端末B: PostgreSQL:9.6.18 pgAdmin3:1.2.22…(Continue Reading)

特定のモデルのインスタンスのディスク容量を調べる方法

投稿者: Anonymous 特定のモデルがどの程度の容量を消費するかを確認するために生成されたインスタンスとそのモデルがhas_manyで持つ関連付けられたものを含めたディスク容量を取得する方法はありますか? 例えば class User < ActiveRecord::Base has_many :posts end とある時にpostに1万件テストデータを入れて実際にどの程度のバイト数を消費するのかを確認したく思っています。 環境はPostgresqlです。 解決 pg_class テーブルの relpages がブロック数(8KiB単位 / show block_size;で確認) で reltuples が行数、ということで、雰囲気を掴むレベルであれば VACUUM; SELECT relpages * 8192 / reltuples FROM pg_class WHERE relname = ‘posts’; くらいでもよいかも知れません。 インデックスなどレコードデータ以外のメタ情報もあるので WHERE relname like ‘%posts%’ とかして、当てはまるものを合計する必要はあるでしょう。 実際には行をいくつか追加して、前後の増減も見てみたらよいと思います。 回答者: Anonymous