Re: [SQL] Add calculated fields from one table to other table
Hi Richard, Thanks a lot. I am sending you the create statement of tables & few insert statements as well. Hope this helps to solve the problem. CREATE TABLE ticks( tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass), ric varchar(30) NOT NULL, tick_date date NOT NULL, tick_time time NOT NULL, price float8, volume int4, CONSTRAINT ticks_pkey PRIMARY KEY (tick_id),) WITHOUT OIDS; CREATE TABLE times( times_time time NOT NULL, count int4, CONSTRAINT times_pkey PRIMARY KEY (times_time)) selct statement of ticks table ric | tick_date | tick_time | price -++--+--- A | 2006-04-04 |
00:00:55.023 | 4.05 AA | 2006-04-04 | 00:00:55.023 | 9.05 A | 2006-04-04 | 00:00:59.023 | 6.05 A | 2006-04-04 | 00:01:00.023 | 5.05 ABC | 2006-04-04 | 00:01:00.509 |12.00 ABI | 2006-04-04 | 00:01:03.511 |13.00 AA | 2006-04-04 | 00:01:08.023 | 6.05 ABT | 2006-04-04 | 00:01:08.518 | 3.06 ABT | 2006-04-04 | 00:01:09.518 | 7.06 select statement of times table times_time --- 00:00:00 00:01:00 00:02:00 I want the query result to look ric | times_time | count |
avg_price++---+--- A | 00:00:00 | 2 | 5.05 AA | 00:00:00 | 1 | 9.05ABC | 00:00:00 | 0 | ABI | 00:00:00 | 0 | ABT | 00:00:00 | 0 | A | 00:01:00 | 1 | 5.05 AA | 00:01:00 | 1 | 6.05ABC | 00:01:00 | 1 |12.00 ABI | 00:01:00 | 1 |13.00 ABT | 00:01:00 | 2 | 5.06 I am really thankful to you. Regards RoopaRichard Broersma Jr <[EMAIL PROTECTED]> wrote: > Hi Richard,> > Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I> am able to get the result for a particular ric.> > Can you help me with getting the result for all the rics in the ticks table> > Thanks> RoopaCould you send create table statements for the tables you are working on, and a few insertstatements for each table to have sample data. then show what you want the query results to looklike.But from what you stated in your previous emails here is what I gather: maybe it might work?select tk.ric as ric, tm.times_time as minute, --timestamps by minutescount(tk.*) as ,...from times tmleft join ticks tkon (tm.times_time = date_trunc('minutes', tk.time))group by ric, minuteorder by minute;
We have the perfect Group for you. Check out the handy changes to Yahoo! Groups.
Re: [HACKERS] [SQL] Case Preservation disregarding case
"Chuck McDevitt" <[EMAIL PROTECTED]> writes: > Equivalent, yes. But I can interpret that clause it mean I can show > either the case folded or non-case-folded value in the information > schema, as they are equivalent. Well, that's an interesting bit of specs-lawyering, but I don't see how you can defend it against these rules in SQL99 5.2: 21) For every IB there is exactly one corresponding case-normal form CNF. CNF is an derived from IB as follows. Let n be the number of characters in IB. For i ranging from 1 (one) to n, the i-th character M(i) of IB is translated into the corresponding character or characters of CNF as follows. Case: a) If M(i) is a lower case character or a title case character for which an equivalent upper case sequence U is defined by Unicode, then let j be the number of characters in U; the next j characters of CNF are U. b) Otherwise, the next character of CNF is M(i). 22) The case-normal form of the of a is used for purposes such as and including determination of identifier equivalence, representation in the Definition and Information Schemas, and representation in diagnostics areas. NOTE 44 - Any lower-case letters for which there are no upper- case equivalents are left in their lower-case form. Again, obviously we are not compliant because we fold to lower rather than upper case, but I do not see how you can read (22) as not requiring the information schema to show the upper-cased form. The output of functions such as PQfname() might be considered closer to diagnostics info than information schema, but that's covered too. But the really serious problem with what you propose is that it would allow two table columns with names that the system considers distinct to show as the same string in the information schema and diagnostic outputs. That can't be acceptable --- it's going to break any application that does any nontrivial analysis of what it sees there, not to mention that it violates various primary key constraints in the information schema specification. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Add calculated fields from one table to other table
> Hi Richard,
>
> Thanks a lot. I still am not able to get the result for all the rics in the
> ticks table but I
> am able to get the result for a particular ric.
>
> Can you help me with getting the result for all the rics in the ticks table
>
> Thanks
> Roopa
Could you send create table statements for the tables you are working on, and a
few insert
statements for each table to have sample data. then show what you want the
query results to look
like.
But from what you stated in your previous emails here is what I gather: maybe
it might work?
selecttk.ric as ric,
tm.times_time as minute, --timestamps by minutes
count(tk.*) as ,
...
from times tm
left join ticks tk
on (tm.times_time = date_trunc('minutes', tk.time))
group by ric, minute
order by minute;
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Add calculated fields from one table to other table
Hi Richard, Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I am able to get the result for a particular ric. Can you help me with getting the result for all the rics in the ticks table Thanks RoopaRichard Broersma Jr <[EMAIL PROTECTED]> wrote: > Thanks for your help. That does make sense, but I am not able to get the result what I wanted> exactly. Let me explain you.> > I have ticks table in which I have columns like ric, tick_time, price & volume. The times> table has just one column with times_time which has time data for each minute ie.) > > Ticks> ric | tick_time | price | volume> A | 12:00:01 | 23.00 | 12> A |
12:00:02 | 26.00 | 7> B | 12: 00:02 | 8.00 | 2> B | 12:01:01 | 45.00 | 6> > Times> times_time> 12:00> 12:01> 12:02> > Now I want the timeseries for each minute for all ric in the tick table. So my query goes like> this for a particular ric say for example ric 'A'> > select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from ticks where ric = 'A'> group by ric) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'A' group by> tm.times_time, foo.ric order by tm.times_time; > > I get the result as I expect, but i am not able to derive a query for all rics in the tick> table.> > I really appreciate your help.Sorry I prematurely sent my
first emailSelect foo.ric,date_trunc("minute", tm.times_time) as time_tick,count(tk.*) tickperminute,avg(tk.price),... --your other aggregate functionsfrom (select ric from ticks where ric = 'A' group by ric) as foojoin ticks tk on (tk.ric = foo.ric) right join times tmon (tk.tick_time >= tm.times_time) and (tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and (tk.ric = 'A') -- this shouldn't be neccessary-- if you restructor your join-- since foo limits all ric to 'A'-- but since it is on the wrong side-- of an outer join it can't.group by foo.ric, time_tick order by time_tick;Regards,Richard Broersma Jr.
Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call rates.
Re: [SQL] Table Relationships
Thanks for the help. From: Aaron Bono [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 2:44 PM To: A. Kretschmer Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Table Relationships On 10/31/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes: > am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes: > > I would go further by adding a type table like this: > > > > operation_type ( > > operation_type_id bigserial (PK), > > You are sure, that you need bigserial? Hey, your idea is okay, but i think, we don't need *BIG*serial for this. Okay? Andreas Sorry, just force of habbit. Serial works or you can just drop the id and use the code as the primary key. You should at the very least put a unique constraint on the code field. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] record datatype comparisons
On Oct 31 06:49, Alvaro Herrera wrote: > George Pavlov wrote: > > thanks fo the reply. i was misled by pgAdmin (1.6) giving both as > > "record" in the datatype (i thought "row" and "?column?" were just "best > > guess" column headers). > > > > so, if they are indeed differently shaped is there any way to make them > > be the same shape? > > > > note that this one also fails with the same error (one would think these > > are the "same shape"): > > > > select > > (select (1,2)) > > is distinct from > > (select (1,2)) > > ; > > This one works: > > alvherre=# select > row(1,2) > is distinct from > row(1,2) > ; > ?column? > -- > f > (1 fila) What's the difference between "SELECT (1, 2);" and "SELECT ROW(1, 2);"? Regards. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] record datatype comparisons
George Pavlov wrote: > thanks fo the reply. i was misled by pgAdmin (1.6) giving both as > "record" in the datatype (i thought "row" and "?column?" were just "best > guess" column headers). > > so, if they are indeed differently shaped is there any way to make them > be the same shape? > > note that this one also fails with the same error (one would think these > are the "same shape"): > > select > (select (1,2)) > is distinct from > (select (1,2)) > ; This one works: alvherre=# select row(1,2) is distinct from row(1,2) ; ?column? -- f (1 fila) Is that what you're after? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] record datatype comparisons
On Oct 31 04:22, Andrew Sullivan wrote: > On Tue, Oct 31, 2006 at 12:38:36PM -0800, George Pavlov wrote: > > Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of > > type record, aren't they? > > I don't think so. Psql gives you a hint that not: > > testing=# SELECT (1::int, 'a'::varchar); > row > --- > (1,a) > (1 row) > > testing=# SELECT (SELECT(1::int, 'a'::varchar)); > ?column? > -- > (1,a) > (1 row) > > Note the column headers. They're differently shaped. Because > pseudotype record doesn't have a shape, equality doesn't make sense, > so you need two shapes that are already identical, so they can use > the matching rules for that. Can you be more verbose please? I couldn't understand what you mean with "shape". AFAIK, both above queries should return same TupleDesc, which I think means they should share same "shape". I don't have an idea about the row comparison internals, but I still cannot see a reason for the difference of returned types. I'd be appreciated if you can clarify the explanation a little bit. Regards. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] record datatype comparisons
thanks fo the reply. i was misled by pgAdmin (1.6) giving both as "record" in the datatype (i thought "row" and "?column?" were just "best guess" column headers). so, if they are indeed differently shaped is there any way to make them be the same shape? note that this one also fails with the same error (one would think these are the "same shape"): select (select (1,2)) is distinct from (select (1,2)) ; ERROR: operator does not exist: record = record SQL state: 42883 Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan > Sent: Tuesday, October 31, 2006 1:23 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] record datatype comparisons > > On Tue, Oct 31, 2006 at 12:38:36PM -0800, George Pavlov wrote: > > Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of > > type record, aren't they? > > I don't think so. Psql gives you a hint that not: > > testing=# SELECT (1::int, 'a'::varchar); > row > --- > (1,a) > (1 row) > > testing=# SELECT (SELECT(1::int, 'a'::varchar)); > ?column? > -- > (1,a) > (1 row) > > Note the column headers. They're differently shaped. Because > pseudotype record doesn't have a shape, equality doesn't make sense, > so you need two shapes that are already identical, so they can use > the matching rules for that. > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > Everything that happens in the world happens at some place. > --Jane Jacobs ---(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] record datatype comparisons
On Tue, Oct 31, 2006 at 12:38:36PM -0800, George Pavlov wrote: > Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of > type record, aren't they? I don't think so. Psql gives you a hint that not: testing=# SELECT (1::int, 'a'::varchar); row --- (1,a) (1 row) testing=# SELECT (SELECT(1::int, 'a'::varchar)); ?column? -- (1,a) (1 row) Note the column headers. They're differently shaped. Because pseudotype record doesn't have a shape, equality doesn't make sense, so you need two shapes that are already identical, so they can use the matching rules for that. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Round Numeric Type
On Tue, Oct 31, 2006 at 05:35:17PM -0200, Ezequias Rodrigues da Rocha wrote: > Hi list, > > I did a trigger to update a points on a table but in some cases the > PostgreSQL does a round of my Numeric like. > > If >= 0.5 so postgresql puts 1 > If < 0.5 so postgresql puts 0 > > Did anybody knows if it is possible to control this matter ? Sounds like your datatype doesn't match your input, and that you've got a type that rounds. What's the datatype you're putting into? A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Table Relationships
On 10/31/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes:> am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes:> > I would go further by adding a type table like this: > >> > operation_type (> > operation_type_id bigserial (PK),>> You are sure, that you need bigserial?Hey, your idea is okay, but i think, we don't need *BIG*serial for this. Okay?AndreasSorry, just force of habbit. Serial works or you can just drop the id and use the code as the primary key. You should at the very least put a unique constraint on the code field. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] record datatype comparisons
I am trying to do some record comparisons using IS DISTINCT FROM and I feel like I am missing something. Basically comparisons between manually constructed records work as expected, but if I have a record returned by a select on one (or both sides) of the comparison I get errors "ERROR: operator does not exist: record = record". I suspect some simple missing parentheses/syntax issue but I feel like I have tried everything... The simplest way to reproduce: select ((1::int,'a'::varchar) is distinct from (2::int,'a'::varchar)); --> true, as expected select ((1::int,'a'::varchar) is distinct from (select (2::int,'a'::varchar))); --> throws the error Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of type record, aren't they? In real life I want the right side of the IS DISTINCT FROM to be the result of a query to a table. Please help. Thanks! George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Table Relationships
am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes: > am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes: > > I would go further by adding a type table like this: > > > > operation_type ( > > operation_type_id bigserial (PK), > > You are sure, that you need bigserial? Hey, your idea is okay, but i think, we don't need *BIG*serial for this. Okay? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] Table Relationships
am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes: > I would go further by adding a type table like this: > > operation_type ( > operation_type_id bigserial (PK), You are sure, that you need bigserial? > This gives you the flexibility to add more operation types in the future. Yeah! 9223372036854775807 possible types, great! ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Round Numeric Type
Hi list,I did a trigger to update a points on a table but in some cases the PostgreSQL does a round of my Numeric like.If >= 0.5 so postgresql puts 1If < 0.5 so postgresql puts 0Did anybody knows if it is possible to control this matter ? RegardsEzequias
Re: [SQL] Table Relationships
On 10/31/06, A. Kretschmer <[EMAIL PROTECTED]> wrote:
am Tue, dem 31.10.2006, um 11:15:26 -0600 mailte Curtis Scheer folgendes:> Given the following two tables:>> CREATE TABLE public.task> (> taskid int4 NOT NULL DEFAULT nextval('task_taskid_seq'::regclass),
> description varchar,> CONSTRAINT pk_taskid PRIMARY KEY (taskid)> )>> public.users> (> userid int4 NOT NULL,> username varchar,> CONSTRAINT pk_userid PRIMARY KEY (userid)
> )>> I want to record which user ?performed the task? and which user ?checked the> task?, I?ve come up with a few ideas on this but I would like to know what the> correct way would be to implement this into my table design.
Perhaps a table like this:(user int references public.users,task int references public.task,ts timestamptz default now(),action char(1) check (action in ('p','c')))
-- with p(perform), c(cheked)I would go further by adding a type table like this:operation_type ( operation_type_id bigserial (PK), operation_cd varchar(10), operation_name varchar(20)
)with two codes "perform" and "check" and another tableoperation (user int references public.users (PK),task int references public.task (PK),ts timestamptz default now() (PK),
operation_type_id bigint references operation_type)This gives you the flexibility to add more operation types in the future.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Table Relationships
am Tue, dem 31.10.2006, um 11:15:26 -0600 mailte Curtis Scheer folgendes:
> Given the following two tables:
>
> CREATE TABLE public.task
> (
> taskid int4 NOT NULL DEFAULT nextval('task_taskid_seq'::regclass),
> description varchar,
> CONSTRAINT pk_taskid PRIMARY KEY (taskid)
> )
>
> public.users
> (
> userid int4 NOT NULL,
> username varchar,
> CONSTRAINT pk_userid PRIMARY KEY (userid)
> )
>
> I want to record which user ?performed the task? and which user ?checked the
> task?, I?ve come up with a few ideas on this but I would like to know what the
> correct way would be to implement this into my table design.
Perhaps a table like this:
(
user int references public.users,
task int references public.task,
ts timestamptz default now(),
action char(1) check (action in ('p','c'))
)
-- with p(perform), c(cheked)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] [SQL] Case Preservation disregarding case
On Tue, 31 Oct 2006, Chuck McDevitt wrote: > We treated quoted identifiers as case-specific, as the spec requires. > > In the catalog, we stored TWO columns... The column name with case > converted as appropriate (as PostgreSQL already does), used for looking > up the attribute, > And a second column, which was the column name with the case exactly as > entered by the user. Wouldn't using that second column's value tend to often violate 5.2SR10 (at least that's the reference item in SQL92)? AFAICT, that rule basically says that the regular identifier is equivalent to the case-folded one for purposes of information and definition schema and similar purposes which seems like it would be intended to include things like column labeling for output. There's a little bit of flexibility there on both similar purposes and equivalence, though. 10) The of a is equivalent to an in which every letter that is a lower- case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equiva- lence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Table Relationships
Given the following two tables:
CREATE TABLE public.task
(
taskid int4 NOT NULL DEFAULT
nextval('task_taskid_seq'::regclass),
description varchar,
CONSTRAINT pk_taskid PRIMARY KEY (taskid)
)
public.users
(
userid int4 NOT NULL,
username varchar,
CONSTRAINT pk_userid PRIMARY KEY (userid)
)
I want to record which user “performed the task”
and which user “checked the task”, I’ve come up with a few
ideas on this but I would like to know what the correct way would be to
implement this into my table design.
Thanks,
Curtis
Re: [SQL] Add calculated fields from one table to other table
> Thanks for your help. That does make sense, but I am not able to get the
> result what I wanted
> exactly. Let me explain you.
>
> I have ticks table in which I have columns like ric, tick_time, price &
> volume. The times
> table has just one column with times_time which has time data for each minute
> ie.)
>
> Ticks
> ric | tick_time | price | volume
> A | 12:00:01 | 23.00 | 12
> A | 12:00:02 | 26.00 | 7
> B | 12: 00:02 | 8.00 | 2
> B | 12:01:01 | 45.00 | 6
>
> Times
> times_time
> 12:00
> 12:01
> 12:02
>
> Now I want the timeseries for each minute for all ric in the tick table. So
> my query goes like
> this for a particular ric say for example ric 'A'
>
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from
> ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >=
> tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric =
> 'A' group by
> tm.times_time, foo.ric order by tm.times_time;
>
> I get the result as I expect, but i am not able to derive a query for all
> rics in the tick
> table.
>
How about:
SELECT
foo.ric,
date_trunc('minute', tm.times_time) as minute,
count(tk.*),
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
