Re: [GENERAL] find a string contained in an attribute
Do you have a link where I can read more about this because is not easy to
understand.
Thanks for your help,
karina
On Thu, Mar 25, 2010 at 1:34 AM, Pavel Stehule wrote:
> 2010/3/24 Szymon Guz :
> > 2010/3/24 Karina Guardado
> >>
> >> Hi,
> >>
> >> I want to know if it is possible to search for a string of characters
> >> inside an attribute for example I have the following table and values
> >>
> >> cod_unidad | nombre_uni
> >> +-
> >> 1 | Facultad de Ciencias Naturales y Matemática
> >> 2 | Facultad de Ciencias Humanidades
> >> 3 | Facultad de Ingeniería
> >> 4 | Facultad de Agronomía
> >> 5 | Oficinas Centrales
> >> 6 | test
> >>
> >>
> >> I want to retrieve all the rows where I find the word Ciencias for
> >> example. Is there a function or with select is possible to do it?
> >>
> >> thanks in advance,
> >>
> >> karina
> >> El Salvador, Centroamerica
> >
> > For example something like this should work:
> > SELECT * FROM table WHERE nombre_uni like '%Ciencias%';
> > more you can find
> > here:
> http://www.postgresql.org/docs/8.4/interactive/functions-matching.html
> > regards
> > Szymon Guz
>
> better to use a fulltext it is much faster on tables larger than small
>
> create index foo on tab using gin(to_tsvector('simple', nombre_uni))
>
> select * from tab where to_tsvector('simple', nombre_uni) @@
> to_tsquery('simple', 'Ciencias');
>
> Regards
> Pavel Stehule
>
Re: [GENERAL] data type to store a lot of text and tables of data
Thank you very much for your help and so soon answears. I will try using the solutions you suggest. best regards, karina On Wed, Mar 24, 2010 at 4:44 PM, Raymond O'Donnell wrote: > On 24/03/2010 22:39, Karina Guardado wrote: > > You know I need to design a table where some attributes have a data type > > that allow me to store text mixed with tables and other information. I > > use PHP code to get the information and store it in to the database so I > > wonder if this is posible to do. I found in the link you provided me > > that If character varying is used without length specifier, the type > > accepts strings of any size. So this is useful for me when only text has > > to be stored but when the user wants to store information mixed with > > tables like the following : > > I suppose it depends on what the format of the data is the example > you posted is HTML, which is pure text, so could be stored in a VARCHAR > or TEXT column. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
Re: [GENERAL] data type to store a lot of text and tables of data
You know I need to design a table where some attributes have a data type that allow me to store text mixed with tables and other information. I use PHP code to get the information and store it in to the database so I wonder if this is posible to do. I found in the link you provided me that If character varying is used without length specifier, the type accepts strings of any size. So this is useful for me when only text has to be stored but when the user wants to store information mixed with tables like the following : Reciba un respetuoso saludo y deseo de éxitos en sus actividades. El motivo de la presente es para solicitarle se realice una corrección en el plan de mantenimiento preventivo y correctivo de fotocopiadoras, duplicadores e impresoras debido a que en el Centro de Cómputo aparecen, según el reporte, 4 impresoras asignadas y en realidad solamente tenemos dos que son las que detallo a continuación: Impresora Marca Modelo No. De Inventario Observaciones X HP LaserJet 1300 12040-3701-080-0005 Si aparece en el listado. X HP LaserJet 3055 12040-0603-080-0002 No aparece en el listado I hope this help, thanks a lot, karina On Wed, Mar 24, 2010 at 4:08 PM, Raymond O'Donnell wrote: > On 24/03/2010 22:01, Karina Guardado wrote: > > > what should be the data type to store a file that can be xls, doc, pdf > > for example. > > Sorry, I missed this bit - for binary files you can use the bytea type. > Look under "Binary data types" in the docs. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
Re: [GENERAL] data type to store a lot of text and tables of data
for example something like the following : *Table 9-25. Date/Time Operators* OperatorExampleResult + date '2001-09-28' + integer '7'date '2001-10-05' + date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00:00' + date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00:00' + interval '1 day' + interval '1 hour'interval '1 day 01:00:00' + timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00:00' + time '01:00' + interval '3 hours'time '04:00:00' - - interval '23 hours'interval '-23:00:00' - date '2001-10-01' - date '2001-09-28'integer '3' - date '2001-10-01' - integer '7'date '2001-09-24' - date '2001-09-28' - interval '1 hour'timestamp '2001-09-27 23:00:00' - time '05:00' - time '03:00'interval '02:00:00' - time '05:00' - interval '2 hours'time '03:00:00' - timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00:00' - interval '1 day' - interval '1 hour'interval '1 day -01:00:00' - timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00:00' * 900 * interval '1 second'interval '00:15:00' * 21 * interval '1 day'interval '21 days' * double precision '3.5' * interval '1 hour'interval '03:30:00' / interval '1 hour' / double precision '1.5'interval '00:40:00' is it possible? or is better to use an atached file and store it ? and what should be the data type to store a file that can be xls, doc, pdf for example. On Wed, Mar 24, 2010 at 3:55 PM, Raymond O'Donnell wrote: > On 24/03/2010 21:48, Karina Guardado wrote: > > Hi, > > > > Is there a special data type to use to store a lot of text and tables of > > data in a column or attribute? > > For large amounts of text, just use the TEXT data type: > >http://www.postgresql.org/docs/8.4/static/datatype-character.html > > Not sure what you mean by "tables of data". > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
[GENERAL] data type to store a lot of text and tables of data
Hi, Is there a special data type to use to store a lot of text and tables of data in a column or attribute? thanks in advance, regards, karina El Salvador, Centroamerica
Re: [GENERAL] find a string contained in an attribute
Thanks a lot for your help that's exactly what I wanted. It worked fine. regards, karina On Wed, Mar 24, 2010 at 3:25 PM, Szymon Guz wrote: > 2010/3/24 Karina Guardado > > Hi, >> >> I want to know if it is possible to search for a string of characters >> inside an attribute for example I have the following table and values >> >> cod_unidad | nombre_uni >> +- >> 1 | Facultad de Ciencias Naturales y Matemática >> 2 | Facultad de Ciencias Humanidades >> 3 | Facultad de Ingeniería >> 4 | Facultad de Agronomía >> 5 | Oficinas Centrales >> 6 | test >> >> >> I want to retrieve all the rows where I find the word Ciencias for >> example. Is there a function or with select is possible to do it? >> >> thanks in advance, >> >> karina >> El Salvador, Centroamerica >> > > For example something like this should work: > > SELECT * FROM table WHERE nombre_uni like '%Ciencias%'; > > more you can find here: > http://www.postgresql.org/docs/8.4/interactive/functions-matching.html > > regards > Szymon Guz >
[GENERAL] find a string contained in an attribute
Hi, I want to know if it is possible to search for a string of characters inside an attribute for example I have the following table and values cod_unidad | nombre_uni +- 1 | Facultad de Ciencias Naturales y Matemática 2 | Facultad de Ciencias Humanidades 3 | Facultad de Ingeniería 4 | Facultad de Agronomía 5 | Oficinas Centrales 6 | test I want to retrieve all the rows where I find the word Ciencias for example. Is there a function or with select is possible to do it? thanks in advance, karina El Salvador, Centroamerica
[GENERAL] problems with encoding
Hi,
I have a Map info file named map.TAB and when I tried to export it to
Postgres I get an error of encoding so I used konwert to convert it to utf8
using the following statement
konwert any/es-utf8 map.TAB -O
But this only convert the name of the header of each column but not the
registers of each column so I don't know what to do in order to make it work
and I get the following error
ERROR 1: INSERT command for new feature failed.
ERROR: invalid byte sequence for encoding "UTF8": 0xe17465
HINT: This error can also happen if the byte sequence does not match the
encoding expected by the server, which is controlled by "client_encoding".
Command: INSERT INTO "geocalderas" (wkb_geometry , "fnode_", "tnode_",
"lpoly_", "rpoly_", "length", "geo_lin_", "geo_lin_id", "fnode1", "tnode1",
"lpoly1", "rpoly1", "geo_lin1", "geo_lin__1", "fnode1_1", "tnode1_1",
"lpoly1_1", "cod", "iden", "descripció") VALUES
(GeomFromEWKT('SRID=32767;LINESTRING (447585.611617154267151
352945.605798984819558,447663.959493021073285
352903.167054586228915,447669.807996430899948
352846.719404269650113,447645.862237187044229
352814.281670245632995,447594.825768752663862
352805.778938578849193,447551.734437025908846
352808.138727543642744)'::TEXT) , 291, 297, 0,
0, 281.033, 175,83, 0, 0, 0,
0, 0, 0, 0, 0, 0,600300,
12083, 'Cr�ter, caldera')
ERROR 1: Terminating translation prematurely after failed
any help please,
thanks
karina
[GENERAL] problems with function pg_catalog.btrim(date)
Hi everybody, I hope some one can help me, I have created the following function but I always get the error that there is not function pg_catalog.btrim(date), in the version postgresql 8.1 it worked but now it does not so I don't know if it is related with a configuration problem of the postgresql or somethin in the function code : thanks karina CREATE FUNCTION edad(date) RETURNS integer AS $_$ DECLARE fechnac ALIAS FOR $1; dia_nac integer; mes_nac integer; anho_nac integer; fecha_nac date; fecha_temp text; hoy date; dia integer; mes integer; anho integer; temp integer; edad integer; BEGIN hoy := current_date; hoy := date(trim(hoy)); fecha_nac:= date(trim(fechnac)); fecha_temp:=CAST(fecha_nac AS text ); dia_nac :=CAST(substring(fecha_temp from 1 for 2) AS integer ); mes_nac := CAST (substring(fecha_temp from 4 for 2) AS integer); anho_nac :=CAST(substring(fecha_temp from 7 for 4) AS integer ); dia :=CAST(substring(hoy from 1 for 2) AS integer ); mes := CAST (substring(hoy from 4 for 2) AS integer); anho := CAST(substring(hoy from 7 for 4) AS integer ); edad:= (anho-anho_nac)-1; IF mes+1-mes_nac < 0 THEN RETURN edad; END IF; IF mes+1-mes_nac < 0 THEN RETURN edad; END IF; IF mes+1-mes_nac > 0 THEN RETURN edad+1; END IF; IF dia-dia_nac >= 0 THEN RETURN edad+1; ELSE RETURN edad; END IF; END; $_$ LANGUAGE plpgsql; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problems with special characters
Hi list, I have a problem I have created a database with encoding SQL_ASCII and when I insert the data using a terminal in linux and insert the data for example Insert into mytable values(1,'Eléctrico'); it works fine but if I try to copy this data from a text file doing \copy mytable from textfile, it insert the data but instead of é writes a ? and I don't have any idea how to copy the data without this problem. I really appreciate your help. thanks karina -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
