[SQL] query

2006-02-07 Thread superboy143 (sent by Nabble.com) 

Hello,

I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything.

View this message in context: query
Sent from the PostgreSQL - sql forum at Nabble.com.

Re: [SQL] query

2006-02-07 Thread Richard Huxton 

superboy143 (sent by Nabble.com) wrote:

Hello,

I have a table in which I have a field with format like 100101. It
has many values like 100101, 100102, 100103, 100201, 100202, 100301.


OK - so they look like numbers but aren't.


I have to write a query such that I have to get only distinct values
such that they contain only the substring I need. If I give 10 as
substring, then it should return only 100101 or 100102 but not both
i.e if the last two characters are not same it should not return both
of them. 


The statement of the rule and the example contradict each other. Surely 
100101 and 100102 are distinct values. Surely they contain "10" as a 
substring. So according to your rule the should be returned.


> It should return only values starting with 10 the middle two

values should be distinct and the last two characters may be
anything. 


This seems to agree with your example and not your rule.

So:
1. You don't want distinct values of your field, you want distinct 
substrings of your field?
2. You don't want to match a substring, you want to match the start of 
the string?

3. You want only one of 100101 or 100102 but you don't care which?

Is this correct?

By the way - it looks to me like you are trying to store multiple values 
in one column. If you split the values into their own columns I'd guess 
your query would be much easier.


--
  Richard Huxton
  Archonet Ltd

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

[SQL] passing array to database function

2006-02-07 Thread padmanabha konkodi 

  hi everybody,

i want to pass Integer array to database function from java precompiled statement. 

java gives java.sql.Array interface but i dont know which implementing class i have to use in postgre

regards
ptrashantha.k.p

Re: [SQL] passing array to database function

2006-02-07 Thread Richard Huxton 

padmanabha konkodi wrote:

 hi everybody,

i want to pass Integer array to database function from java
precompiled statement.

java gives java.sql.Array interface but i dont know which
implementing class i have to use in postgre


I don't really "do" java, but have you tried an array of integers for 
your PostgreSQL function? What happened?


--
  Richard Huxton
  Archonet Ltd


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

Re: [SQL] passing array to database function

2006-02-07 Thread Achilleus Mantzios 
O Richard Huxton έγραψε στις Feb 7, 2006 :

> padmanabha konkodi wrote:
> > Β hi everybody,
> > 
> > i want to pass Integer array to database function from java
> > precompiled statement.
> > 
> > java gives java.sql.Array interface but i dont know which
> > implementing class i have to use in postgre
> 
> I don't really "do" java, but have you tried an array of integers for 
> your PostgreSQL function? What happened?

The correct list is -jdbc.
Theoritically you would have to write your own implementation
of java.sql.Array.
Practically build a String with the contents you want
e.g.
'{1,2,3,4}' 
for a int[] array, and your done.

> 
> 

-- 
-Achilleus


---(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] problem referencing an attrib which is not unique

2006-02-07 Thread Vic Rowan 
-- Forwarded message --From: Vic Rowan <[EMAIL PROTECTED]>Date: Feb 7, 2006 2:31 PM
Subject: problem referencing an attrib which is not uniqueTo: [email protected] everybody,I need some thing like this below for an application which stores log messages in multiple languages. The table 'event_msg' stores predefined messages in multiple languages which can be populated with place holder values from the application. (These of course are language independent). So, the event_id associates these predefined messages from both the tables so that displaying a log message is as simple as looking up the event_id from the 'logs' table and similarly looking up the event_id and language from the 'event_msg' table to retreive the predefined_msg with the correct language - the application determines the lang from a settings file - and combining them to display the log message.
CREATE TABLE event_msg (  event_id varchar(30) NOT NULL,  language char(2) NOT NULL,  predefined_msg varchar(250) NOT NULL,  PRIMARY KEY (event_id, language));CREATE TABLE logs (  id int NOT NULL,
  event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL,    placeholder_values varchar(250),  priority varchar(20) NOT NULL,  timestamp Date NOT NULL,  primary key (id));The problem I am facing is the event_id from logs is not able to reference event_id from event_msg as its not unique. 
There are as many entries for each event_id as there are languages supported in the 'event_msg' table. I would be glad if somebody could suggest some work around here to the above structure. Or alternately do I need to alter the table structure altogether and if so what is the better way of doing this? 
Thanks in advance for any help offered.Cheers,Vic Rowan.

Re: [SQL] problem referencing an attrib which is not unique

2006-02-07 Thread Richard Huxton 

Vic Rowan wrote:

CREATE TABLE event_msg (
  event_id varchar(30) NOT NULL,
  language char(2) NOT NULL,
  predefined_msg varchar(250) NOT NULL,
  PRIMARY KEY (event_id, language)
);

CREATE TABLE logs (
  id int NOT NULL,
  event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL,
  placeholder_values varchar(250),
  priority varchar(20) NOT NULL,
  timestamp Date NOT NULL,
  primary key (id)
);


The problem I am facing is the event_id from logs is not able to reference
event_id from event_msg as its not unique.


The problem is with your event_msg table. You have two concepts - 
"event" and "event_msg_localised" but only one table.


The logs table should reference "event" (which has one row for each 
event and a unique event_id). The "event_msg_localised" table also 
references "event".


The event table might only contain the event_id primary-key, but I'd 
probably put a description in their for my own use (perhaps saying what 
values to expect).


HTH
--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] problem referencing an attrib which is not unique

2006-02-07 Thread Patrick JACQUOT 

Vic Rowan wrote:




-- Forwarded message --
From: *Vic Rowan* <[EMAIL PROTECTED] >
Date: Feb 7, 2006 2:31 PM
Subject: problem referencing an attrib which is not unique
To: [email protected] 


hello everybody,

I need some thing like this below for an application which stores log 
messages in multiple languages. The table 'event_msg' stores 
predefined messages in multiple languages which can be populated with 
place holder values from the application. (These of course are 
language independent). So, the event_id associates these predefined 
messages from both the tables so that displaying a log message is as 
simple as looking up the event_id from the 'logs' table and similarly 
looking up the event_id and language from the 'event_msg' table to 
retreive the predefined_msg with the correct language - the 
application determines the lang from a settings file - and combining 
them to display the log message.


CREATE TABLE event_msg (
  event_id varchar(30) NOT NULL,
  language char(2) NOT NULL,
  predefined_msg varchar(250) NOT NULL,
  PRIMARY KEY (event_id, language)
);

CREATE TABLE logs (
  id int NOT NULL,
  event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL, 
  placeholder_values varchar(250),

  priority varchar(20) NOT NULL,
  timestamp Date NOT NULL,
  primary key (id)
);


The problem I am facing is the event_id from logs is not able to 
reference event_id from event_msg as its not unique.
There are as many entries for each event_id as there are languages 
supported in the 'event_msg' table.
I would be glad if somebody could suggest some work around here to the 
above structure. Or alternately do I need to alter the table structure 
altogether and if so what is the better way of doing this?


Thanks in advance for any help offered.

Cheers,
Vic Rowan.


I think you need three tables

One to list the allowable events, which will be used as reference
CREATE TABLE eventlist (
 event_id varchar(30) PRIMARY-KEY
);

One to give the messages translations
CREATE TABLE messagetranslations(
event-id varchar(30) references eventlist (event_id) NOT NULL
language char(2) not null
event-translation varchar(250)
PRIMARY KEY (event_id, language)
);

and your log table
CREATE TABLE logs (
 id int NOT NULL,
 event_id varchar(30) REFERENCES eventlist (event_id) NOT NULL, 
 placeholder_values varchar(250),

 priority varchar(20) NOT NULL,
 timestamp Date NOT NULL,
 primary key (id)
);

btw,  event-id could be just an integer. If, as I  understand, event-id 
is so large a string,

it's probably because it contains the english name of the event.
Just put it in an occurrence of messagetranslation, with language = 'EN'
other thing : with only 2 chars as language id, how do you distinguish 
EN-US and EN-UK

(or whatever id  the latter can have assigned)?.

hth
P. Jacquot


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

Re: [SQL] problem referencing an attrib which is not unique

2006-02-07 Thread Vic Rowan 
Thanks a lot Patrick and Richard for the help! Especially about the details that I hadnt even asked for, like 2 chars for language and I guess it makes very much sense in considering these for situations like EN-US or EN-UK. It was really insightful.
On 2/7/06, Patrick JACQUOT <[EMAIL PROTECTED]> wrote:
Vic Rowan wrote:>>> -- Forwarded message --> From: *Vic Rowan* <[EMAIL PROTECTED] 
[EMAIL PROTECTED]>>> Date: Feb 7, 2006 2:31 PM> Subject: problem referencing an attrib which is not unique> To: [email protected] [email protected] hello everybody,>> I need some thing like this below for an application which stores log> messages in multiple languages. The table 'event_msg' stores
> predefined messages in multiple languages which can be populated with> place holder values from the application. (These of course are> language independent). So, the event_id associates these predefined
> messages from both the tables so that displaying a log message is as> simple as looking up the event_id from the 'logs' table and similarly> looking up the event_id and language from the 'event_msg' table to
> retreive the predefined_msg with the correct language - the> application determines the lang from a settings file - and combining> them to display the log message.>> CREATE TABLE event_msg (
>   event_id varchar(30) NOT NULL,>   language char(2) NOT NULL,>   predefined_msg varchar(250) NOT NULL,>   PRIMARY KEY (event_id, language)> );>> CREATE TABLE logs (>   id int NOT NULL,
>   event_id varchar(30) REFERENCES event_msg (event_id) NOT NULL,>   placeholder_values varchar(250),>   priority varchar(20) NOT NULL,>   timestamp Date NOT NULL,>   primary key (id)
> );>>> The problem I am facing is the event_id from logs is not able to> reference event_id from event_msg as its not unique.> There are as many entries for each event_id as there are languages
> supported in the 'event_msg' table.> I would be glad if somebody could suggest some work around here to the> above structure. Or alternately do I need to alter the table structure> altogether and if so what is the better way of doing this?
>> Thanks in advance for any help offered.>> Cheers,> Vic Rowan.>I think you need three tables One to list the allowable events, which will be used as referenceCREATE TABLE eventlist (
  event_id varchar(30) PRIMARY-KEY);One to give the messages translationsCREATE TABLE messagetranslations( event-id varchar(30) references eventlist (event_id) NOT NULL language char(2) not null
 event-translation varchar(250) PRIMARY KEY (event_id, language));and your log tableCREATE TABLE logs (  id int NOT NULL,  event_id varchar(30) REFERENCES eventlist (event_id) NOT NULL,
  placeholder_values varchar(250),  priority varchar(20) NOT NULL,  timestamp Date NOT NULL,  primary key (id));btw,  event-id could be just an integer. If, as I  understand, event-idis so large a string,
it's probably because it contains the english name of the event.Just put it in an occurrence of messagetranslation, with language = 'EN'other thing : with only 2 chars as language id, how do you distinguish
EN-US and EN-UK(or whatever id  the latter can have assigned)?.hthP. Jacquot---(end of broadcast)---TIP 6: explain analyze is your friend

[SQL] Filtering data based on timestamp

2006-02-07 Thread Panos Kassianidis 

Hello everybody,

I am building a data logging application and I am facing the following
problem. I have a table which holds timestamp-value pairs for a recorded
variable. I would like to be able to select a range of values between 
a starting

and an ending timestamp but also specify an interval over which values
should be averaged. For example let's say I have 180 values recorded 
over the last 3 hours

(1 per minute)
and I would like to retrieve values recorded in the last 2 hours but
with an interval of 5 minutes, which means that my query should return
one tuple for every 5 tuples in the table and the value of this tuple
should be an average of 5 values.
Does anyone have any clue how I can make this query? I have though
about somehow grouping the tuples with group by and use the AVG
function but the tuples don't have anything in common to be grouped by.

Thank you very much in advance.

Panos


---(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

Re: [SQL] Filtering data based on timestamp

2006-02-07 Thread Richard Huxton 

Panos Kassianidis wrote:

and I would like to retrieve values recorded in the last 2 hours but
with an interval of 5 minutes, which means that my query should return
one tuple for every 5 tuples in the table and the value of this tuple
should be an average of 5 values.
Does anyone have any clue how I can make this query? I have though
about somehow grouping the tuples with group by and use the AVG
function but the tuples don't have anything in common to be grouped by.


You'll need to write an enhanced version of date_trunc() that can group 
timestamps into arbitrary units, e.g.

  my_date_trunc('... 12:34', 'm', 5) = '...12:30'
Then you can group by your new value.

--
  Richard Huxton
  Archonet Ltd

---(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

Re: [SQL] executing dynamic commands

2006-02-07 Thread codeWarrior 
In your function why not create a temporary table then use that for your 
processing ?

CREATE TEMPRORARY TABLE tabledest AS (SELECT * FROM tblsrc WHERE condition);


<[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Hi,
>
> I user PostgreSQl 8.0.4 on Win2003 Server and write a function to copy 
> rows from one table into another table with the same column definition.
> My first approach was to use something like:
>
> query_value :=  'INSERT INTO ' || tabledest || ' SELECT * FROM ' || 
> tablesrc;
> EXECUTE query_value;
>
> This only works if the column definition AND the order between source and 
> destination is the same !
> In my case I have always the same column definitions but they are not in 
> the same order between source and destination table.
> What I tryed then is to loop through the column definition of the source 
> and query the sourcetable for the value. For that I have to execut a query 
> with dynamic tablename and dynamic columname to generate two stings one 
> with the columndefinitin and one with the columnvalues to exececute 
> something like: INSERT INTO tabelfoo (columndefinitinstring) VALUES 
> (columnvaluesstring)
>
> see snip of function:
>
> fieldvalues RECORD;
> output  RECORD;
> insertvalues VARCHAR;
> fieldname VARCHAR;
>
>
> -- Get Attribute List from Table and write it to output
> -- Read Values of Fieldname from source
> query_value :=  'select * from ' || tablesrc ;
>
> FOR fieldvalues IN EXECUTE query_value LOOP
>
>  FOR output IN SELECT a.attnum,
>a.attname AS field,
>FROM
>  pg_class c,  pg_attribute a,  pg_type t
>WHERE
>  c.relname = tablesrc  AND
>  a.attnum > 0  AND
>  a.attrelid = c.oid  AND
>  a.atttypid = t.oid
>ORDER BY a.attnum LOOP
>
> -- Read Field Name from Out Table
> fieldname := output.field;
>
> -- Write Field Name into Variable
> IF insertcolumns IS NULL THEN
>   insertcolumns := fieldname;
> ELSE
>   insertcolumns := insertcolumns || ',' || fieldname;
> END IF;
>
> Until here everyting is fine ... but now I try to query  the value from 
> RECORD fieldvalues with the columname fieldname variable from the inner 
> loop !
> I tryed the following ...
>
> query_value :=  'select quote_ident(' || fieldvalues || ').quote_literal(' 
> || fieldname ||')';
>
> EXECUTE query_value;
>
>
> and I get the following error message ...
>
> ERROR:  could not find array type for data type record
> CONTEXT:  SQL statement "SELECT  'select quote_ident(' ||  $1  || 
> ').quote_literal(' ||  $2  ||')'"
> PL/pgSQL function "prx_db__appendtable" line 87 at assignment
>
>
>  END LOOP;
>
>END LOOP;
>
> I know the function is not runnable, but my question is  how can I 
> dynamically combine "fieldvalues"."fieldname" to read the values column by 
> colum out if a RECORD variable to generate the "columnvaluesstring" 
> mentioned above ?!
> Maybe this approach is to complicated and there is a quick and easy 
> solution ?!
>
> Any help is very much appreciated !!
>
> Thanx a lot & Regards
>
> Chris
>
> ---(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
> 



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

[SQL] trecherous subselect needs warning?

2006-02-07 Thread george young 
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
This query returns zero rows:

newschm3=# select run_id from s_bake where opset_id not in (select opset_id 
from opset_steps);
 run_id

(0 rows)

But, by my mistake, table opset_steps has no column "opset_id"!
Surely it should return an error, or at least a warning, not just an
empty rowset.  "s_bake" *does* have an "opset_id" column, so that's
what it uses.

The "from opset_steps" is useless.  I can understand it might be
inappropriate to make such illegal, but wouldn't a warning be appropriate?
It seems like postgres should know immediately that there is a
useless "from" clause.

Even trickier would be:
  select run_id from s_bake where opset_id in (select opset_id from 
opset_steps);

which would return all rows from s_bake IFF opset_steps has any rows!
Eeek!

I suppose the moral of the story is to ALWAYS, absolutely ALWAYS
qualify a correlation name (table alias).  Of course, what I meant
in the original query was:

  select s.run_id from s_bake s where s.opset_id not in (select os.opset_id 
from old_opset_steps os);

Sigh.  Am I missing something here?


-- George Young

-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

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

   http://archives.postgresql.org

Re: [SQL] Filtering data based on timestamp

2006-02-07 Thread Richard Huxton 

Panos Kassianidis wrote:

Thanks very much for the answer.
Could you also please tell me how am I supposed to write such a function?
How is date_trunc implemented so that I can extend it? In C in SQL?


I'd probably write it in plpgsql unless you need the speed.


And where can I find its source code?


With the source - available from the website.

--
  Richard Huxton
  Archonet Ltd

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

Re: [SQL] trecherous subselect needs warning?

2006-02-07 Thread Tom Lane 
george young  writes:
> This query returns zero rows:

> newschm3=# select run_id from s_bake where opset_id not in (select opset_id 
> from opset_steps);
>  run_id
> 
> (0 rows)

> But, by my mistake, table opset_steps has no column "opset_id"!
> Surely it should return an error, or at least a warning, not just an
> empty rowset.

Access to upper-level variables from subqueries is (a) useful and (b)
required by the SQL spec, so we are not going to start throwing warnings
about it.

regards, tom lane

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

Re: [SQL] trecherous subselect needs warning?

2006-02-07 Thread george young 
On Tue, 07 Feb 2006 12:45:53 -0500
Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins:

> george young  writes:
> > This query returns zero rows:
> 
> > newschm3=# select run_id from s_bake where opset_id not in (select opset_id 
> > from opset_steps);
> >  run_id
> > 
> > (0 rows)
> 
> > But, by my mistake, table opset_steps has no column "opset_id"!
> > Surely it should return an error, or at least a warning, not just an
> > empty rowset.
> 
> Access to upper-level variables from subqueries is (a) useful and (b)

My orginal posting suggested a warning for the *useless*
"from opset_steps" clause, since it's presence is misleading.  

But I don't suppose the SQL spec allows warnings that are not
explicitly in the spec, alas.

Of course upper-level variables must in general be accessible
from subqueries.

> required by the SQL spec, so we are not going to start throwing warnings
> about it.

I was just trying to find a way to prevent other innocent users
from wasting many hours of torment tracking down this subtle
twist of SQL...

How about a *documentation* suggestion that sub-queries can be
very dangerous if one doesn't qualify all column references?
Maybe with an example like the one that bit me?

-- George 
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

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

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

Re: [SQL] trecherous subselect needs warning?

2006-02-07 Thread Tom Lane 
george young  writes:
> How about a *documentation* suggestion that sub-queries can be
> very dangerous if one doesn't qualify all column references?
> Maybe with an example like the one that bit me?

I have no objection to a documentation example, but there isn't any
obvious place where it would fit in the manual.  Perhaps some sort
of "SQL gotchas" webpage on techdocs would be appropriate.

regards, tom lane

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

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