Re: [SQL] Finding multiple events of the same kind

[Leif B. Kristensen]

My question and your answer have now become part of a blog entry at my 
site: . Thank you again.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE

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

   http://www.postgresql.org/docs/faq

Re: [SQL] COPY to table with array columns (Longish)

[Aaron Bono]

So how about creating a sys table too:

SYS
  sys_id

ZKCOST
  sys_id,
  zkcost_id,
  zkcost_value

and

ZPRECMPL
  sys_id,
  zprecmpl_id,
  zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute
  attribute_id (PK),
  attribute_name

character
  character_id (PK),
  character_name

character_attribute
  character_attribute_id (PK),
  character_id (FK),
  attribute_id (FK),
  value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:

Not quite... ZKCOST and ZPRECMPL are two completely different things. They
have no relation except they're both stored in the SYS table in D3.

If we put it in a tree:
SYS
 |
 +- ZKCOST
 | \- 
 |
 +- ZPRECMPL
 | +- 
 | +- 
 | +- 
 | \- 

or table:
SYS
+---+-+-+-+-+
| ZKCOST|  | | | |
| ZPRECMPL  |  |  |  |  |
+---+-+-+-+-+


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] COPY to table with array columns (Longish)

[operationsengineer1]

> Not quite... ZKCOST and ZPRECMPL are two completely
> different things. They
> have no relation except they're both stored in the
> SYS table in D3.
> 
> If we put it in a tree:
>   SYS
>|
>+- ZKCOST
>| \- 
>|
>+- ZPRECMPL
>| +- 
>| +- 
>| +- 
>| \- 
> 
> or table:
>   SYS
> 
>
+---+-+-+-+-+
>   | ZKCOST|  | | |
> |
>   | ZPRECMPL  |  |  |  | 
> |
> 
>
+---+-+-+-+-+
> 
> So other than a variable-element array, the only
> other way would be to
> create a table with a column count equal to or
> greater than the maximum
> amount of values (call that value 'Y') that any sys
> item holds then if a
> particular record (eg, ZKCOST) has less values than
> Y, fill the rest of the
> columns with blanks (as above).
> 
> That's what I've done at the moment, but only for 9
> columns, so anything
> over 9 fields will be truncated past and including
> field 10:
>   wbau=# \d sys
>   Table "public.sys"
>Column | Type | Modifiers
>   +--+---
>a0 | text | not null
>a1 | text |
>a2 | text |
>a3 | text |
>a4 | text |
>a5 | text |
>a6 | text |
>a7 | text |
>a8 | text |
>a9 | text |
>   Indexes:
>   "id" PRIMARY KEY, btree (a0)
> 
> a0 = primary key - eg, ZPRECMPL or ZKCOST
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> On Behalf Of Aaron Bono
> Sent: Tuesday, 13 June 2006 2:12 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] COPY to table with array columns
> (Longish)
> 
> I think two tables should suffice: ZKCOST and
> ZPRECMPL.
> 
> So you would have
> 
> ZKCOST
> zkcost_id,
> zkcost_value
> 
> and
> 
> ZPRECMPL
> zkcost_id,
> zprecmpl_id,
> zprecmpl_value
> 
> where zkcost_id is the primary key for ZKCOST and
> zkcost_id,
> zprecmpl_id together are the primary key for
> ZPRECMPL and zkcost_id is
> a foreign key from ZPRECMPL to ZKCOST.
> 
> That will work won't it?
> 
> -Aaron
> 
> On 6/12/06, Phillip Smith
> <[EMAIL PROTECTED]> wrote:
> 
> > So you're suggesting creating a child table for
> each SYS record? Ie, a
> table called "ZPRECMPL" etc?

if the data is unrelated, then the data should be
separated (in a perfect world).

can you convert into the following form:

TABLE_ZKCOST
zkcost_id
zkcost_value

TABLE_ZPRECMPL
zprecmpl_id

TABLE_ZPRECMPL_DATA
zprecmpl_data_id
zprecmpl_id
zprecmpl_value


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

   http://www.postgresql.org/docs/faq

Re: [SQL] COPY to table with array columns (Longish)

[operationsengineer1]

> > Not quite... ZKCOST and ZPRECMPL are two
> completely
> > different things. They
> > have no relation except they're both stored in the
> > SYS table in D3.
> > 
> > If we put it in a tree:
> > SYS
> >  |
> >  +- ZKCOST
> >  | \- 
> >  |
> >  +- ZPRECMPL
> >  | +- 
> >  | +- 
> >  | +- 
> >  | \- 
> > 
> > or table:
> > SYS
> > 
> >
>
+---+-+-+-+-+
> > | ZKCOST|  | | |  
>  
> > |
> > | ZPRECMPL  |  |  |  |
> 
> > |
> > 
> >
>
+---+-+-+-+-+
> > 
> > So other than a variable-element array, the only
> > other way would be to
> > create a table with a column count equal to or
> > greater than the maximum
> > amount of values (call that value 'Y') that any
> sys
> > item holds then if a
> > particular record (eg, ZKCOST) has less values
> than
> > Y, fill the rest of the
> > columns with blanks (as above).
> > 
> > That's what I've done at the moment, but only for
> 9
> > columns, so anything
> > over 9 fields will be truncated past and including
> > field 10:
> > wbau=# \d sys
> > Table "public.sys"
> >  Column | Type | Modifiers
> > +--+---
> >  a0 | text | not null
> >  a1 | text |
> >  a2 | text |
> >  a3 | text |
> >  a4 | text |
> >  a5 | text |
> >  a6 | text |
> >  a7 | text |
> >  a8 | text |
> >  a9 | text |
> > Indexes:
> > "id" PRIMARY KEY, btree (a0)
> > 
> > a0 = primary key - eg, ZPRECMPL or ZKCOST
> > 
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]
> > On Behalf Of Aaron Bono
> > Sent: Tuesday, 13 June 2006 2:12 PM
> > To: pgsql-sql@postgresql.org
> > Subject: Re: [SQL] COPY to table with array
> columns
> > (Longish)
> > 
> > I think two tables should suffice: ZKCOST and
> > ZPRECMPL.
> > 
> > So you would have
> > 
> > ZKCOST
> > zkcost_id,
> > zkcost_value
> > 
> > and
> > 
> > ZPRECMPL
> > zkcost_id,
> > zprecmpl_id,
> > zprecmpl_value
> > 
> > where zkcost_id is the primary key for ZKCOST and
> > zkcost_id,
> > zprecmpl_id together are the primary key for
> > ZPRECMPL and zkcost_id is
> > a foreign key from ZPRECMPL to ZKCOST.
> > 
> > That will work won't it?
> > 
> > -Aaron
> > 
> > On 6/12/06, Phillip Smith
> > <[EMAIL PROTECTED]> wrote:
> > 
> > > So you're suggesting creating a child table for
> > each SYS record? Ie, a
> > table called "ZPRECMPL" etc?
> 
> if the data is unrelated, then the data should be
> separated (in a perfect world).
> 
> can you convert into the following form:
> 
> TABLE_ZKCOST
> zkcost_id
> zkcost_value
> 
> TABLE_ZPRECMPL
> zprecmpl_id
> 
> TABLE_ZPRECMPL_DATA
> zprecmpl_data_id
> zprecmpl_id
> zprecmpl_value

just read Aaron's latest post - it is best to do as he
suggested and link back to the sys table.

TABLE_SYS
  sys_id

TABLE_ZKCOST
  zkcost_id
  sys_id
  zkcost_value

TABLE_ZPRECMPL
  zprecmpl_id
  sys_id

TABLE_ZPRECMPL_DATA
  zprecmpl_data_id
  zprecmpl_id
  zprecmpl_value

the difference, and i'm not sure it is significant, is
that the above links the ZPRECMPL_DATA back to
ZPRECMPL, then it links ZPRECMPL back to SYS.

that's how my head wraps around this problem and i
think it should work alright as long as you can
massage the data into this format.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 1: 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

[SQL] Efficient Searching of Large Text Fields

[Aaron Bono]

In another post on a different topic, Rod Taylor said the following:

"A\tcat in   the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', 'hat'].

This got me thinking.  I have a discussion forum for gamers and want
to provide searching capabilities so the user can type in a phrase
like "magical bow" and get all posts, sorted by relevance that contain
these words.

My questions are:
1. Will storing the posts in an ARRAY help improve performance of
these searches?  If so, by how much?
2. What functions or libraries are available to make such searching
easy to implement well?
3. What is the best way to sort by relevance?

Thanks,
Aaron Bono

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

  http://www.postgresql.org/docs/faq

Re: [SQL] Efficient Searching of Large Text Fields

[Rod Taylor]

On Tue, 2006-06-13 at 15:30 -0500, Aaron Bono wrote:
> In another post on a different topic, Rod Taylor said the following:
> 
> "A\tcat in   the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', 
> 'hat'].
> 
> This got me thinking.  I have a discussion forum for gamers and want
> to provide searching capabilities so the user can type in a phrase
> like "magical bow" and get all posts, sorted by relevance that contain
> these words.
> 
> My questions are:
> 1. Will storing the posts in an ARRAY help improve performance of
> these searches?  If so, by how much?

It won't help at all. Fast partial matches against arrays is nearly
impossible. You might take a look at tsearch though.

-- 


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

   http://www.postgresql.org/docs/faq

Re: [SQL] Efficient Searching of Large Text Fields

[PFC]

2. What functions or libraries are available to make such searching
easy to implement well?


	the tsearch2 module does that, and has a substantial advantage over a  
solution you might reimplement : it's already done (and it works). Try  
it...


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

Re: [SQL] Efficient Searching of Large Text Fields

[Aaron Bono]

I will look that tsearch (at .  It appears their 8.1.x version is
still in development and I use PostgreSQL 8.1.3 but it is worth trying
- I'm not in a hurry for that feature anyway.

I also looked at PHPBB a little - it appears their database stores
words but the code is so difficult to dig through I was not sure about
their implementation or even what they used it for.  Would it be worth
the work to save the text into a separate searchable table that kept
individual words and word counts or would that be more work and eat up
more space than it is worth?  You could actually index the words that
way and get much quicker searches.  Then again, as I read through
tsearch, it may make this approach unnecessary...

I have also seen what looks like people using search results tables
that, after a search is performed, save a list of the results.  For
example, if I were doing a search of a forum, I could save the search
in a table like this:

forum_topic
  forum_topic_id (PK)
  forum_topic_name
  etc...

forum_topic_search
  forum_topic_search_id (PK)
  forum_topic_search_dt
  forum_topic_search_desc

forum_topic_search_results
  forum_topic_search_results_id (PK)
  forum_topic_search_id (FK)
  sort_index (int to tell us the order the results are returned in)
  forum_topic_id (FK)

This way you can allow users to page through the results without
having to constantly research or cache the results somewhere in
memory.

Has anyone tried an approach like this?

When do you clean these search tables out?  They could get quite large
after a while.

Thanks!
Aaron

On 6/13/06, Rod Taylor <[EMAIL PROTECTED]> wrote:

It won't help at all. Fast partial matches against arrays is nearly
impossible. You might take a look at tsearch though.


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

  http://www.postgresql.org/docs/faq

[SQL] Error with "limit" clause

[Rommel the iCeMAn]

Hi List,
 
I've been trying the following query from VB.NET: select * from my_table
order by column1 limit 1. This query works flawlessly when I use the
pgAdminIII query tool, but when I run this query from VB.NET, I get the
following error: syntax error at or near "limit". Any ideas as to the cause
of this error? Seems to me this error is specific to the PostgreSQL .NET
providers, what do you think?

If it's of any help, here's my connection string: database=test;User
ID=iceman;Password={suppressed};Server=my_server;Port=5432;Timeout=10

Thanks in advance,
Rommel Edwards.


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

   http://archives.postgresql.org

Re: [SQL] COPY to table with array columns (Longish)

[Phillip Smith]

Thanks Aaron - There are currently 8175 records in my SYS file - I might
need to go with this approach but be selective about which items I export so
I don't end up with 8000 tables related to SYS! There's probably a lot of
 in there that doesn't actually need to be exported.

Thanks again,
-p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 1:05 AM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

So how about creating a sys table too:

SYS
   sys_id

ZKCOST
   sys_id,
   zkcost_id,
   zkcost_value

and

ZPRECMPL
   sys_id,
   zprecmpl_id,
   zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute
   attribute_id (PK),
   attribute_name

character
   character_id (PK),
   character_name

character_attribute
   character_attribute_id (PK),
   character_id (FK),
   attribute_id (FK),
   value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
> Not quite... ZKCOST and ZPRECMPL are two completely different things. They
> have no relation except they're both stored in the SYS table in D3.
>
> If we put it in a tree:
> SYS
>  |
>  +- ZKCOST
>  | \- 
>  |
>  +- ZPRECMPL
>  | +- 
>  | +- 
>  | +- 
>  | \- 
>
> or table:
> SYS
> +---+-+-+-+-+
> | ZKCOST|  | | | |
> | ZPRECMPL  |  |  |  |  |
> +---+-+-+-+-+

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: 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] COPY to table with array columns (Longish)

[Aaron Bono]

I guess I still don't understand...

If you take the approach operationsengineer1 and I suggested, you
should only need 3 or 4 tables regardless of the number of SYS file
records.

Good luck with your implementation.

-Aaron

On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:

Thanks Aaron - There are currently 8175 records in my SYS file - I might
need to go with this approach but be selective about which items I export so
I don't end up with 8000 tables related to SYS! There's probably a lot of
 in there that doesn't actually need to be exported.

Thanks again,
-p


---(end of broadcast)---
TIP 1: 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] COPY to table with array columns (Longish)

[Phillip Smith]

No, it was me that didn't understand!! But I do now - quite simple really!

Cheers,
-p

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Aaron Bono
Sent: Wednesday, 14 June 2006 10:41 AM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] COPY to table with array columns (Longish)

I guess I still don't understand...

If you take the approach operationsengineer1 and I suggested, you
should only need 3 or 4 tables regardless of the number of SYS file
records.

Good luck with your implementation.

-Aaron

On 6/13/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
> Thanks Aaron - There are currently 8175 records in my SYS file - I might
> need to go with this approach but be selective about which items I export
so
> I don't end up with 8000 tables related to SYS! There's probably a lot of
>  in there that doesn't actually need to be exported.
>
> Thanks again,
> -p

---(end of broadcast)---
TIP 1: 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


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

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

   http://www.postgresql.org/docs/faq

Re: [SQL] Error with "limit" clause

[Tom Lane]

"Rommel the iCeMAn" <[EMAIL PROTECTED]> writes:
> I've been trying the following query from VB.NET: select * from my_table
> order by column1 limit 1. This query works flawlessly when I use the
> pgAdminIII query tool, but when I run this query from VB.NET, I get the
> following error: syntax error at or near "limit". Any ideas as to the cause
> of this error? Seems to me this error is specific to the PostgreSQL .NET
> providers, what do you think?

Could VB be rewriting the query behind your back?  Turn on log_statement
logging, and look in the postmaster log to see what's really being sent
to the backend ...

regards, tom lane

---(end of broadcast)---
TIP 1: 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

[SQL] Good examples of calling slony stored procedures

[Mark Adan]

Hi

I was wondering if where can I find some examples of calling the slony
stored procedures instead of using slonik?  I want to be able to for
example add a table into slony.  Thanks

Mark Adan

---(end of broadcast)---
TIP 1: 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] Error with "limit" clause

[Rommel the iCeMAn]

> Could VB be rewriting the query behind your back?  Turn on log_statement
logging, and look in the postmaster log to see what's really being sent to
the > backend ...

Exactly!

This was an oversight on my part tho. I had used the following command:

MyDataReader = MyCommand.ExecuteReader(CommandBehavior.SingleRow)

By passing the "CommandBehavior.SingleRow" parameter VB inserted the "limit
1" clause unknown to me. The solution was easy, either explicitly state
"limit 1" in my SQL query OR use the CommandBehavior.SingleRow parameter -
NOT BOTH :-)

Thanks for the input,
Rommel Edwards.


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

Re: [SQL] Good examples of calling slony stored procedures

[Tom Lane]

"Mark Adan" <[EMAIL PROTECTED]> writes:
> I was wondering if where can I find some examples of calling the slony
> stored procedures instead of using slonik?  I want to be able to for
> example add a table into slony.  Thanks

This is likely the wrong bunch to ask --- there's a slony project
mailing list where the right people to ask hang out.  Don't have
the address at hand.

regards, tom lane

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

[SQL] to_char with time

[chester c young]

this does not work: select to_char(current_time,'HH24:MI')what am I missing?  is it possible to format a time column in a select?thanks,stumped, aka, chester __Do You Yahoo!?Tired of spam?  Yahoo! Mail has the best spam protection around http://mail.yahoo.com 

Re: [SQL] to_char with time

[Aaron Bono]

Try select to_char(now(),'HH24:MI');

-Aaron

On 6/13/06, chester c young <[EMAIL PROTECTED]> wrote:

this does not work: select to_char(current_time,'HH24:MI')

what am I missing?  is it possible to format a time column in a select?

thanks,
stumped, aka, chester


---(end of broadcast)---
TIP 1: 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] to_char with time

[Phillip Smith]

Try using current_timestamp instead of current_time.
This works for me in a SELECT:

to_char(current_timestamp + '1 MONTH AGO', 'YYMM') as ‘reference’

 

Cheers,

-p

 

-Original Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of chester
c young
Sent: Wednesday,
 14 June 2006 2:10
 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] to_char with time

 

this does not work:
select to_char(current_time,'HH24:MI')

what am I missing?  is it possible to format a time column in a select?

thanks,
stumped, aka, chester

 __
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 





***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments