Re: [SQL] indexing longish string

[jose]

Why don't you use some type of hash like md5 for indexing ?

2010/11/30 Rob Sargent :
> Were we to create a table which included a text field for a small block
> of xml (100-1000 chars worth), would an index on that field be useful
> against exact match queries?
>
> We're wondering if a criterion such as "where 'a string expected to be
> of size range 100 to 500' = tabelWithStrings.stringSearched" would make
> good use of an index on "stringSearched" column.
>
> The key is that there will only be exact match queries.
>
> Thanks for your thoughts.
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Composite Types

[Jose]

Hello
I am using  postgres r8.1 and i make 2 Composite Types
as:
CREATE TYPE regiao AS   (pais_nome 
varchar(150),    estado_nome 
varchar(150),    estado_sigla 
varchar(4),    cidade_nome varchar(150));ALTER TYPE 
regiao OWNER TO postgres;==
and 
CREATE TYPE telcontato AS   
(telefone1 varchar(15),    telefone2 
varchar(15),    telefone3 varchar(15));ALTER TYPE 
telcontato OWNER TO postgres;===
and a table as
REATE TABLE unidades(  unidade_pa int4 
NOT NULL,  unidade_tipo int4,  unidade_nome varchar(100) NOT 
NULL,  unidade_razao varchar(150),  unidade_bandeira 
varchar(1),  unidade_endereco varchar(150),  unidade_bairro 
varchar(80),  unidade_regiao regiao,  unidade_cep 
varchar(10),  unidade_cnpj varchar(110),  unidade_radio 
varchar(15),  unidade_contato telcontato,  unidade_abertura 
date,  unidade_contrato date,  CONSTRAINT unidades_pkey 
PRIMARY KEY (unidade_pa)) WITHOUT OIDS;ALTER TABLE unidades OWNER TO 
postgres;
I can insert without problems rows in my table and 
of corse I can retreave this information if I use select * from 
.
ok
My problem is , How I can make a query to 
retreave  the cidade_nome  from table unidades
If I use for example
select unidade_regiao.estado_sigla from 
unidades
I recive this message:
ERROR:  missing FROM-clause entry for 
table "unidade_regiao"
If I try use
select unidade_regiao.(estado_sigla) from unidades
 
I recive this message
ERROR:  syntax error at or near "(" at character 23
 
So I ask How I can retrive a specific information if I use a Composite 
Types ?
 
Thaks any help
J. V. Finger
 
 
 

[SQL] Function in C++

[Jose Mendoza]

 

I would like to have, only if it is possible, a small
example of how can be that done. I have this problem now because I have a very
light interface using the ODBC and with the same code I can connect to
Postgresql (servers Linux) and SQLserver (servers Windows). And the libpq++
works in C++ if I reject the idea of use ODBC.

 

Any help will be appreciated.

 

Thans

 

Jose Mendoza

[EMAIL PROTECTED]

 

 

 

[SQL] MySQL DB to PostgresSQL DB

[Jose Apablaza]

Dear all,
This is my first post and I will try to be short and clear in my questions and I hope you can help me with that.
I have a MySQL DB and I want to export to PostgreSQL DB.
My questions are;
- Is posible to export MySQL DB to PostgreSQL DB?
- Does PostgreSQL DB has tools to import MySQL DB?
- If is posible, How do I need to build the DB in MySQL?, in order to have success in the exportation.
- Do someone did it before?, exporting MySQL DB to PostgreSQL DB?
- What kind of risk do we can to have in this process?
- How long can take this process?

Thanks in advance.
Jose

  

Re: [SQL] SQL Developer accessing PostgreSQL

[Jose Alarcon]

Squirrel been tested. It
is opensource, and is done in Java using JDBC to support many
databases. It
is a universal SQL client, see

http://www.squirrelsql.org/

El 29/03/10 18:22, Hiltibidal, Rob escribió:

  
  
  SQL Developer accessing PostgreSQL
  

  
  I
recommend Aqua Data Studio
   
  Just
drop in the jdbc jar
   
   
   
  
  
  From:
pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On
Behalf
Of Snyder, James
  Sent: Monday, March 29, 2010 11:34 AM
  To: pgsql-sql@postgresql.org
  Subject: [SQL] SQL Developer accessing PostgreSQL
  
  
   
  Hello,
  Is there a way
to configure
Oracle’s SQL Developer to access a PostgreSQL database? 
  Thanks,Jim
  
  PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above.  If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender.  If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmas...@argushealth.com.  Thank you.



  




<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] problem with the Index

[Jose Antonio Leo]

I have a problem with the index of 1 table.

I hava a table created :
CREATE TABLE "acucliart" (
   "cod_pto" numeric(8,0) NOT NULL,
   "cod_cli" varchar(9) NOT NULL,
   "mes" numeric(2,0) NOT NULL,
   "ano" numeric(4,0) NOT NULL,
   "int_art" numeric(5,0) NOT NULL,
   "cantidad" numeric(12,2),
   "ven_siv_to" numeric(14,2),
   "ven_civ_to" numeric(14,2),
   "tic_siv_to" numeric(14,2),
   "tic_civ_to" numeric(14,2),
   "visitas" numeric(2,0),
   "ult_vis" date,
   "ven_cos" numeric(12,2),
   "ven_ofe" numeric(12,2),
   "cos_ofe" numeric(12,2),
   CONSTRAINT "acucliart_pkey"
   PRIMARY KEY ("cod_cli")
);

if i do this select:
explain select * from acucliart where cod_cli=1;
postgres use the index
NOTICE:  QUERY PLAN:
Index Scan using cod_cli_ukey on acucliart  (cost=0.00..4.82 rows=1
width=478)

and this select
explain select * from acucliart where cod_cli>1;
Postgres don't use the index:
NOTICE:  QUERY PLAN:
Seq Scan on acucliart  (cost=0.00..22.50 rows=333 width=478)

why?


tk


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] prosgrees + java + trasnacciones

[jose antonio leo]

Hola a todos!
Intento hacer una aplicacion en java que va contra pgsql y me gustaria
bloquear registros.

Un jps llama a un metodo de una clase para modificar un registro. Lo primero
que hace es una consulta del registro (aquí quiero bloquear el registro) y
despues se muestra en el navegador el resultado, un usuario lo modifica,
pulsa el botón de actualizar y lo actualiza, desbloqueo.

He probado con select for update y lo hace bien, solo que si un usuario
quiere consultarlo para modificar tambien el mismo registro, se queda
esperando a que el otro acabe la actualización.

Hay alguna manera de detectar que ese registro se está consultando en select
for update para que no se quede esperando o hay alguna forma de hacerlo?

Gracias por todos y un saludo


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] newbie question * compare integer in a "where IN" statement

[Jose Ig Mendez]

Title: Firma Correo

  
  


Hi everybody,

I'm trying to compare in a sentence like this (using PostGres 8.3) :

select * from myTable where id_integer IN ('1,2,3,4')

I want to get the records which key "id_integer" is 1 or 2 or 3 or
4. the type od my "id", of course, is integer.

I've tried many differents ways but I cannot get the result I want.

I would like to cast the integer parameter I cannot change the part
after "IN" it has to be a "string list".

Do I have to use a function ? I would like not to use it.

How can I compare a key (integer) with a lists of values ?

I'm desperate

THANX IN ADVANCE

-- 
  
  
  
  
  
José Ignacio Méndez Yanes
Área de Operaciones / Area of Operations
Phone: +34 916
011 373 / +34 946 416 066
Mobile: +34 666
431 099
Fax: +34 916 011 372 / +34 944 318 286

  

   
   Alcalde Ángel Arroyo 10, 1ª
Planta. 28904.
  Getafe, Madrid
Kanala Bidea, Edif. 103, 1ª
Izda. Parque Tecnológico. 48170. Zamudio, Bizkaia
www.andago.com
  

  

  
  
  Síguenos en: Twitter - Facebook
  - LinkedIn
  - YouTube
  

  
  
 Antes de imprimir este mensaje, asegúrese
de que es necesario. Consider the environment before
printing this mail. 
  

  
  AVISO LEGAL
  
  ANDAGO
  CONSULTING SL / ANDAGO INGENIERÍA, SL le informa que los
datos facilitados por Ud. y utilizados para el envío de esta
comunicación serán objeto de tratamiento automatizado o no en
nuestros ficheros, con la finalidad de gestionar la agenda de
contactos de nuestra empresa y para el envío de comunicaciones
profesionales por cualquier medio electrónico o no. Vd. podrá en
cualquier momento ejercer el derecho de acceso, rectificación,
cancelación y oposición en los términos establecidos en la Ley
Orgánica 15/1999. El responsable del tratamiento es ANDAGO
CONSULTING SL / ANDAGO INGENIERIA SL, con domicilio en C/
ALCALDE ANGEL ARROYO, 10, 28904, Getafe (Madrid).
  El contenido de
esta comunicación, así como el de toda la documentación anexa,
es confidencial y va dirigido únicamente al destinatario del
mismo. En el supuesto de que usted no fuera el destinatario, le
solicitamos que nos lo indique y no comunique su contenido a
terceros, procediendo a su destrucción. 
  DISCLAIMER
  
  The content of
this communication and any attached information is confidential
and exclusively for the use of the addressee. If you are not the
addressee, we ask you to notify to the sender and do not pass
its content to another person, and please be sure you destroy
it. 

  

Re: [SQL] newbie question * compare integer in a "where IN" statement

[Jose Ig Mendez]

Title: Firma Correo

  
  
Hi,

Thank you very much Pavel, that solves my problem

Regards

On 13/07/11 09:50, Pavel Stehule wrote:
Hello
  
  you can try
  
  SELECT .. FROM .. WHERE id = ANY(string_to_array('1,2,3,4,5',','))
  
  other forms are slow
  
  Regards
  
  Pavel Stehule
      
  2011/7/13 Jose Ig Mendez <jmen...@andago.com>

   
Hi everybody,

I'm trying to compare in a sentence like this (using
PostGres 8.3) :

select * from myTable where id_integer IN ('1,2,3,4')

I want to get the records which key "id_integer" is 1 or 2
or 3 or 4. the type od my "id", of course, is integer.

I've tried many differents ways but I cannot get the result
I want. 
I would like to cast the integer parameter I cannot change
the part after "IN" it has to be a "string list".

Do I have to use a function ? I would like not to use it.

How can I compare a key (integer) with a lists of values ?

I'm desperate

THANX IN ADVANCE

-- 
  
  
José
Ignacio Méndez Yanes
Área
  de Operaciones / Area of Operations
Phone: +34 916 011 373
  / +34 946
416 066
Mobile: +34 666 431 099
Fax: +34 916 011 372
  / +34 944
318 286

  

   
   Alcalde Ángel
Arroyo 10, 1ª Planta. 28904.
  Getafe, Madrid
Kanala Bidea,
Edif. 103, 1ª Izda. Parque Tecnológico.
  48170. Zamudio, Bizkaia
www.andago.com
  

  

  
  
  Síguenos en: Twitter
  - Facebook
  - LinkedIn - YouTube
  

  
  
 Antes
de imprimir este mensaje, asegúrese de que es
necesario. Consider the environment before
printing this mail. 
  

  
  AVISO
LEGAL
  
  ANDAGO
  CONSULTING SL / ANDAGO INGENIERÍA, SL le informa
que los datos facilitados por Ud. y utilizados para el
envío de esta comunicación serán objeto de tratamiento
automatizado o no en nuestros ficheros, con la finalidad
de gestionar la agenda de contactos de nuestra empresa y
para el envío de comunicaciones profesionales por
cualquier medio electrónico o no. Vd. podrá en cualquier
momento ejercer el derecho de acceso, rectificación,
cancelación y oposición en los términos establecidos en
la Ley Orgánica 15/1999. El responsable del tratamiento
es ANDAGO CONSULTING SL / ANDAGO INGENIERIA SL, con
domicilio en C/ ALCALDE ANGEL ARROYO, 10, 28904, Getafe
(Madrid).
  El contenido
de esta comunicación, así como el de toda la
documentación anexa, es confidencial y va dirigido
únicamente al destinatario del mismo. En el supuesto de
que usted no fuera el destinatario, le solicitamos que
nos lo indique y no comunique su contenido a terceros,
procediendo a su destrucción. 
  DISCLAIMER
  
  The content of
this communication and any attached information is
confidential and exclusively for the use of the
addressee. If you are not the addressee, we ask you to
notify to the sender and do not pass its content to
another person, and please be sure you destroy it. 

  

  
  


-- 
  
  
  
  
  
José Ignacio Méndez Yanes
Área de Operaciones / Area of Operations
Phone: +34 916
011 373 / +34 946 416 066
Mobile: +34 666
431 099
Fax: +34 916 011 372 / +34 944 318 286

  

   
   Alcalde Ángel Arroyo 10, 1ª

[SQL] newbie question * compare integer in a "where IN" statement

[Jose Ig Mendez]

Title: Firma Correo

  
  
Hi everybody,

I'm trying to compare in a sentence like this (using PostGres 8.3) :

select * from myTable where id_integer IN ('1,2,3,4')

I want to get the records which key "id_integer" is 1 or 2 or 3 or
4. the type od my "id", of course, is integer.

I've tried many differents ways but I cannot get the result I want.

I would like to cast the integer parameter I cannot change the part
after "IN" it has to be a "string list".

Do I have to use a function ? I would like not to use it.

How can I compare a key (integer) with a lists of values ?

I'm desperate

THANX IN ADVANCE

-- 
  
  
  
  
  
José Ignacio Méndez Yanes
Área de Operaciones / Area of Operations
Phone: +34 916
011 373 / +34 946 416 066
Mobile: +34 666
431 099
Fax: +34 916 011 372 / +34 944 318 286

  

   
   Alcalde Ángel Arroyo 10, 1ª
Planta. 28904.
  Getafe, Madrid
Kanala Bidea, Edif. 103, 1ª
Izda. Parque Tecnológico. 48170. Zamudio, Bizkaia
www.andago.com
  

  

  
  
  Síguenos en: Twitter
  - Facebook
  - LinkedIn
  - YouTube
  

  
  
 Antes de imprimir este mensaje, asegúrese
de que es necesario. Consider the environment before
printing this mail. 
  

  
  AVISO LEGAL
  
  ANDAGO
  CONSULTING SL / ANDAGO INGENIERÍA, SL le informa que los
datos facilitados por Ud. y utilizados para el envío de esta
comunicación serán objeto de tratamiento automatizado o no en
nuestros ficheros, con la finalidad de gestionar la agenda de
contactos de nuestra empresa y para el envío de comunicaciones
profesionales por cualquier medio electrónico o no. Vd. podrá en
cualquier momento ejercer el derecho de acceso, rectificación,
cancelación y oposición en los términos establecidos en la Ley
Orgánica 15/1999. El responsable del tratamiento es ANDAGO
CONSULTING SL / ANDAGO INGENIERIA SL, con domicilio en C/
ALCALDE ANGEL ARROYO, 10, 28904, Getafe (Madrid).
  El contenido de
esta comunicación, así como el de toda la documentación anexa,
es confidencial y va dirigido únicamente al destinatario del
mismo. En el supuesto de que usted no fuera el destinatario, le
solicitamos que nos lo indique y no comunique su contenido a
terceros, procediendo a su destrucción. 
  DISCLAIMER
  
  The content of
this communication and any attached information is confidential
and exclusively for the use of the addressee. If you are not the
addressee, we ask you to notify to the sender and do not pass
its content to another person, and please be sure you destroy
it. 

  

[SQL] Function that returns a tuple.

[Claudio Jose Zanardi Grillo]

How can I write a function in sql or pl- pgsql that returns a set of
tuples ?

Thanks.

Claudio

Re: [SQL] count( distinct x )

[Jose Rodrigo Fernandez Menegazzo]

> The problem I have is with this statement:
>
> select count( distinct area ) from areapostcode where postcode like
> 'BS1%'
>
> the above statement fails with
> ERROR:  parser: parse error at or near "distinct"
>
> I am not the greatest when it comes to SQL, but the pgsql docs implied
> that the above would work.
>
> What I am trying to do is get a count of the no of matches from the
> statement below
> select distinct area from areapostcode where postcode like 'BS1%'
>
> Not the count of:
> select area from areapostcode where postcode like 'BS1%'
>

I don't have where to try it, but have you tried:

select distinct count(area) from areapostcode where postcode like 'BS1%'

Rodrigo F.

[SQL] Using count on a join, group by required?

[Jose Ildefonso Camargo Tolosa]

Greetings!

First: This is working, I just need a clarification on concept, so, it
is not necessary for you to look deeply at the SQL statement.

I have this:

Table: products that references manufacturer via
products.manufacturer_id to manufacturer.id (not important, just
informative).
Table: product_serials that references products via
product_serials.product_id to products.id

And I wanted to get this output:

Product ID, Product Name, Product Code, Manufacturer ID, Manufacturer
Name, Number of Associated Serials.

So, I build a query for that:

select products.id as product_id,products.name as name,products.code
as code,manufacturer.id as manufacturer_id,manufacturer.name as
manufacturer_name,count(product_serials.product_id) as num_serials
from products left join manufacturer on
products.manufacturer_id=manufacturer.id left join product_serials on
product_serials.product_id=products.id group by
products.id,products.name,products.code,manufacturer.id,manufacturer.name;

And it works, it gives me something like:

 product_id | name | code  | manufacturer_id |
manufacturer_name  | num_serials
+--+---+-++-
 17 | THE product  | 1235711131719 |  19 |
THE product's manufacturer |   5
  6 | Car Battery 500A 12V | 7591512021575 |   8 |
Acumuladores Duncan, C.A.  |  11
  1 | Test product 1   | 123456789012  |   1 |
Test Manufacturer  |   6

Which is correct, and exactly what I wanted.

So far, so good.  The thing is: the group by clause, I had to add it
because the parser forced me to, because it complained like this:

ERROR:  column "manufacturer.name" must appear in the GROUP BY clause
or be used in an aggregate function

and I had to include *all* the requested columns on the group by
clause, can anybody tell me why? or at least point to some doc that
help me understanding this?

Thanks in advance,

Ildefonso Camargo

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Using count on a join, group by required?

[Jose Ildefonso Camargo Tolosa]

On Wed, Aug 11, 2010 at 8:09 AM, Peter Eisentraut  wrote:
> On tis, 2010-08-10 at 22:21 -0430, Jose Ildefonso Camargo Tolosa wrote:
>> And it works, it gives me something like:
>>
>>  product_id |         name         |     code      | manufacturer_id |
>>     manufacturer_name      | num_serials
>> +--+---+-++-
>>          17 | THE product          | 1235711131719 |              19 |
>> THE product's manufacturer |           5
>>           6 | Car Battery 500A 12V | 7591512021575 |               8 |
>> Acumuladores Duncan, C.A.  |          11
>>           1 | Test product 1       | 123456789012  |               1 |
>> Test Manufacturer          |           6
>>
>> Which is correct, and exactly what I wanted.
>>
>> So far, so good.  The thing is: the group by clause, I had to add it
>> because the parser forced me to, because it complained like this:
>>
>> ERROR:  column "manufacturer.name" must appear in the GROUP BY clause
>> or be used in an aggregate function
>>
>> and I had to include *all* the requested columns on the group by
>> clause, can anybody tell me why? or at least point to some doc that
>> help me understanding this?
>
> This is fixed in PostgreSQL 9.1devel (*); there you only need to put the
> primary key into the GROUP BY clause.  Earlier versions didn't know that
> that was enough to ensure a deterministic result.
>
> (*) -- It will probably be a bit over a year before that is released.

Ok, I see, so, in the meantime, just add all columns: no big deal, I
was only curious on why.

Thanks for your answer!

Ildefonso.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Question about POSIX Regular Expressions performance on large dataset.

[Jose Ildefonso Camargo Tolosa]

Hi!

I'm analyzing the possibility of using PostgreSQL to store a huge
amount of data (around 1000M records, or so), and these, even
though are short (each record just have a timestamp, and a string that
is less than 128 characters in length), the strings will be matched
against POSIX Regular Expressions (different regexps, and maybe
complex).

Because I don't have a system large enough to test this here, I have
to ask you (I may borrow a medium-size server, but it would take a
week or more, so I decided to ask here first).  How is the performance
of Regexp matching in PostgreSQL?  Can it use indexes? My guess is:
no, because I don't see a way of generally indexing to match regexp :(
, so, tablescans for this huge dataset.

What do you think of this?

Sincerely,

Ildefonso Camargo

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Question about POSIX Regular Expressions performance on large dataset.

[Jose Ildefonso Camargo Tolosa]

Hi, again,

I just had this wacky idea, and wanted to share it:

what do you think of having the dataset divided among several servers,
and sending the query to all of them, and then just have the
application "unify" the results from all the servers?

Would that work for this kind of *one table* search? (there are no
joins, and will never be).  I think it should, but: what do you think?

Ildefonso.

On Tue, Aug 17, 2010 at 9:51 PM, Jose Ildefonso Camargo Tolosa
 wrote:
> Hi!
>
> I'm analyzing the possibility of using PostgreSQL to store a huge
> amount of data (around 1000M records, or so), and these, even
> though are short (each record just have a timestamp, and a string that
> is less than 128 characters in length), the strings will be matched
> against POSIX Regular Expressions (different regexps, and maybe
> complex).
>
> Because I don't have a system large enough to test this here, I have
> to ask you (I may borrow a medium-size server, but it would take a
> week or more, so I decided to ask here first).  How is the performance
> of Regexp matching in PostgreSQL?  Can it use indexes? My guess is:
> no, because I don't see a way of generally indexing to match regexp :(
> , so, tablescans for this huge dataset.
>
> What do you think of this?
>
> Sincerely,
>
> Ildefonso Camargo
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Reading from file without superuser privilege

[Jose Antonio Quintana/UPC]

I need to read from a file in order to update a table.

The manual says that it is necessary to have the superuser privilege to 
read from a file.

Is it possible to read files without this privilege?

Thanks.


___
José Antonio Quintana Romero
Unitat de Projectes
Vicegerència de Desenvolupament Organitzatiu i Personal
Edifici Vèrtex. Planta 3
Pl. Eusebi Güell, 6 
08034 - Barcelona