[SQL] auto_insert
I'm looking function at postrgeSQL the same like "auto_insert " function at mySQL
Re: [SQL] auto_insert
serial ex: create table a( i serial, b int); bye - Original Message - From: jodi To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 10:27 Subject: [SQL] auto_insert I'm looking function at postrgeSQL the same like "auto_insert " function at mySQL
Re: [SQL] time series data
Hi,
thanks for the feedback. btw i've run the statement below & got the following result :
time_key | yr_id | month_id | month_desc | day_id
--+---+--++
193 | 1994 | 7 | jul | 13
(1 row)
actually if i'm going to have a result as below how could i did in my statement ???
The result should appear as :
time_key | yr_id | month_id | month_desc |day_id
--+---+--+---
1 | 1994 | 1 | Jan
2 | 1994 | 2 | Feb
3 | 1994 | 3 | Mac
4 | 1994 | 4 | Apr
5 | 1994 | 5 | May
6 | 1994 | 6 | Jun
7 | 1994 | 7 | July
8 | 1994 | 8 | Aug
9 | 1994 | 9 | Sept
10 | 1994 | 10 | Oct
11 | 1994 | 11 | Nov
12 | 1994 | 12 | Dec
.
.
.
the data for day_id should be incremental from 1->31 for each month for specific year. meaning to say Every month should have the day_id
from 1>31 . (follow exactly the day of the month)
Time_key
Yr_id
Month_id
Month_desc
Day_id(1-30/31 days)
1
1994
1
Jan
1
2
1994
1
Jan
2
3
1994
1
Jan
3
4
1994
1
Jan
4
5
1994
1
Jan
5
6
1994
1
Jan
6
7
1994
1
Jan
7
8
1994
1
Jan
8
9
1994
1
Jan
9
pls guide /help me to solve the above problem . thanks in advance.
Tomasz Myrta <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/27/2004 10:56 AM CET
To: [EMAIL PROTECTED]
cc: [EMAIL PROTECTED]
Subject: Re: [SQL] time series data
Dnia 2004-01-27 02:42, Użytkownik [EMAIL PROTECTED] napisał:
>
>
> Hi,
>
> thanks for the info..btw can u pls explain a little bit detail since
> i can't get thru yr solution.
> thanks in advance
insert into time_table (time_key,year_id,month_id,month_desc,day_id)
select
newid,
extract('year' from your_date),
extract('month' from your_date),
to_char(your_date,'mon'),
extract('day' from your_date)
...
from (select nextval('time_seq') as newid,
'1994-01-01'::date+'1 day'::interval * currval('time_seq') as your_date) x;
Everything you need is to execute query above as many times as you need.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL Query for Top Down fetching of childs
On Thursday 29 January 2004 06:11, Kumar wrote: > Dear Friends, > > Postgres 7.3.4 on RH Linux 7.2. > > I need a query to get the Childs of a parent (Top down analysis). Need to > list all the departments(Childs) of a parent organization. The table > structure is Two places to look for examples: 1. the contrib/tablefunc folder has an example of this sort of thing 2. search the mailing list articles for CONNECT BY (oracle's name for this sort of thing) or "tree" -- Richard Huxton Archonet Ltd ---(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
[SQL] query not using index for descending records?
Hi All, I have this table; id - Serial datetime - timestamp without timezone with the index as index idx_trafficlogs_datetime_id on trafficlogs using btree (datetime,id); When performing the following query: explain select datetime,id from trafficlogs order by datetime,id limit 20; QUERY PLAN Limit (cost=0.00..2.31 rows=20 width=12) -> Index Scan using idx_trafficlogs_datetime_id on trafficlogs (cost=0.00..1057.89 rows=9172 width=12) (2 rows) however, I am wanting to return the last 20 records. I have been using: explain select datetime,id from trafficlogs order by datetime,id desc limit 20; QUERY PLAN - Limit (cost=926.38..926.43 rows=20 width=12) -> Sort (cost=926.38..949.31 rows=9172 width=12) Sort Key: datetime, id -> Seq Scan on trafficlogs (cost=0.00..322.72 rows=9172 width=12) as you can see, a sequential scan is performed. How do I get pg to use an index scan for this query. The table in a production environment grows by approx 150,000 records daily, hence long term performance is a major factor here - for each additional day of data, the above query takes an additional 6-8 secs to run. Tia, Darren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] limit 1 and functional indexes
On Wed, Jan 28, 2004 at 12:23:38 +0100,
Alexandra Birch <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Postgres choses the wrong index when I add limit 1 to the query.
> This should not affect the index chosen.
I don't know the complete answer to your question, but since no one else
has commented I will answer what I can.
It IS reasobable for the planner to choose a different plan when you
add a LIMIT clause to a query.
> I read that functional indexes are sometimes not chosen correctly by
> optimizer.
I don't believe there are any particular problems with functional indexes.
The opitmizer isn't perfect and will sometimes choose poor plans.
> Is there anything I can do to always use the functional index in the
> following queries?
Have you done an ANALYZE of the table recently?
It might be useful to see the EXPLAIN ANALYZE output, rather than just
the EXPLAIN output, as that will give the actual times needed to do
the various steps.
>
> Query with limit 1 choses wrong index:
> ---
> explain
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
> order by order_date DESC LIMIT 1
>
> Index Scan Backward using transactions_date_aff on transactions
> (cost=0.00..930780.96 rows=2879 width=33)
> ---
>
> Without limit 1 choses correct index:
> ---
> explain
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
> order by order_date DESC
>
> Index Scan using transactions_pop_i on transactions (cost=0.00..11351.72 rows=2879
> width=33)
> ---
>
> We have postgresql-7.3.2-3.
> Thank you,
>
> Alexandra
>
> ---(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 7: don't forget to increase your free space map settings
Re: [SQL] LEFT JOIN on one and/or another column
On Wed, Jan 28, 2004 at 20:27:00 -0800, Octavio Alvarez <[EMAIL PROTECTED]> wrote: > > Hi. I have a table with two foreign keys (1 field each), like in > > id| serial > ext_key_original | integer > ext_key_exception | integer > > They mean different things, as one refers to a typical value, and the > other one refers to an exception that applies for that tuple. > > Each key references a different table, one with typical data, and one with > exception data. > > I'd like to join on the original, except when exception <> NULL, but I > can't quite figure out how to do so. I am not completely sure from your description what exactly you are trying to do, but it sounds like you can left join your table to the two foreign key tables and then use coallesce to grab the value from the appropiate joined table. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Query for Top Down fetching of childs
Thanks a lot Mr.Richard Huxton. It managed to find a similar one and modified to my need. It is working fine. Thanks a lot - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Thursday, January 29, 2004 3:57 PM Subject: Re: [SQL] SQL Query for Top Down fetching of childs > On Thursday 29 January 2004 06:11, Kumar wrote: > > Dear Friends, > > > > Postgres 7.3.4 on RH Linux 7.2. > > > > I need a query to get the Childs of a parent (Top down analysis). Need to > > list all the departments(Childs) of a parent organization. The table > > structure is > > Two places to look for examples: > 1. the contrib/tablefunc folder has an example of this sort of thing > 2. search the mailing list articles for CONNECT BY (oracle's name for this > sort of thing) or "tree" > > -- > Richard Huxton > Archonet Ltd > > ---(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
[SQL]
Hi I have an urgent problem I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i can not insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); executing above statement with plain integers does fine and inserts them into table.. Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx
Re: [SQL]
So, you are an artist. Isn't it? :) Your query is actually a string. This is your string: INSERT into table values(a,b,c,d) You must change your string to actually use values of tha a, b... You can make this string with sprintf sprintf(string, "INSERT into table values(%d,%d,%d,%d)", a, b, c, d); Then you launch: res=PQexecute(Conn, string); Why don't you use PHP? Is easier. Have a nice day - Original Message - From: MUKTA To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 15:08 Subject: [SQL] Hi I have an urgent problem I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i can not insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); executing above statement with plain integers does fine and inserts them into table.. Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx
Re: [SQL] query not using index for descending records?
On Thu, Jan 29, 2004 at 22:18:08 +1000, email lists <[EMAIL PROTECTED]> wrote: > Limit (cost=0.00..2.31 rows=20 width=12) >-> Index Scan using idx_trafficlogs_datetime_id on trafficlogs > (cost=0.00..1057.89 rows=9172 width=12) > (2 rows) > > however, I am wanting to return the last 20 records. I have been using: > > explain select datetime,id from trafficlogs order by datetime,id desc > limit 20; You probably don't want to do that. The DESC only applies to the one expression it follows. What you want is probably: explain select datetime,id from trafficlogs order by datetime desc,id desc limit 20; The index won't get used because with id and datetime being checked in different orders, only the first part of the index is usable. And probably that wasn't selective enough for an index scan to be used. ---(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]
You might also consider using ecpg which allows a syntax like: int a, b, c; EXEC SQL INSERT INTO mytable ( :a, :b, :c ); See http://www.postgresql.org/docs/current/interactive/ecpg.html for details. HTH Jürgen Viorel Dragomir <[EMAIL PROTECTED]> schrieb am 29.01.2004, 14:11:44: > So, you are an artist. Isn't it? :) > Your query is actually a string. > This is your string: INSERT into table values(a,b,c,d) > > You must change your string to actually use values of tha a, b... > You can make this string with sprintf > sprintf(string, "INSERT into table values(%d,%d,%d,%d)", a, b, c, d); > > Then you launch: > res=PQexecute(Conn, string); > > Why don't you use PHP? Is easier. > > Have a nice day > - Original Message - > From: MUKTA > To: [EMAIL PROTECTED] > Sent: Thursday, January 29, 2004 15:08 > Subject: [SQL] > > > Hi I have an urgent problem > I want to insert values into a table using the C syscalls provided by the libpq > library, but i find that i can not insert into the table when i use variables > instead of values...like so: > int a,b,c,d; > using the C function > > res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); > > executing above statement with plain integers does fine and inserts them into > table.. > > Is there some special way to insert variables rather than plain values? do i have > to build functions (in sql) or somehting?help! > Thanx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query not using index for descending records?
O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 : As i see there was a thread http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php dealing with this issue, assuming the "correct" order by should be "order by datetime ASC, id DESC". Do you know of any progress for declaring the direction of each column in a multicolumn index? > On Thu, Jan 29, 2004 at 22:18:08 +1000, > email lists <[EMAIL PROTECTED]> wrote: > > Limit (cost=0.00..2.31 rows=20 width=12) > >-> Index Scan using idx_trafficlogs_datetime_id on trafficlogs > > (cost=0.00..1057.89 rows=9172 width=12) > > (2 rows) > > > > however, I am wanting to return the last 20 records. I have been using: > > > > explain select datetime,id from trafficlogs order by datetime,id desc > > limit 20; > > You probably don't want to do that. The DESC only applies to the one > expression it follows. What you want is probably: > explain select datetime,id from trafficlogs order by datetime desc,id desc > limit 20; > > The index won't get used because with id and datetime being checked in > different orders, only the first part of the index is usable. And probably > that wasn't selective enough for an index scan to be used. > > ---(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 > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] query not using index for descending records?
On Thu, Jan 29, 2004 at 15:29:11 +0200, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 : > > As i see there was a thread > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php > dealing with this issue, assuming the "correct" order by > should be "order by datetime ASC, id DESC". So you really didn't want them in the reverse order? > Do you know of any progress for declaring the direction of each > column in a multicolumn index? If you are using 7.4 you can use a functional index to get around this. Assuming id is a numeric type, you can make an index on datetime and (-id) and sort by datetime, -id and the index should get used. This should still get fixed at some point, as this trick doesn't work for types for which the - operator exists. But I haven't heard of anyone working on it for 7.5, so don't expect a real fix any time soon. ---(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]
Hi!! wow, ur help was extremely helpfull! thanx a bunch! i just joined this pgsql project 2 days ago so i dunno bout php etc, i guess i wont need to use the ecpg stuff for now.. Thanx a lot! Bye PS: how did u know i was an artist! :) - Original Message - From: Viorel Dragomir To: MUKTA ; [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 6:41 PM Subject: Re: [SQL] So, you are an artist. Isn't it? :) Your query is actually a string. This is your string: INSERT into table values(a,b,c,d) You must change your string to actually use values of tha a, b... You can make this string with sprintf sprintf(string, "INSERT into table values(%d,%d,%d,%d)", a, b, c, d); Then you launch: res=PQexecute(Conn, string); Why don't you use PHP? Is easier. Have a nice day - Original Message - From: MUKTA To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 15:08 Subject: [SQL] Hi I have an urgent problem I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i can not insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); executing above statement with plain integers does fine and inserts them into table.. Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx
Re: [SQL] limit 1 and functional indexes
> >
> > Postgres choses the wrong index when I add limit 1 to the query.
> > This should not affect the index chosen.
>
> I don't know the complete answer to your question, but since no one else
> has commented I will answer what I can.
Thanks - your reply is apreciated :)
> It IS reasobable for the planner to choose a different plan when you
> add a LIMIT clause to a query.
OK - I'll investigate this further.
> > I read that functional indexes are sometimes not chosen correctly by
> > optimizer.
>
> I don't believe there are any particular problems with functional indexes.
> The opitmizer isn't perfect and will sometimes choose poor plans.
OK - but there was some discussion about statistics for functional indexes, for eg:
http://archives.postgresql.org/pgsql-general/2004-01/msg00978.php
This does not help me solve my problem though :)
> > Is there anything I can do to always use the functional index in the
> > following queries?
>
> Have you done an ANALYZE of the table recently?
Yip - I should have said we do a daily VACUUM ANALYZE.
> It might be useful to see the EXPLAIN ANALYZE output, rather than just
> the EXPLAIN output, as that will give the actual times needed to do
> the various steps.
I thought the cost values would be enough from the EXPLAIN alone.
And the query takes so long to run :(
Here is the output of EXPLAIN ANALYZE first with limit 1 then without:
explain analyze
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
order by order_date DESC LIMIT 1;
--
Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0
loops=1)
-> Index Scan Backward using transactions_date_aff on transactions
(cost=0.00..982549.96 rows=2956 width=33) (actual
time=377718.61..377718.61 rows=0 loops=1)
Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111'::text)
Total runtime: 378439.32 msec
explain analyze
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
order by order_date DESC;
QUERY PLAN
-
Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0
loops=1)
Sort Key: order_date
-> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79
rows=2956 width=33) (actual time=126.13..126.13
rows=0 loops=1)
Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111'::text)
Total runtime: 248.25 msec
Thank you,
Alexandra
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] query not using index for descending records?
O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 : > On Thu, Jan 29, 2004 at 15:29:11 +0200, > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 : > > > > As i see there was a thread > > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php > > dealing with this issue, assuming the "correct" order by > > should be "order by datetime ASC, id DESC". > > So you really didn't want them in the reverse order? I am not the initiator of this thread, i was just lurking :) > > > Do you know of any progress for declaring the direction of each > > column in a multicolumn index? > > If you are using 7.4 you can use a functional index to get around this. > Assuming id is a numeric type, you can make an index on datetime and > (-id) and sort by datetime, -id and the index should get used. > > This should still get fixed at some point, as this trick doesn't work > for types for which the - operator exists. But I haven't heard of > anyone working on it for 7.5, so don't expect a real fix any time soon. > It would be nice to have this feature for varchar as well. > ---(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 8: explain analyze is your friend
[SQL] How to retrieve N lines of a text field.
Hi all;
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
I had thought about using something like:
select substring(test from '#"' || repeat('%\n', $1) || '#"%' for '#') from
multiline_test;
However, this always selects every line but the final one (because %\n seems
to be interpreted to be the largest possible string, while I want it to be
the smallest possible string).
Is there a workaround? Any other help? Or do I need to write a UDF?
Best Wishes,
Chris Travers
---(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] limit 1 and functional indexes
On Thu, Jan 29, 2004 at 16:02:06 +0100,
Alexandra Birch <[EMAIL PROTECTED]> wrote:
>
> Here is the output of EXPLAIN ANALYZE first with limit 1 then without:
The time estimate for the limit 1 case is way off. I can't tell if that
is a bug or not having detailed enough statistics.
Hopefully someone more knowlegable will take a look at this question.
>
> explain analyze
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
> order by order_date DESC LIMIT 1;
> --
> Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0
> loops=1)
>-> Index Scan Backward using transactions_date_aff on transactions
> (cost=0.00..982549.96 rows=2956 width=33) (actual
> time=377718.61..377718.61 rows=0 loops=1)
> Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111'::text)
> Total runtime: 378439.32 msec
>
> explain analyze
> select code
> from transactions
> where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
> order by order_date DESC;
>QUERY PLAN
>
> -
> Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17
> rows=0 loops=1)
>Sort Key: order_date
>-> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79
> rows=2956 width=33) (actual time=126.13..126.13
> rows=0 loops=1)
> Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111'::text)
> Total runtime: 248.25 msec
>
> Thank you,
>
> Alexandra
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to retrieve N lines of a text field.
"Chris Travers" <[EMAIL PROTECTED]> writes:
> However, this always selects every line but the final one (because %\n seems
> to be interpreted to be the largest possible string, while I want it to be
> the smallest possible string).
I don't think you can solve this in SQL99 regular expressions, but
if you use the POSIX-style regex operators, you can write something
like "[^\n]*\n" to match exactly one line. There are other features
that might help too, such as the counted-match operator "{n}".
Read the man page.
BTW, I bought a copy of the O'Reilly book "Mastering Regular
Expressions" a couple years ago, and it is worth every penny
if you do much of anything with regexes.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] query not using index for descending records?
On Thu, 29 Jan 2004, Bruno Wolff III wrote: > On Thu, Jan 29, 2004 at 15:29:11 +0200, > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > O kyrios Bruno Wolff III egrapse stis Jan 29, 2004 : > > > > As i see there was a thread > > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00762.php > > dealing with this issue, assuming the "correct" order by > > should be "order by datetime ASC, id DESC". > > So you really didn't want them in the reverse order? > > > Do you know of any progress for declaring the direction of each > > column in a multicolumn index? > > If you are using 7.4 you can use a functional index to get around this. > Assuming id is a numeric type, you can make an index on datetime and > (-id) and sort by datetime, -id and the index should get used. > > This should still get fixed at some point, as this trick doesn't work > for types for which the - operator exists. But I haven't heard of > anyone working on it for 7.5, so don't expect a real fix any time soon. I'd thought that I'd previously sent a message containing a set of definitions for the reverse opclasses (not meant for inclusion to the system because I was making SQL functions that basically did - to use as the function of the operator class, but possibly worth playing with) but now that I actually search again, I don't see it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] query not using index for descending records?
Stephan Szabo <[EMAIL PROTECTED]> writes: > I'd thought that I'd previously sent a message containing a set of > definitions for the reverse opclasses (not meant for inclusion to the > system because I was making SQL functions that basically did - comparison function> to use as the function of the operator class, but > possibly worth playing with) but now that I actually search again, I don't > see it. I don't recall having seen such a thing go by... I fear that using SQL functions as comparators would only be useful for proof-of-concept, not as an industrial-strength implementation. The index code needs comparator functions not to leak memory, and I doubt that that could be guaranteed with a SQL function. You'd probably have speed issues too. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to retrieve N lines of a text field.
Chris Travers wrote:
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
How 'bout something like this:
CREATE OR REPLACE FUNCTION first_n_lines(text, int)
RETURNS setof text AS '
DECLARE
i int := 0;
oneline text;
BEGIN
LOOP
i := i + 1;
IF i > $2 THEN
EXIT;
END IF;
SELECT INTO oneline split_part($1, ''\n'', i);
IF oneline = THEN
EXIT;
END IF;
RETURN NEXT oneline;
END LOOP;
RETURN;
END
' LANGUAGE 'plpgsql';
regression=# select * from first_n_lines('abc\ndef\nghi', 2);
first_n_lines
---
abc
def
(2 rows)
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] query not using index for descending records?
On Thu, 29 Jan 2004, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > I'd thought that I'd previously sent a message containing a set of > > definitions for the reverse opclasses (not meant for inclusion to the > > system because I was making SQL functions that basically did - > comparison function> to use as the function of the operator class, but > > possibly worth playing with) but now that I actually search again, I don't > > see it. > > I don't recall having seen such a thing go by... > > I fear that using SQL functions as comparators would only be useful for > proof-of-concept, not as an industrial-strength implementation. The > index code needs comparator functions not to leak memory, and I doubt > that that could be guaranteed with a SQL function. You'd probably have > speed issues too. Yeah, that's what I figured. I thought it might be useful for people to play with though since at least for the integer/float types writing C versions of the comparitors is easy. I was thinking for real it'd be nice to be able to use the normal comparitor but invert the return value as necessary rather than providing two functions, but I didn't look at what that would take. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] query not using index for descending records?
Stephan Szabo <[EMAIL PROTECTED]> writes: > Yeah, that's what I figured. I thought it might be useful for people to > play with though since at least for the integer/float types writing C > versions of the comparitors is easy. I was thinking for real it'd be nice > to be able to use the normal comparitor but invert the return value as > necessary rather than providing two functions, but I didn't look at what > that would take. I think the C versions should be written to just call the "normal" comparators and negate the result, which'll make them one-liner boilerplate. It's just a matter of grinding out all that boilerplate ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] LEFT JOIN on one and/or another column (thanks)
Yes, Bruno. Thank you very much. That's what I was looking for, but since I hadn't used CASE nor COALESCE, I didn't know it was easier that way. The idea is that on a column I have info about a routine task, and in another one I have info about human-made changes to the time of that task, like assigning a different task, or moving it to another moment, and the query I'm trying to make should answer what tasks should be done now. Thank you. Bruno Wolff III said: > On Wed, Jan 28, 2004 at 20:27:00 -0800, > Octavio Alvarez <[EMAIL PROTECTED]> wrote: >> >> Hi. I have a table with two foreign keys (1 field each), like in >> >> id| serial >> ext_key_original | integer >> ext_key_exception | integer >> >> I'd like to join on the original, except when exception <> NULL, but I >> can't quite figure out how to do so. > > I am not completely sure from your description what exactly you are trying > to do, but it sounds like you can left join your table to the two foreign > key tables and then use coallesce to grab the value from the appropiate > joined table. -- Octavio Alvarez. E-mail: [EMAIL PROTECTED] Agradezco que sus correos sean enviados siempre a esta dirección. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of views
Bill, First off: discussion moved to the SQL list, where it really belongs. > Well, I would have agreed with the uselessness, until this project. The > "source of endless debugging" frightens me! Well, the last time I tried to use this capability was SQL Server 7. On that model, the problems I found were: 1) There was no good way to differentiate the recordsets returned; you had to keep careful track of what order they were in and put in "fillers" for recordsets that didn't get returned. 2) Most of the MS client technology (ODBC, ADO) was not prepared to handle multiple recordsets. I ended up hiring a C-based COM hacker to write me a custom replacement for ADO so that we could handle procedure results reliably. All in all, it wasn't worth it and if I had the project to do over again, I would have chosen a different approach. > > This can be done with Set Returning Functions. The issue is that the > > call to the function requires special syntax, and the program calling the > > function must know what columns are going to be returned at the time of > > the call. Hmmm, is that clear or confusing? > > Clear as mud. In my case, my application simply doesn't care what row of > what kind are returned. See, I'm writing the server end, and all said and > done, it's really just glue (frighteningly thick glue, but glue > nonetheless) To be clearer: You can create a Set Returning Function (SRF) without a clearly defined set of return columns, and just have it return "SETOF RECORD". However, when you *use* that function, the query you use to call it needs to have a clear idea of what columns will be returned, or you get no data. All of this is very hackneyed, as I'm sure you realize. Overall, I'd say that the programming team you've been inflicted with don't like relational databases, or at least have no understanding of them. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] java.lang.StringIndexOutOfBoundsException: String index
Mohan, > DEBUG] Transaction - -called safeRollback with null argument > java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported > at This is because "SET AUTOCOMMIT TO OFF" *is* no longer supported. Are you sure that you have the latest JDBC? Also, this should be on the PGSQL-JDBC mailing list, not this one. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] Set-Returning Functions WAS: On the performance of views
Bill, > I don't understand at all. If I do "SELECT * FROM > set_returning_function()" and all I'm going to do is iterate through the > columns and rows, adding them to a two dimensional array that will be > marshalled as a SOAP message, what about not knowing the nature of the > return set can cause me to get no data? Because that's not the syntax for a function that returns SETOF RECORD. The syntax is: SELECT * FROM set_returning_function(var1, var2) AS alias (col1 TYPE, col2 TYPE); That is, if the function definition does not contain a clear row structure, the query has to contain one. This does not apply to functions that are based on a table or composite type: CREATE FUNCTION RETURNS SETOF table1 ... CREATE FUNCTION RETURNS SETOF comp_type Can be called with: SELECT * FROM some_function(var1, var2) as alias; What this means is that you have to know the structure of the result set, either at function creation time or at function execution time. > > One of the things I love about working with open source databases is I > don't see a lot of that. The people on these lists are almost always > smarter than me, and I find that comforting ;) Flattery will get you everywhere. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
You can also use PGexecParams() (see the libpq documentation). It can be a little more cumbersome to use, though. Best Wishes, Chris Travers - Original Message - From: MUKTA To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 8:08 PM Subject: [SQL] Hi I have an urgent problem I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i can not insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); executing above statement with plain integers does fine and inserts them into table.. Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx
Re: [SQL] limit 1 and functional indexes
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> >QUERY PLAN
> >
> > Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17
> > rows=0 loops=1)
> >Sort Key: order_date
> >-> Index Scan using transactions_pop_i on transactions
> > (cost=0.00..11653.79 rows=2956 width=33)
> > (actual time=126.13..126.13 rows=0 loops=1)
> > Index Cond: (upper((pop)::text) =
> > '79BCDC8A4A4F99E7C111'::text)
> > Total runtime: 248.25 msec
Yeah, the problem with functional indexes is that the optimizer doesn't have
any clue how the records are distributed since it only has statistics for
columns, not your expression. Notice it's estimating 2956 rows where in fact
there are 0.
I think someone was actually working on this so it may be improved in 7.5 but
I'm not sure.
Given the type of data you're storing, which looks like hex strings, are you
sure you need to do a case-insensitive search here? Can't you just uppercase
it when you store it?
The other option would be to use a subquery and force the planner not to pull
it up, something like:
select code
from (
select code
from transactions
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111')
offset 0
)
order by order_date DESC;
The offset 0 prevents the optimizer from pulling the subquery into the outer
query. I think this will prevent it from even considering the order_date index
scan, but you'll have to try to be sure.
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] java.lang.StringIndexOutOfBoundsException: String index
Now i a get another wierd error DEBUG] Transaction - -called safeRollback with null argument java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:482) at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:461) at org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit(AbstractJdbc1Connection.java:957) They say its a driver problem i think i am having the right driver. I am having RedHat Linux and postgres7.4 and pg73jdbc3.jar and jdbc-2.0.jar in my class path. I donot understand what is going wrong but in my server logs i also see this error for torque [DEBUG] SharedPoolDataSourceFactory - -Starting initJdbc2Pool [DEBUG] TorqueInstance - -getConfiguration() = [EMAIL PROTECTED] [DEBUG] AbstractDataSourceFactory - -applyConfiguration(null, [EMAIL PROTECTED]) [DEBUG] AbstractDataSourceFactory - -applyConfiguration([EMAIL PROTECTED], [EMAIL PROTECTED]) [DEBUG] ConvertUtils - -Convert string 'SELECT 1' to class 'java.lang.String' [ERROR] AbstractDataSourceFactory - -Property: validationQuery value: SELECT 1 is not supported by DataSource: org.apache.commons.dbcp.datasources.SharedPoolDataSource [ERROR] AbstractDataSourceFactory - -Property: defaultMaxActive value: 16 is not supported by DataSource: org.apache.commons.dbcp.datasources.SharedPoolDataSource please let me know if any has an idea Thanks in Advance --Mohan > O kyrios [EMAIL PROTECTED] egrapse stis Jan 29, 2004 : > >> Hi All >> >> I am having the torque3.1.jar and postgresql-7.4. I have compiled the >> new jdbc driver called as postgresql.jar and have placed it in the lib >> directory from where the ant scripts catch the jars. Whenever i try to >> access through torque >> >> gestList = BaseGestlistPeer.doSelect(new Criteria()); >> >> this error arises >> >> java.lang.StringIndexOutOfBoundsException: String index out of range: >> 23 >> at java.lang.String.charAt(String.java:460) >> at >> org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) >> at >> org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) > > Just from the above, some one could conclude you are running > a 7.2.x postgresql driver. > > Make sure you havent messed up with the CLASSPATH. > Also after ensuring your 7.4.1 postgresql.jar is in the right location, > make sure you are running a 7.4.1 backend. > >> >> I have searched all the lists and the answer i got is a jdbc driver >> issue. Please let me know what i need to for getting around with this >> problem. This happens only with this method in the whole application. >> But the same method works when i did not use the new torque 3.1 and >> everything happens smoothly.Please let me know if there is a way to >> configure jdbc with torque or any other configuration issue for >> postgres you know might have been creating this problem. I am having >> one heck of a night mare wid this issue. >> >> >> thanks >> >> --Mohan >> >> >> >> ---(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 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] java.lang.StringIndexOutOfBoundsException: String index out of range: 23 at java.lang.String.charAt(String.java:460)
Hi All I am having the torque3.1.jar and postgresql-7.4. I have compiled the new jdbc driver called as postgresql.jar and have placed it in the lib directory from where the ant scripts catch the jars. Whenever i try to access through torque gestList = BaseGestlistPeer.doSelect(new Criteria()); this error arises java.lang.StringIndexOutOfBoundsException: String index out of range: 23 at java.lang.String.charAt(String.java:460) at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) I have searched all the lists and the answer i got is a jdbc driver issue. Please let me know what i need to for getting around with this problem. This happens only with this method in the whole application. But the same method works when i did not use the new torque 3.1 and everything happens smoothly.Please let me know if there is a way to configure jdbc with torque or any other configuration issue for postgres you know might have been creating this problem. I am having one heck of a night mare wid this issue. thanks --Mohan ---(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] java.lang.StringIndexOutOfBoundsException: String index
O kyrios [EMAIL PROTECTED] egrapse stis Jan 29, 2004 : > Hi All > > I am having the torque3.1.jar and postgresql-7.4. I have compiled the new > jdbc driver called as postgresql.jar and have placed it in the lib > directory from where the ant scripts catch the jars. Whenever i try to > access through torque > > gestList = BaseGestlistPeer.doSelect(new Criteria()); > > this error arises > > java.lang.StringIndexOutOfBoundsException: String index out of range: 23 > at java.lang.String.charAt(String.java:460) > at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1653) > at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398) Just from the above, some one could conclude you are running a 7.2.x postgresql driver. Make sure you havent messed up with the CLASSPATH. Also after ensuring your 7.4.1 postgresql.jar is in the right location, make sure you are running a 7.4.1 backend. > > I have searched all the lists and the answer i got is a jdbc driver issue. > Please let me know what i need to for getting around with this problem. > This happens only with this method in the whole application. But the same > method works when i did not use the new torque 3.1 and everything happens > smoothly.Please let me know if there is a way to configure jdbc with > torque or any other configuration issue for postgres you know might have > been creating this problem. I am having one heck of a night mare wid this > issue. > > > thanks > > --Mohan > > > > ---(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 8: explain analyze is your friend
