Re: [SQL] Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
On Saturday 11 April 2009 00:41:54 Jasen Betts wrote:
> On 2009-04-02, Alvaro Herrera wrote:
> > James Kitambara wrote:
> >> Dear Srikanth,
> >> You can solve your problem by doing this
> >>
> >> THE SQL IS AS FOLLOWS
> >> ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE
> >> TABLE NAME time_interval
> >>
> >> COUNT (*) FROM
> >> (select customer_id, log_session_id, start_ts, end_ts ,
> >> end_ts-start_ts as "Interval" from time_interval
> >> where end_ts-start_ts >= '1 hour'
> >> and '2008-12-07 07:59:59' between start_ts and end_ts)
> >> AS COUNT ;
> >
> > Another way to phrase the WHERE clause is with the OVERLAPS operator,
> > something like this:
> >
> > WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07
> > 08:59:59')
> >
> > What I'm not so sure about is how optimizable this construct is.
> >
>
> http://www.postgresql.org/docs/8.3/interactive/xindex.html
> if you gave the apropriate GIST index on (start_ts, end_ts) the
> overlaps may be optimisable. the subquery will run to completion
> and count will count the results. - but this form gives different results.
>
> beter to do
>
> select COUNT (*) AS COUNT FROM time_interval
> WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07
> 08:59:59')
>
> or
>
> select COUNT (*) AS COUNT FROM time_interval
> where end_ts-start_ts >= '1 hour'
> and '2008-12-07 07:59:59' between start_ts and end_ts;
I only managed to get this to use the gist-index, and not with the overlaps
operator. I had to install the contrib-module btree_gist in order to be able to
create a gist index on the timestamps.
This is my index:
CREATE index origo_tart_end_time_idx on onp_crm_activity_log using gist
(start_time, end_time) ;
start_time and end_time are both timestamps.
Here are the EXPLAIN outputs:
andreak=# EXPLAIN ANALYZE select id from onp_crm_activity_log where end_time -
start_time >= '1 hour' AND '2008-12-07 07:59:59' between start_time and
end_time;
QUERY
PLAN
---
Bitmap Heap Scan on onp_crm_activity_log (cost=10.56..232.62 rows=76 width=4)
(actual time=0.175..0.175 rows=0 loops=1)
Recheck Cond: (('2008-12-07 07:59:59'::timestamp without time zone >=
start_time) AND ('2008-12-07 07:59:59'::timestamp without time zone <=
end_time))
Filter: ((end_time - start_time) >= '01:00:00'::interval)
-> Bitmap Index Scan on origo_tart_end_time_idx (cost=0.00..10.54 rows=229
width=0) (actual time=0.168..0.168 rows=0 loops=1)
Index Cond: (('2008-12-07 07:59:59'::timestamp without time zone >=
start_time) AND ('2008-12-07 07:59:59'::timestamp without time zone <=
end_time))
Total runtime: 0.274 ms
(6 rows)
andreak=# EXPLAIN ANALYZE select id from onp_crm_activity_log where
(start_time, end_time) OVERLAPS('2008-11-07 07:59:59'::timestamp, '2008-12-07
08:59:59'::timestamp);
QUERY PLAN
Seq Scan on onp_crm_activity_log (cost=0.00..319.29 rows=2968 width=4)
(actual time=14.542..15.794 rows=83 loops=1)
Filter: "overlaps"(start_time, end_time, '2008-11-07 07:59:59'::timestamp
without time zone, '2008-12-07 08:59:59'::timestamp without time zone)
Total runtime: 16.129 ms
(3 rows)
Is it possible to make the overlaps operator use the index? I'd prefer the
overlaps-syntax as I find it cleaner.
--
Andreas Joseph Krogh
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
+-+
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Ordering a name list and ignoring whitespace
Hi all, Doing some googling and looking through the docs, I can't find an obvious way to do this beside post processing after the query (which I am trying to avoid). I'm trying to select a list of names in alphabetical order but ignoring the whitespace. So for example, the name "La Combe" should come after "Lace" but before "Lacs..." Any ideas? Mikel -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Ordering a name list and ignoring whitespace
Mikel Lindsaar wrote: Hi all, Doing some googling and looking through the docs, I can't find an obvious way to do this beside post processing after the query (which I am trying to avoid). I'm trying to select a list of names in alphabetical order but ignoring the whitespace. So for example, the name "La Combe" should come after "Lace" but before "Lacs..." Any ideas? Could you do it like this: SELECT replace(name_column, ' ', '') AS name_replaced FROM your_table_name ORDER BY name_replaced This can get a bit slowish if your table has quite a number of rows. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need a script that bakes INSERT script from SELECT results
I'm a novice to PL/pgSQL, and I'm curious, how to write function, that wraps SELECT results into INSERT script. The functionality I need is about to replicate table data into remote DB. I have tried dblink_build_insert(), but it requires primary key on tuple to create INSERT script from. I would prefer filter clause to primary key, to generate either multiple inserts or one multi-row insert. Could someone post an example how to LOOP through row fields to wrap them into 'VALUES(...,,)' list? Thanks, Olksy -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need a script that bakes INSERT script from SELECT results
On Apr 16, 2009, at 7:29 AM, ShuA wrote: Could someone post an example how to LOOP through row fields to wrap them into 'VALUES(...,,)' list? If you declare record or table row types, you can insert the values using (rec.*), something like this: create or replace function test () returns void as $$ declare rec record; begin for rec in select * from whatever loop insert into some_table values (rec.*); end loop; end; $$ language plpgsql; John DeSoi, Ph.D. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re[2]: [SQL] Need a script that bakes INSERT script from SELECT results
-Original Message- From: John DeSoi To: ShuA Date: Thu, 16 Apr 2009 08:25:15 -0400 Subject: Re: [SQL] Need a script that bakes INSERT script from SELECT results > On Apr 16, 2009, at 7:29 AM, ShuA wrote: > > > Could someone post an example how to LOOP through row fields to wrap > > them into 'VALUES(...,,)' list? > > > If you declare record or table row types, you can insert the values > using (rec.*), something like this: > > > create or replace function test () > returns void as $$ > declare > rec record; > begin > for rec in select * from whatever loop > insert into some_table values (rec.*); > end loop; > end; > $$ language plpgsql; > > John DeSoi, Ph.D. The functionality I need is about to replicate table data into remote DB. > for rec in select * from whatever loop > insert into some_table values (rec.*); > end loop; ^^^ that is not my case, unfortunately. The next, what is supposed to do with baked INSERT statements, is push them thru dblink_exec(), as text param, to make insert works on remote DB. Olksy -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Ordering a name list and ignoring whitespace
Mikel Lindsaar writes: > I'm trying to select a list of names in alphabetical order but > ignoring the whitespace. > So for example, the name "La Combe" should come after "Lace" but > before "Lacs..." FWIW, this would probably happen automatically if you were using a non-C locale. I'm not sure that's really a good solution, because switching to a different locale would affect every sort operation you ever do. But it's an alternative to consider. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] finding UNIQUES in information_schema
Hi all. Im failing while trying to obtain some metainfo from information_schema. Acording to the docs, constraint_column_usage and key_column_usage views contains some information about constraints and indexes. See: Table "public.almatnov" Column| Type | Modifiers -++ formu | integer| alucod | integer| default 0 codcarr | character varying(3) | default ''::character varying anifm | character varying(2) | default 20 comentarios | text | default ''::text Indexes: "almatnov_alucod_codcarr_idx" UNIQUE, btree (alucod, codcarr) "almatnov_codcarr_alucod" UNIQUE, btree (alucod, codcarr) And now: ematerias=# SELECT * from information_schema.constraint_table_usage where table_name='almatnov'; table_catalog | table_schema | table_name | constraint_catalog | constraint_schema | constraint_name ---+--+++---+- (0 rows) ematerias=# SELECT * from information_schema.key_column_usage where table_name='almatnov'; constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint +---+-+---+--++-+--+--- (0 rows) 1) Im a doing anything wrong? 2) It is safe to extract metainfo from pg_catalog? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to set the value to the column
Hi All, I have a table test with columns name and value test table name value It has around 500 rows. I added a new column id to the table, Table test id, name, value I am not sure how to insert numbers to my column id (1-500). Thanks
Re: [SQL] how to set the value to the column
DM wrote:
> Hi All,
>
> I have a table test with columns name and value
>
> test table
> name
> value
>
> It has around 500 rows.
>
> I added a new column id to the table,
>
> Table test
> id,
> name,
> value
>
> I am not sure how to insert numbers to my column id (1-500).
You can create a SEQUENCE and use this Sequence for that, an example:
test=# create table foo (name text);
CREATE TABLE
Zeit: 2,824 ms
test=*# copy foo from stdin;
Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.
>> a
>> b
>> c
>> d
>> e
>> \.
Zeit: 5592,132 ms
test=*# create sequence foo_seq;
CREATE SEQUENCE
Zeit: 10,030 ms
test=*# alter table foo add column id int;
ALTER TABLE
Zeit: 0,347 ms
test=*# update foo set id = nextval('foo_seq');
UPDATE 5
Zeit: 0,379 ms
test=*# select * from foo;
name | id
--+
a| 1
b| 2
c| 3
d| 4
e| 5
(5 Zeilen)
Zeit: 0,241 ms
test=*#
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to set the value to the column
never mind i got the answer. Thanks for looking into it. - deepak On Thu, Apr 16, 2009 at 12:00 PM, DM wrote: > Hi All, > > I have a table test with columns name and value > > test table > name > value > > It has around 500 rows. > > I added a new column id to the table, > > Table test > id, > name, > value > > I am not sure how to insert numbers to my column id (1-500). > > Thanks > > >
Re: [SQL] finding UNIQUES in information_schema
Gerardo Herzig writes: > Hi all. Im failing while trying to obtain some metainfo from > information_schema. Acording to the docs, constraint_column_usage and > key_column_usage views contains some information about constraints and > indexes. No, they contain information about constraints, period. How did you create those indexes? If you made them via SQL-standard PRIMARY KEY or UNIQUE constraint syntax, they should show in the information_schema. Otherwise not. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] finding UNIQUES in information_schema
Tom Lane wrote: > Gerardo Herzig writes: >> Hi all. Im failing while trying to obtain some metainfo from >> information_schema. Acording to the docs, constraint_column_usage and >> key_column_usage views contains some information about constraints and >> indexes. > > No, they contain information about constraints, period. How did you > create those indexes? If you made them via SQL-standard PRIMARY KEY or > UNIQUE constraint syntax, they should show in the information_schema. > Otherwise not. > > regards, tom lane > Well, i just dont get it. Official docs from 8.2 says: """ The view key_column_usage identifies all columns in the current database that are restricted by some *unique*, *primary key*, or foreign key constraint. Check constraints are not included in this view. Only those columns are shown that the current user has access to, by way of being the owner or having some privilege. """ Well, damn, lets create some UNIQUE edatos=# CREATE UNIQUE INDEX aluestud_alu_cod_anifm on aluestud (alucod, codcarr1, anifm); CREATE INDEX edatos=#\d aluestud [...] Indexes: "estud_idx1" PRIMARY KEY, btree (alucod, codcarr1) "aluestud_alu_cod_anifm" UNIQUE, btree (alucod, codcarr1, anifm) And... edatos=# select table_name, constraint_name, column_name from information_schema.key_column_usage where table_name='aluestud'; table_name | constraint_name | column_name +-+- aluestud | estud_idx1 | alucod aluestud | estud_idx1 | codcarr1 (2 rows) Damn, im that idiot? Or should i get the UNIQUE i have just defined also? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] finding UNIQUES in information_schema
Gerardo Herzig writes: > Tom Lane wrote: >> No, they contain information about constraints, period. How did you >> create those indexes? If you made them via SQL-standard PRIMARY KEY or >> UNIQUE constraint syntax, they should show in the information_schema. >> Otherwise not. > Well, i just dont get it. No, apparently not. The term "constraint" in this context has a very specific meaning; it's talking about the CONSTRAINT syntax in CREATE TABLE or ALTER TABLE. This is per SQL specification. Indexes are an implementation detail that the standard doesn't concern itself with; therefore anything you do with CREATE INDEX is outside the standard and is not reflected in the standard-defined information_schema. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
