[SQL] Pattern Matchig
Hello everybody I'm trying to make a query in a table to find the records who contains a full word 'im looking for. Here is an example about i'm trying code text 1foobar 2 foobar 3 foobar y 4foobar 5foobars 6foobar. 7 foobar. What i want is to find foobar (records 1, 2, 3 and 4) but no foobars (record 5). If i try SELECT code FROM table WHERE text LIKE '%foobar%' The result is 1, 2, 3, 4, 5, 6, 7. But if I try SELECT code FROM table WHERE text LIKE '% foobar %' The result is only record number 3. How can i search the entire FOOBAR word? The result wanted must be all, excepting 5. Thank you very much. PD: Sorry about my english -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Pattern Matchig
Alvaro Herrera escribió: Tk421 escribió: The result is only record number 3. How can i search the entire FOOBAR word? The result wanted must be all, excepting 5. Something like this: select code from table where text ~ '[[:<:]]foobar[[:>:]]' Thank you very much, it worked! Gracias, Álvaro
Re: [SQL] dateformat issue
Gavin 'Beau' Baumanis escribió:
Hi Everyone,
I have a question about dates, please.
I am using Coldfusion and Postgres
I have a function that takes the ate entered in a form and converts it
into an odbc date format.
So the string ends up being;
{d '2008-04-10'}
Strangely, the function is used throughout the application and causes
no issues but on one particular template.
And this same templates is replicated for other clients - that
seemingly have no issue .
The SQL is simply
Insert into table (datefield) values ({d '2008-04-10'})
Now, I have read the docs and do not see the {d '2008-04-10'} format
as being a valid date data type...
But it works elsewhere in the application / on other sites - thus my
current state of confusion.
Thanks in advance for any ideas you might have.
Please contact me if you should have any questions.
Gavin 'Beau' Baumanis
Senior Application Developer
PalCare Pty. Ltd.
E: [EMAIL PROTECTED]
T: +61 -3 9318 4567
M: +61 -4 38 545 586
W: http://www.palcare.com.au
I had some similar probles with dates, and i solved it using a
string format (mmdd) to set date format, and two functions (toString
and toDate) made by myself inf the necesary language (asp, php, jsp)
This format is full valid for comparations (>, <, =) and orders in
SQL. The only problem is to work in your application, but it may be
solved with some functions
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem inserting data
Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column "premarco" is of type boolean but expression is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem inserting data
Adrian Klaver escribió: On Friday 20 June 2008 5:20 am, Tk421 wrote: Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column "premarco" is of type boolean but _expression_ is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much Two options: 1) Change the column in Postgres to an integer type. 2) Change the assignment in pg_cast from explicit to implied for the int4,bool cast. See for more details: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html Thank you very much. I've choosen the second option, using this query: update pg_cast set castcontext='i' where castsource=23 and casttarget=16
[SQL] A table with two names or table alias
Can I create a table name alias? What i want is to access the same table with two different names. An example: I've the table CITY with fields code and name. I want to know if i can create an alternative name to table CITY, like LOCATION, to make possible these two sentences: insert into CITY values (10, 'New York'); insert into LOCATION values (11, 'Paris') Thank you. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Sequence and nextval problem
Hello everybody.
I've got an vb aplication that uses an Access database. I'm trying
to convert the database to postgres. The conversion was done ok, but
i've got a little problem that i don't know how to solve. Let's see if
anyone can help me.
The conversion from access database to postgres worked fine.
Everithing it's ok. But now, when i use my database i've found a problem
with sequences. In the conversion, the "autonumeric" fields from access
have been converted to sequences, everithing ok in a first view. The
problem comes because the autonumeric fields in access always return the
last value of the table +1, but postgres no. Postgres returns "lost" (i
don't know how to call them) values. An example.
This is an example of a table:
code | description
- |
1 | desc 1
2 | desc 2
6 | desc 6
7 | desc 7
In access if i execute "INSERT INTO table (description) VALUES
('desc 8'), the result row is 8 | desc 8
But in postgres the same query te result row is 3 | desc 8
My question is, can i do something to make ANY sequence to take the
last value from his associated table, and not a "lost" value?
Thank you very much
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] EXECUTE query INTO problem
I've got a problem with a function: It receives two parameters, the first, the table name, and the second, a where condition. The function looks like this: DECLARE cod bigint; query TEXT; BEGIN query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2; EXECUTE query INTO cod; · · · END; I've alwais get the same error, in the EXECUTE sentence: it says: Error at or near NULL at character X I've also tried declaring cod as row, but the error is the same. Anybody can help me? Thank you very much -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem inserting image
I've made a routine in VB to insert a image into a table. The code
is:
Set cmd = New ADODB.Command
cmd.ActiveConnection = objconexion
cmd.CommandText = "INSERT INTO tb_tmp_ventanas (ventana)
VALUES (?)"
archivo = "c:\image.jpg"
fd = FreeFile
Open archivo For Binary Access Read As fd
flen = LOF(fd)
Set imagen = cmd.CreateParameter("imagen", adLongVarBinary,
adParamInput, flen)
ReDim chunk(1 To flen)
Get fd, , chunk()
imagen.AppendChunk chunk()
cmd.Parameters.Append imagen
Set rsInsert = cmd.Execute
Close
But on execute there is the next error: ERROR: type "lo" does not
exist;
How can i reference the "lo" type or create it, in postgres 8.3
Thank you very much
Re: [SQL] How to Import Excel Into PostgreSQL database
Try this: http://sqlmanager.net/products/postgresql/manager/ It's an application that includes a lot of tools for importing and exporting databases. It's the best tool i've used for these kind of actions, and to manage postgreSQL databases Venkat Rao Tammineni escribió: Dear All, I using PosgreSql8.1.I want to import Excel into PostgreSQL database. Is there any way to import ? . Please anyone can guide me. I am waiting for your great response. Thanks Venkat.
