Re: [SQL] Array fields in Postgresql...

[Achilleus Mantzios]

O kyrios Randolf Richardson, DevNet SysOp 29 egrapse stis Nov 17, 2003 :

Also intarray contrib package is a good learning place.

> > Can any one give me a link/tutorial for using arrays in databases..I
> > need Queries to access the arrays as well...
> 
>   A quick search in Google.Com yielded a number of results which included 
> the following:
> 
>   PostgreSQL Tutorial
>   http://www.eskimo.com/support/PostgreSQL/tutorial/
>   ("Arrays" are listed near the end of the page)
> 
>   In Google.Com, you can also use the following search string:
> 
>   +postgresql +arrays +tutorial
> 
> 

-- 
-Achilleus


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Arrays - a good idea?

[Achilleus Mantzios]

O kyrios Yasir Malik egrapse stis Nov 18, 2003 :

> Hello,
> I don't think there's any reason you should use arrays.  You can do
> everything using tables, and it probably would be easier to use tables
> instead.  Extracting information from arrays is also more
> difficult if you're using something like JDBC or the connectivity
> available in PHP.  I don't know the criteria of when arrays are necessary,
> but I cannot think of an example where arrays are absolutely necessary
> Regards,
> Yasir

PgSQL herself uses arrays in some occasions:

pg_class.relacl and
pg_constraint.conkey, pg_constraint.confkey are such cases.

I think arrays *should* be used when the array fits the model.
For instrance if we want to provide Tree hierarchy capability in a table
one way is to do it with the genealogical approache where
the parents of a node are represented as an array of ids.
(This plus the intarray package makes the performance great).

Also if we want to store polynomial furmulae, we can do that
by storing the coefficients in an float array.
We then can write a C function that calculates the value
of this polyonym given the formula (the array) and X.

In these situations we could use the relational model
but the performance would be prohibiting.

On the other hand, ofcourse, when the size of the child
'relation' is not bounded, we should use the pure relational approach.

As a conclusion, use arrays when

- The size is bounded (Not necessarilly limited)
- The array fits in the model (Intuitively)
- Performance is an issue

In other words:
- Dont be afraid of arrays
- Dont overdo it with arrays!

P.S.

PgSQL jdbc driver does a good job in reading arrays.
Unfortunately when storing arrays, the String must be programmatically
formatted.

> 
> On Tue, 18 Nov 2003, Paul Ganainm wrote:
> 
> > Date: Tue, 18 Nov 2003 22:05:00 -
> > From: Paul Ganainm <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Subject: [SQL] Arrays - a good idea?
> >
> >
> >
> > Hi all,
> >
> >
> > Even though PostgreSQL supports arrays, is it a good idea to use them? I
> > mean, they
> >
> > a) don't conform to the relational model
> >
> > and
> >
> > b) are not transportable
> >
> >
> > so if one is designing an app, should one use them?
> >
> > When should they not/never be used? What are the criteria for justifying
> > their use?
> >
> >
> > Paul...
> >
> >
> > --
> >
> > plinehan__AT__yahoo__DOT__com
> >
> > C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
> >
> > Please do not top-post.
> >
> >
> > ---(end of broadcast)---
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >   subscribe-nomail command to [EMAIL PROTECTED] so that your
> >   message can get through to the mailing list cleanly
> >
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
> 

-- 
-Achilleus


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

Re: [SQL] Need Help : Query problem

[Michael Glaesemann]

On Wednesday, November 19, 2003, at 10:26 AM, Abdul Wahab Dahalan wrote:
How do I write a query so that I can get a result as below
[ select only a record/s with same kk and kj but different pngk.
For example here I've 3 records with same kk=01,kj=01 but diff  
pngk=a,b,c
and 2 records with same kk=01,kj=02 but diff pngk=a,b]
I think you'll want to take a look at using GROUP BY





---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Expressional Indexes

[Randolf Richardson, DevNet SysOp 29]

>> For example, if I want to index on a date field but only have the index 
>> keep track of the most recent 30 days (and then create a secondary
>> index for all dates) so as to improve performance on more heavily
>> loaded systems. 
>> 
>> Am I understanding this new terminology correctly?  Thanks in advance. 
> 
> No, you could do the above using "partial indexes" but it wouldn't work
> very well in this case because the "last 30 days" keeps moving and you
> would have to keep redefining the index periodically.

For the application I will need to develop in the future, it would be 
okay for this index to hold data more than 30 days old, and then be 
redefined on a monthly basis along with regular database vacuuming (and 
other maintenance).

Could this be done with a "partial index" as follows?

CREATE INDEX my_index on my_table (create_date)
  WHERE (create_date > age(timestamp '30 days'));

If I've made any mistakes here, please don't hesitate to let me know 
because the age() function is new to me.

> It also wouldn't really help performance.

Really?  A smaller index would result in fewer comparisons behind-the-
scenes though, wouldn't it?

> Expression Indexes are just more powerful "functional indexes". In 7.3
> they could be used for indexing expressions like "lower(foo)". In 7.4
> they're more powerful and you can index expressions other than simple
> function calls. 
[sNip]

So an "Expression Index" could, for example, be used to sort alpha-
numeric data in a case-insensitive manner?  I just want to make sure I'm 
understanding this correctly.

Thanks.

-- 
Randolf Richardson - [EMAIL PROTECTED]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Arrays - a good idea?

[Yasir Malik]

Hello,
I don't think there's any reason you should use arrays.  You can do
everything using tables, and it probably would be easier to use tables
instead.  Extracting information from arrays is also more
difficult if you're using something like JDBC or the connectivity
available in PHP.  I don't know the criteria of when arrays are necessary,
but I cannot think of an example where arrays are absolutely necessary
Regards,
Yasir

On Tue, 18 Nov 2003, Paul Ganainm wrote:

> Date: Tue, 18 Nov 2003 22:05:00 -
> From: Paul Ganainm <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: [SQL] Arrays - a good idea?
>
>
>
> Hi all,
>
>
> Even though PostgreSQL supports arrays, is it a good idea to use them? I
> mean, they
>
> a) don't conform to the relational model
>
> and
>
> b) are not transportable
>
>
> so if one is designing an app, should one use them?
>
> When should they not/never be used? What are the criteria for justifying
> their use?
>
>
> Paul...
>
>
> --
>
> plinehan__AT__yahoo__DOT__com
>
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
>
> Please do not top-post.
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so that your
>   message can get through to the mailing list cleanly
>

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] SOLVED: Emulating 'connect by prior' using stored proc

[Randolf Richardson, DevNet SysOp 29]

[sNip]
> It's the same in this case. The tree building stuff only ever looks down
> so the level returned in the query results will start at 1 no matter
> where you enter your tree.  In our case we could enter the tree at
> 'node' 100 and get the tree below that but the function will start at 1
> because we only interested in the data below not the entry point and not
> where in the tree we entered.

Thanks.  That's excellent news!  =)

> As in the solution if you really want to start it at 5 then set that as
> the value of your second parameter.

Interesting.  Although I don't have an immediate need for this, I'm 
sure it could be useful when storing the results of multiple queries of this 
nature in a temporary table.

-- 
Randolf Richardson - [EMAIL PROTECTED]
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/

This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.


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

   http://archives.postgresql.org

[SQL] Addition and subtraction on BIT type

[Yasir Malik]

Hello,
Is there a way to do addition and subtraction on BIT types?  For example,
for
creat table test (a BIT(3));
insert into test values (B'101');

select a + 1 from test; fails

and select a::smallint + 1 from test; also fails.

In addition, is there a way to change the bit of a bit string?  For
example change a 1 to a 0 or vice versa.

Any suggestions?
Thanks,
Yasir

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

   http://archives.postgresql.org

Re: [SQL] Is there a more elegant way to write this query?...

[Terence Kearns]

Nick Fankhauser wrote:
Hi-

I'm suffering from a performance problem, but when I look at my query, I'm
not convinced that there isn't a better way to handle this in SQL. -So I'm
seeking advice here before I go to the performance list.
I have three tables- case, actor and actor_case_assignment.

As the names imply, actor_case_assignment contains records that assign an
actor to a case. Actors such as attorneys or judges may have many cases,
while the average actor (we hope) only has one.
What I'm trying to do is link these tables to get back a single row per
actor that shows the actor's name, the number of cases that actor is
assigned to, and if they only have one case, I want the number for that
case. This means I have to do some grouping to get the case count, but I'm
then forced to use an aggregate function like max on the other fields. I
hope there's a better way. Any suggestions?
Here's what I'm using right now:

  select
actor.actor_id,
max(actor.actor_full_name),
max(case_data.case_public_id),
max(case_data.case_id),
count(case_data.case_id) as case_count
  from
actor,
actor_case_assignment,
case_data
  where
actor.actor_full_name_uppercase like upper('martin%')
and actor.actor_id = actor_case_assignment.actor_id
and case_data.case_id = actor_case_assignment.case_id
  group by
actor.actor_id
  order by
max(actor.actor_full_name),
case_count desc,
  limit
1000;
Thanks!
-Nick
-
Nick Fankhauser
[EMAIL PROTECTED]  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
select
count(as.case_id) as case_count,
max(as.case_id) as max_case_id,
a.actor_full_name
from
actors a,
actor_case_assignment as
where
a.case_id = actor_case_assignment.case_id
and a.actor_full_name_uppercase like upper('martin%')
-- this field is(should be) indexed
group by
a.actor_full_name
order by
a.actor_full_name_uppercase -- this field is(should be) indexed
This query will not pick up any actors that a NOT assigned to a case, 
but you said in your criteria that all actors are assigned to at least 
one case. This query relys on that assumption and eliminates the expense 
of an outer join.

Also, you are always getting max_case_id but it is obvious that this 
will be the relevent case where case_count == 1 you can simply ignore 
max_case_id when case_count != 1

try running this query with explain to compare against the others.
Also, try adding an index to the actor_full_name_uppercase column since 
you are using it in an order by clause and searching on it. indexes will 
incur slight performance expenses when inserting and updating.

cheers.



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] Compare strings which resembles each other

[Gulshan Babajee]

Hy, I have a table containing some rows. Each row contains a field name
which is of type String.

For example:
in row1 string1 is "Adieu Poulet",
in row2 string2 is "Adieu a Poulet",
in row3 string3 is "Adiue Poulet",
in row4 string4 is "Adieu Pouleet", etc..
(That is each string resembles each other a bit, they are somewhat similar
to each other)

What type of query should I make so as it return me all these string. That
is I watn a query which return me strings which are similar or somewhat
similar to each other.

thanks in advance
gulshan

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

   http://archives.postgresql.org

Re: [SQL] Getting last insert value

[Yasir Malik]

Guillaume,
Thank you very much for your response.
Yasir

On Sat, 15 Nov 2003, Guillaume LELARGE wrote:

> Date: Sat, 15 Nov 2003 17:41:41 +
> From: Guillaume LELARGE <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: Re: [SQL] Getting last insert value
>
> Le Samedi 15 Novembre 2003 16:40, vous avez écrit :
> > Doing a "select currval() from my_table" after your insert should work.
> >
> Actually, this is
> select currval('my_sequence')
> Sorry about this.
>
> > For more details, see
> > http://www.postgresql.org/docs/7.3/interactive/functions-sequence.html
>
>
> --
> Guillaume
>   .
>
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match
>

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

   http://archives.postgresql.org

[SQL] Conversion question

[Graham Vickrage]

Apologies as this probably isn't really for this list but...

In postgresql you can execute a statement such as: 

SELECT 1 > 2;

And it would return 'f'

Does anyone know if you can do this in SQL Server as I have to do a
conversion of some prewritten SQL code.

MTIA,

Graham.




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Substrings by Regular Expression

[Jim Drummey]

Hello-

The 7.2 version distributed with RedHat 8.0 seems not
to work for substrings and POSIX regular expressions.

I think this should work

pds=>  select substring( str1 from ',' ) from address;
ERROR:  pg_atoi: error in ",": can't parse ","

Am I missing something?

TIA
J. Drummey
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Substrings by Regular Expression

[Stephan Szabo]

On Fri, 14 Nov 2003, Jim Drummey wrote:

> Hello-
>
> The 7.2 version distributed with RedHat 8.0 seems not
> to work for substrings and POSIX regular expressions.
>
> I think this should work
>
> pds=>  select substring( str1 from ',' ) from address;
> ERROR:  pg_atoi: error in ",": can't parse ","
>
> Am I missing something?

That functionality was added in 7.3 I believe.  You may wish
to upgrade.

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

   http://archives.postgresql.org

[SQL] fmgr_info: function 15238119: cache lookup failed

[Iain]

(B
(B
(BHi All,
(B 
(BI have a problem with a before update 
(Btrigger that I was wondering if someone might be able to give me some help 
(Bwith.
(B 
(BDB version is 7.2. Here is a cut down 
(Bversion of an already simple trigger and function that is returning the 
(Berror:
(B 
(B    fmgr_info: function 
(B15238119: cache lookup failed
(B 
(BCREATE FUNCTION f_hatyuu_detail( ) RETURNS 
(Bopaque  AS 'BEGINraise notice ''f_hatyuu_detail started  
(BTG_WHEN= %  TG_OP= %'',TG_WHEN, TG_OP;  if TG_WHEN 
(B<> ''BEFORE'' or TG_OP <> ''UPDATE'' then  return 
(Bnull; end if;
(B 
(Braise notice ''debug 3''; return 
(Bnew;raise notice ''debug 4'';
(BEND; ' LANGUAGE 
(B'plpgsql';
(B 
(Bdrop trigger TG_D_HATYUU_DETAIL_B on 
(BD_HATYUU_DETAIL;create trigger TG_D_HATYUU_DETAIL_B BEFORE update on 
(BD_HATYUU_DETAIL for each row execute procedure 
(Bf_hatyuu_detail();
(BWhen I do an UPDATE against the table, I 
(Bget the abovementioned error on the RETURN NEW; The debug 4 message is not 
(Bdisplayed. If I return NULL it operates as expected (0 rows updated). Did I miss 
(Bsomething here?
(B 
(Bregards
(BIain

Re: [SQL] fmgr_info: function 15238119: cache lookup failed

[Iain]

(B
(B
(BOoops! I fixed it. Somewhere along the 
(Bline the name of the trigger had changed, and there was an old, probably 
(Binvalidated trigger left on the table. Once I dropped that it was 
(BOK.
(B 
(BSorry for the inconvenience!
(B 
(BRegards
(BIain
(B
(B  - Original Message - 
(B  From: 
(B  Iain 
(B  To: [EMAIL PROTECTED] 
(B  Sent: Thursday, November 20, 2003 
(B  11:37 AM
(B  Subject: fmgr_info: function 
(B  15238119: cache lookup failed
(B  
(B  Hi All,
(B   
(B  I have a problem with a before update 
(B  trigger that I was wondering if someone might be able to give me some help 
(B  with.
(B   
(B  DB version is 7.2. Here is a cut 
(B  down version of an already simple trigger and function that is returning the 
(B  error:
(B   
(B      fmgr_info: function 
(B  15238119: cache lookup failed
(B   
(B  CREATE FUNCTION f_hatyuu_detail( ) 
(B  RETURNS opaque  AS 'BEGINraise notice ''f_hatyuu_detail 
(B  started  TG_WHEN= %  TG_OP= %'',TG_WHEN, 
(B  TG_OP;  if TG_WHEN <> ''BEFORE'' or TG_OP <> 
(B  ''UPDATE'' then  return null; end if;
(B   
(B  raise notice ''debug 
(B  3''; return new;raise notice ''debug 4'';
(B  END; ' LANGUAGE 
(B  'plpgsql';
(B   
(B  drop trigger TG_D_HATYUU_DETAIL_B on 
(B  D_HATYUU_DETAIL;create trigger TG_D_HATYUU_DETAIL_B BEFORE update on 
(B  D_HATYUU_DETAIL for each row execute procedure 
(B  f_hatyuu_detail();
(B  When I do an UPDATE against the table, I 
(B  get the abovementioned error on the RETURN NEW; The debug 4 message is not 
(B  displayed. If I return NULL it operates as expected (0 rows updated). Did I 
(B  miss something here?
(B   
(B  regards
(B  Iain

Re: [SQL] FATAL 2: PageIndexTupleDelete

[Tom Lane]

"cristi" <[EMAIL PROTECTED]> writes:
> FATAL 2: PageIndexTupleDelete: corrupted page pointers: lower =3D 12289, up=
> per =3D
> 1, special =3D 60672

> Why do I have this error message and how can I fix it?

This is a data-corruption problem.  Have you had any system crashes or
other odd behavior lately?

As far as recovering, you can probably fix the broken index with
REINDEX, if the corruption doesn't extend further than the index that's
being complained of.  Otherwise you might have to resort to a dump and
reload.

You should also think about updating your Postgres installation.  You
didn't say what version you're running (naughty naughty, that's required
info in any trouble report) but I can tell from the spelling of the
error message that it's not a current release.

regards, tom lane

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

   http://archives.postgresql.org

[SQL] current_date timezone documentation suggestion

[Erik Thiele]

hi


when i do

select current_date;

around midnight, when does the date switch? does it switch according
local time zone, or does it switch by GMT ? i did not find docs
about this near the docs of "current_date" or anywhere else.

please do add it to the documentation.


thanks for good work!
erik

-- 
Erik Thiele

---(end of broadcast)---
TIP 8: explain analyze is your friend