Thread: Partial Search
I am trying to do a query with a partial search but don't know the syntax. eg. I want the word 'buff' to find 'buffalo' in the variable 'city'. Can anyone give me an example? -- Cheers, Derek
pez@aiming.net wrote: > > I am trying to do a query with a partial search but don't know the > syntax. eg. I want the word 'buff' to find 'buffalo' in the variable > 'city'. Can anyone give me an example? > select * from table where city like '%buff%'; or case insensitive: select * from table where city ~* 'buff';
Hi,
I have a table: orders (id int, val INT4[][]);. The question is how to
select in separate rows values from particular id?
example:
insert into orders values(1,'{{1,1},{1,4},{2,3}}');
select val from orders where id=1; gives me:
val
-------------------
{{1,2},{1,4},{2,3}}
How can I make from it to columned table with first and second int
separated?
mazek
On Wed, 1 Dec 1999, Marcin Mazurek - Multinet SA - Poznan wrote:
> Hi,
> I have a table: orders (id int, val INT4[][]);. The question is how to
> select in separate rows values from particular id?
>
> example:
> insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> select val from orders where id=1; gives me:
> val
> -------------------
> {{1,2},{1,4},{2,3}}
>
If I good undertend you.. You want select values from a array in separate
rows. Hmm see:
select o.val[1][1] from orders o where id=1 UNION ALL select o.val[1][2]
from orders o where id=1;
val
--- 1 2
(2 rows)
And you have selected first dimension of the array now. But it is terrible
method if you have a lot of dimensions and if you want search in array
(for this is '*=' operator ..etc.).
Karel
----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Docs: http://docs.linux.cz (big docs archive)
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
-----------------------------------------------------------------------
On Thu, 2 Dec 1999, Karel Zak - Zakkr wrote:
> > I have a table: orders (id int, val INT4[][]);. The question is how to
> > select in separate rows values from particular id?
> > example:
> > insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> > select val from orders where id=1; gives me:
> > val
> > -------------------
> > {{1,2},{1,4},{2,3}}
> If I good undertend you.. You want select values from a array in separate
> rows. Hmm see:
> select o.val[1][1] from orders o where id=1 UNION ALL select o.val[1][2]
> from orders o where id=1;
> val
> ---
> 1
> 2
> (2 rows)
what i meant was:
c1 c2
1 2
1 4
2 3
how can i do this?
mazek
On Thu, 2 Dec 1999, Marcin Mazurek - Multinet SA - Poznan wrote:
> On Thu, 2 Dec 1999, Karel Zak - Zakkr wrote:
> > > I have a table: orders (id int, val INT4[][]);. The question is how to
> > > select in separate rows values from particular id?
> > > example:
> > > insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> > > select val from orders where id=1; gives me:
> > > val
> > > -------------------
> > > {{1,2},{1,4},{2,3}}
> > If I good undertend you.. You want select values from a array in separate
> > rows. Hmm see:
> > select o.val[1][1] from orders o where id=1 UNION ALL select o.val[1][2]
> > from orders o where id=1;
> > val
> > ---
> > 1
> > 2
> > (2 rows)
> what i meant was:
> c1 c2
> 1 2
> 1 4
> 2 3
> how can i do this?
> mazek
>
Oh man, it is total equivalent:
select o.val[1][1] as c1, o.val[1][2] as c2 from orders o where id=1 UNION ALL
select o.val[2][1], o.val[2][2] from orders o where id=1 UNION ALL
select o.val[3][1], o.val[3][2] from orders o where id=1;
c1|c2
--+--1| 21| 42| 3
(3 rows)
Karel
Don't use arrays. They look very promising but they aren't. Most of these
kind of questions can be resolved by designing a proper relational
database schema.
On 1999-12-01, Marcin Mazurek - Multinet SA - Poznan mentioned:
> Hi,
> I have a table: orders (id int, val INT4[][]);. The question is how to
> select in separate rows values from particular id?
>
> example:
> insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> select val from orders where id=1; gives me:
> val
> -------------------
> {{1,2},{1,4},{2,3}}
>
> How can I make from it to columned table with first and second int
> separated?
>
> mazek
>
>
>
> ************
>
>
--
Peter Eisentraut Sernanders väg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
Arrays do have a role in life: even in a "proper relational" world.
The classical examples are publications with multiple authors, ISBns,
subjects, keywords etc.
Trying to fully normalise attributes like these leads to nonsensical and
unusable database designs.
Cheers,
Stephen.
Peter Eisentraut <peter_e@gmx.net> wrote:
> Don't use arrays. They look very promising but they aren't. Most of these
> kind of questions can be resolved by designing a proper relational
> database schema.
>
> On 1999-12-01, Marcin Mazurek - Multinet SA - Poznan mentioned:
>
> > Hi,
> > I have a table: orders (id int, val INT4[][]);. The question is how to
> > select in separate rows values from particular id?
> >
> > example:
> > insert into orders values(1,'{{1,1},{1,4},{2,3}}');
> > select val from orders where id=1; gives me:
> > val
> > -------------------
> > {{1,2},{1,4},{2,3}}
> >
> > How can I make from it to columned table with first and second int
> > separated?
> >
> > mazek
> >
> >
> >
> > ************
> >
> >
>
> --
> Peter Eisentraut Sernanders väg 10:115
> peter_e@gmx.net 75262 Uppsala
> http://yi.org/peter-e/ Sweden
>
>
>
> ************
--
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 08-8177 1595
Computing & Network solutions. Fax: 08-8177 0133
On Sat, 4 Dec 1999, Stephen Davies wrote:
> Arrays do have a role in life: even in a "proper relational" world.
> The classical examples are publications with multiple authors, ISBns,
> subjects, keywords etc.
> Trying to fully normalise attributes like these leads to nonsensical and
> unusable database designs.
sorry, that is incorrect. in a proper system, you don't run out of
``slots''. say you give each publication an array to hold 3
authors. what happens when that 4th author comes along, then a
fifth? you always have a +1 problem. your database design will stand the
test of time better if you keep a good design.
one may say, ``just put 100 slots for authors''. then you are just
wasting space. if you normalize it (in this case store a table containing
the association between a publication and an author), you never hit an
upper limit, and you don't waste space in all those publication tables.
____________________}John Flinchbaugh{______________________
| -> glynis@hjsoft.com <- john@cs.millersv.edu |
| glynis@netrax.net http://www.hjsoft.com/~glynis/ |
~~Powered by Linux: Reboots are for hardware upgrades only~~
On Sat, 4 Dec 1999, John M. Flinchbaugh wrote: > > Trying to fully normalise attributes like these leads to nonsensical and > > unusable database designs. > ``slots''. say you give each publication an array to hold 3 > authors. what happens when that 4th author comes along, then a > fifth? you always have a +1 problem. your database design will stand the > test of time better if you keep a good design. > > one may say, ``just put 100 slots for authors''. then you are just Hmm... But you can create an array field with variable size. So, it's not waste the space, and have only one limit: 8k record size. So, feel free to use arrays. best regards: --nek;(
"John M. Flinchbaugh" <glynis@butterfly.hjsoft.com> writes:
> On Sat, 4 Dec 1999, Stephen Davies wrote:
>> Arrays do have a role in life: even in a "proper relational" world.
> sorry, that is incorrect. in a proper system, you don't run out of
> ``slots''. say you give each publication an array to hold 3
> authors. what happens when that 4th author comes along, then a
> fifth? you always have a +1 problem.
Actually, Postgres arrays are variable-size, so that argument loses much
of its force. AFAICT, even if you specify a particular size in the
table declaration, Postgres ignores it and makes each tuple's array just
big enough to hold whatever gets stored there.
I have used arrays in some cases where they seemed to be just the right
thing and made the database design much cleaner than it would have been
otherwise. For example, in a futures-trading transaction database,
I made the fill price and contract count be arrays, so that I could
represent "split fill" orders (you to broker: "Buy 10 June S&P futures";
broker to you: "OK, I got 6 at 1415.5 and 4 at 1415.6"). Normalizing
this problem into separate transaction and price tables with an
association table is obviously ridiculous; nor did I like the choice of
representing a split fill as two or more separate transaction entries.
That wastes space for duplicate storage of the other columns, and also
means that you have to work harder to group together several database
records as a single "logical trade". One record per trade with the
ability to store multiple fill prices is the right design here.
Arrays have lots of problems though. For one, there's no clean way to
do something like "find all the records that have FOO in any element of
this array column". We could invent some operators that do that kind of
thing, but they're not there now. Also, you can't expect to put a
really large number of items in an array, because of the system's limit
on overall tuple length; but that limit will go away someday (fairly
soon I think).
There is some ancient code in the backend for storing big arrays
out-of-line as Large Objects, which'd eliminate the tuple-length issue.
But I think that code isn't getting compiled now, much less tested
or used regularly. So it's most likely broken.
The real problem with arrays in Postgres is that they need a lot of
work put into them, and no one has stepped up to the plate to do it.
Fixing the LOARRAY code, allowing NULL array elements, adding functions
to do searches within arrays, etc, etc, are all things that could and
should get done, but they're not high on the priority list of any active
developer. Arrays need to get adopted by somebody.
regards, tom lane
seems to me it is another example of sql86/92 issue: there is no standard regard to those object-relational feature. Informix is our relative and of course the best (and we are going to be better :-), but Orocle's array has maxim length and unfortunately they are the most powerful. and everybody, at least ambitious ones, have a snob inside (and of course have jargon excuses for that (I'm :-). That is the reason to be careful/cautious about any non-sql86/92 feature and this attitude is good for all oss. Actually, I guess, that is at least one of the reason why it is not at the top priority: let's make the sql2 first, sql3, if she will come, can wait. On Sat, 4 Dec 1999, Tom Lane wrote: > "John M. Flinchbaugh" <glynis@butterfly.hjsoft.com> writes: > > On Sat, 4 Dec 1999, Stephen Davies wrote: > >> Arrays do have a role in life: even in a "proper relational" world. > > > sorry, that is incorrect. in a proper system, you don't run out of > > ``slots''. say you give each publication an array to hold 3 > > authors. what happens when that 4th author comes along, then a > > fifth? you always have a +1 problem. > > Actually, Postgres arrays are variable-size, so that argument loses much > of its force. AFAICT, even if you specify a particular size in the > table declaration, Postgres ignores it and makes each tuple's array just > big enough to hold whatever gets stored there. > > I have used arrays in some cases where they seemed to be just the right > thing and made the database design much cleaner than it would have been > otherwise. For example, in a futures-trading transaction database, > I made the fill price and contract count be arrays, so that I could > represent "split fill" orders (you to broker: "Buy 10 June S&P futures"; > broker to you: "OK, I got 6 at 1415.5 and 4 at 1415.6"). Normalizing > this problem into separate transaction and price tables with an > association table is obviously ridiculous; nor did I like the choice of > representing a split fill as two or more separate transaction entries. > That wastes space for duplicate storage of the other columns, and also > means that you have to work harder to group together several database > records as a single "logical trade". One record per trade with the > ability to store multiple fill prices is the right design here. > > Arrays have lots of problems though. For one, there's no clean way to > do something like "find all the records that have FOO in any element of > this array column". We could invent some operators that do that kind of > thing, but they're not there now. Also, you can't expect to put a > really large number of items in an array, because of the system's limit > on overall tuple length; but that limit will go away someday (fairly > soon I think). > > There is some ancient code in the backend for storing big arrays > out-of-line as Large Objects, which'd eliminate the tuple-length issue. > But I think that code isn't getting compiled now, much less tested > or used regularly. So it's most likely broken. > > The real problem with arrays in Postgres is that they need a lot of > work put into them, and no one has stepped up to the plate to do it. > Fixing the LOARRAY code, allowing NULL array elements, adding functions > to do searches within arrays, etc, etc, are all things that could and > should get done, but they're not high on the priority list of any active > developer. Arrays need to get adopted by somebody. > > regards, tom lane > > ************ >