Re: [SQL] Conversion
On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote: > Is the int4 a UNIX epoch? ie. seconds since 1970? > > If so, then this will generally work: > > SELECT CAST(int4field AS abstime); > > or > > SELECT int4field::abstime; http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: The types abstime and reltime are lower precision types which are used internally. You are discouraged from using any of these types in new applications and are encouraged to move any old ones over when appropriate. Any or all of these internal types might disappear in a future release. Don't they? -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Conversion
On Tue, Aug 13, 2002 at 03:14:38PM +0800, Christopher Kings-Lynne wrote: > > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: > > > > The types abstime and reltime are lower precision types which are used > > internally. You are discouraged from using any of these types in new > > applications and are encouraged to move any old ones over when > > appropriate. Any or all of these internal types might disappear in a > > future release. > > Yes, but in absence of: > > SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234'); Sounds nice :) > (Hint Hint Thomas!!!) > > It's all he can do. I suggest using the syntax above to convert his integer > column to a timestamp column. Sure. I use the same. But I don't like it because of that caution :( The other way is SELECT 'epoch'::timestamp + (int4field::text || 's')::interval, but it's much much slower... And it seems not to handle timestamps after 2038-01-19. -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Results from EXECUTE
How can I obtain results from an EXECUTE statement within a pl/PgSQL function? E.g., something like value := EXECUTE ''SELECT '' || quote_ident(field_name) || '' FROM ...''; Thanks in advice. -- Fduch M. Pravking ---(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] Different size in the DATA directory
On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote: > Tim, I guess your problem is dead index pages that can't be reclaimed, > and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a > /contrib/reindexdb script in 7.3. As I see, contrib/reindexdb requires perl for commandline procesing. I don't think it's a good idea, since e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. Thomas, why not to use sed? -- Fduch M. Pravking ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Different size in the DATA directory
On Sat, Nov 02, 2002 at 08:20:04PM -0500, Bruce Momjian wrote: > Alexander M. Pravking wrote: > > As I see, contrib/reindexdb requires perl for commandline > > procesing. I don't think it's a good idea, since > > e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. > > > > Thomas, why not to use sed? > > No perl? I am no perl guy, but I assumed everyone had that already. > > I just looked at the code, and yes, it should use sed rather than perl, > especially since it is using it just for processing command line args. > > Seems it is a problem/bug for you. Patch applied to use sed rather than > perl. Well, I DO use perl, so it's not a problem for me :) But I see many people on these lists that do not. Thanks, Bruce. -- Fduch M. Pravking ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problem with looping on a table function result
I've met the following problem.
I had successfully written a function divide_into_days(timestamp, timestamp)
which returns setof (timestamp, timestamp) pairs - a list of days the
given interval is divided into.
What I want is to use each record from resultset to pass to another
function, something like:
SELECT days.*, summary_stats(days.day_start, days.day_end)
FROMdivide_into_days('2003-06-01', '2003-07-01') days;
The problem is that summary_stats function returns a record, so I have
to use SELECT * FROM summary_stats(...). I can't use the following too:
SELECT *
FROMsummary_stats(days.day_start, days.day_end) stats,
divide_into_days('2003-06-01', '2003-07-01') days;
(there was a discussion a few days ago about using subselects,
but here's a slightly different case).
I wonder if where's a way to do the trick without writing one more
PL/PgSQL table function doing FOR row IN SELECT ... LOOP or using
client-side loop?
Thanks for your help.
--
Fduch M. Pravking
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Expression transformation curiosity
On Sun, Aug 17, 2003 at 11:32:58AM +0100, Richard Huxton wrote: > I just had a look at EXPLAIN ANALYSE output for an SQL function I'm trying to > write. The WHERE clause wasn't too complex, but the explain output took up a > dozen lines. > > Boiling the problem down, I've looked at a clause of the form: > a OR (b AND c) > which PG converts to: > (a OR b) AND (a OR c) > > Now these two are equivalent, but it would take me forever to demonstrate that > with the full query. I'm happy the planner is going to get it right, but I'm > confused as to why the transformation occurs. > > Is it an artefact of displaying the EXPLAIN, or is it actually processed that > way? You could see how testing "a" twice could be expensive in some > situations. Looks like it actually works this way. I had the same problem several weeks ago on 7.3.3 with 4 such OR's. The final filter became monsterous, and the query was very slow. I've simply rewritten the query using UNION, and it became much faster. -- Fduch M. Pravking ---(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] Backup of multiple tables
On Fri, Sep 19, 2003 at 01:54:01PM +0200, Andreas Joseph Krogh wrote: > I usually backup my database with pg_backup without the -t option. But now I > need to only backup certain tables(say tab1 and tab2), is this possible with > pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success. Here's a perl script I used to dump all the tables separately. I'm not sure most of options do work there, I didn't test ;-) It won't be hard to make it dump certain tables, I think. -- Fduch M. Pravking dump.pl Description: Perl program ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Backup of multiple tables
On Fri, Sep 19, 2003 at 04:30:57PM +0200, Andreas Joseph Krogh wrote: > > Here's a perl script I used to dump all the tables separately. > > I'm not sure most of options do work there, I didn't test ;-) > > It won't be hard to make it dump certain tables, I think. > > Thanks for your suggestion, but the problem with it is that I may end up with > inconsistencies if data is inserted/updated or deleted in one of the tables > during the backup, so I would miss the "snapshot"-effect. You can try to explicitly lock all tables being dumped from the script before and release them after dump is complete... But there could be dead-lock conditions. What will gurus say? -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] STRICT function returning a composite type
I noted that such a function returns an empty rowset if a NULL value is
passed as an argument. Is it a bug or feature? I wish it was a feature,
because I probably want to use this behavour.
Here's an example:
CREATE TYPE ts_bounds AS (
sdate timestamptz,
edate timestamptz
);
CREATE OR REPLACE FUNCTION overlap_bounds(timestamptz, timestamptz, timestamptz,
timestamptz)
RETURNS ts_bounds AS '
DECLARE
sdate1 ALIAS FOR $1;
edate1 ALIAS FOR $2;
sdate2 ALIAS FOR $3;
edate2 ALIAS FOR $4;
res ts_bounds%rowtype;
BEGIN
res.sdate := CASE WHEN sdate1 > sdate2 THEN sdate1 ELSE sdate2 END;
res.edate := CASE WHEN edate1 < edate2 THEN edate1 ELSE edate2 END;
IF res.sdate > res.edate THEN
res.sdate := NULL;
res.edate := NULL;
END IF;
RETURN res;
END' LANGUAGE 'plPgSQL' STRICT;
fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', 'infinity');
sdate | edate
+
2003-11-12 00:00:00+03 | 2003-11-13 00:00:00+03
(1 row)
fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', null);
sdate | edate
---+---
(0 rows)
What I want is to get no rows if given intervals don't overlap instead of:
fduch=# SELECT * from overlap_bounds('-infinity', 'yesterday', 'today', 'infinity');
sdate | edate
---+---
|
(1 row)
Is it possible without returning SETOF ts_bounds?
fduch=# SELECT version();
version
-
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 05:14:27PM +, Richard Huxton wrote: > RETURNS NULL ON NULL INPUT or STRICT indicates that the function always > returns NULL whenever any of its arguments are NULL. If this parameter is > specified, the function is not executed when there are NULL arguments; > instead a NULL result is assumed automatically. Does "NULL result" mean an empty rowset if the function returns a record? -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 12:27:58PM -0500, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > Does "NULL result" mean an empty rowset if the function returns a record? > > No, it means a null record. "Empty rowset" would apply to a function > declared to return SETOF something. (I believe that is how we > interpret the concept of strictness for functions returning sets.) Very well then... Can I return a null record from such function explicitly? Sorry, I could't find it anywhere in docs or examples. -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 12:35:41PM -0500, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > Very well then... Can I return a null record from such function > > explicitly? Sorry, I could't find it anywhere in docs or examples. > > Not sure. Seems like you should be able to, but I've never tried it. Thanks for a quick response, guys. I'll try to find it myself and will let you know if I did ;) -- Fduch M. Pravking ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Validity check in to_date?
I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
to_date
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
to_date
2007-01-03
to_timestamp() seems to work the same way. It's probably useful sometimes,
but not in my case... Is it how it supposed to work?
If so, how can I do such a validity check?
If not, has something changed in 7.4?
In any case, I have to find a workaround now and will appreciate any help.
fduch=# SELECT version();
version
-
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote: > As far as I know these results are correct in terms of the underlying > C-library function mktime(). This function is intended to be used when > adding/subtracting intervals from a given timestamp. Which one? mktime() or to_date()? I'm not sure it's handy to use to_date() for any calculations, so I'm surprised why doesn't it work just as date_in() do. > I don't know of any postgres function doing the check you're looking for. Yes, the only thing I could think now is to do something like s/([0-9]+)\.([0-9]+)\.([0-9]+)/\3-\2-\1/ and then pass it to CAST(... AS date) using ISO DateStyle. (I could simply use German DateStyle in case of DD.MM., but I deal with several date formats, e.g. DD/MM/.) > But I can't believe this is the first time this topic is brought up. > You may search the archives on "date plausibility" are related terms. I'm sure too, but it's really hard to find a good keyword sequence when searching such sort of things :( Anyway, thank you for attention. -- Fduch M. Pravking ---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:57:14AM +0100, Karel Zak wrote: > > > If not, has something changed in 7.4? > > No change in 7.4. Maybe in 7.5 or in some 7.4.x. Well, let's see. > The others PostgreSQL stuff which full parse (means check ranges) > date/time is less optimistic with this: > > # select '31.11.2003'::date; > ERROR: date/time field value out of range: "31.11.2003" Exactly! But date_in formats are too limited and "floaty", especially in 7.3 or less. -- Fduch M. Pravking ---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 07:44:54PM +0900, Iain wrote: > T've been following this thread with interest because I have a related > problem. Basically we are storing dates in CHAR fields with al the > associated problems. I'd like to do it, but changing everything to date > fields isn't practical for now, so as a stopgap solution, I want to provide > some validation at the database level. > > I tried: > > create domain ymdtest2 as char(10) constraint valid_date check > (VALUE::DATE); There's no conversion function from char(n) to date, but there's one from text to date. Try using check (VALUE::text::date). -- Fduch M. Pravking ---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 01:55:06PM +0300, Alexander M. Pravking wrote: > Try using check (VALUE::text::date). Assuming check expects boolean result, it's (VALUE::text::date IS NOT NULL) -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problem with intervals
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
>
> 2003-10-26
> (1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)
> When I get rid of the date cast it becomes clear what is happening:
>
> select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
> ?column?
>
> 2003-10-26 23:00:00-08
> (1 row)
>
> Is this a Postgres bug, or is this correct SQL behavior? I'm running
> Postgres 7.2.2.
It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.
Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".
--
Fduch M. Pravking
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Row count after SELECT INTO?
Dear SQL and plPgSQL gurus, I seek for your wisdom. I have a variable assignment via SELECT INTO in my function, and I want to separate 3 cases: 1) no rows found; 2) one row found; 3) many rows found (ambiguous select). The first case is simple, I use FOUND variable for it. But how about the third? I tried to GET DIAGNOSTICS nrows = ROW_COUNT, bit it seems to get only value of 0 or 1. Is this how it supposed to be when SELECTing INTO a single variable? The only way I see now is a FOR ... IN SELECT loop, and I woner if there is a simpler solution. Could you please help me? -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Memory usage on subselect
Hello, PostgreSQL users and developers. I've got a memory usage problem when I try to do a subselect on the same table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's my query: SELECT sreq(s1.id, 'ipacct_ip', now()), s1.* FROMservices s1 WHERE EXISTS ( SELECT 1 FROMservices s2 WHERE s2.id != s1.id AND sreq(s2.id, 'ipacct_ip', now()) = sreq(s1.id, 'ipacct_ip', now()) AND s2.sdate < now() AND s2.edate > now() ) AND s1.sdate < now() AND s1.edate > now(); I.e. I want to find all records from services which have equal values of sreq(...) for them (additionally filtering only those which are actual now). The "services" table is indexed only on "id" column and has about a thousand tuples. sreq(integer, text, timestamptz) is a strict immutable function written in SQL. EXPLAIN says the following: Seq Scan on services s1 (cost=0.00..38628.80 rows=38 width=55) Filter: ((sdate < now()) AND (edate > now()) AND (subplan)) SubPlan -> Seq Scan on services s2 (cost=0.00..56.08 rows=1 width=0) Filter: ((id <> $0) AND (sreq(id, 'ipacct_ip'::text, now()) = sreq($0, 'ipacct_ip'::text, now())) AND (sdate < now()) AND (edate > now())) I see no evil here (of course, the query is going to be slow), but the postgres process begins to consume a lot of memory (I cancelled a query after ~500M). Am I doing something wrong or is it expected behavour? I never seen this before, so I'd think it's me who mistaken, but I can't find anything wrong for a few hours :) Here's subquerie's EXPLAIN ANALYZE for a sample (existing) s1.id: EXPLAIN ANALYZE SELECT 1 FROMservices s2 WHERE s2.id != 561 AND sreq(s2.id, 'ipacct_ip', now()) = sreq(561, 'ipacct_ip', now()) AND s2.sdate < now() AND s2.edate > now(); Seq Scan on services s2 (cost=0.00..56.08 rows=1 width=0) (actual time=177.01..177.01 rows=0 loops=1) Filter: ((id <> 561) AND (sreq(id, 'ipacct_ip'::text, now()) = sreq(561, 'ipacct_ip'::text, now())) AND (sdate < now()) AND (edate > now())) Total runtime: 177.05 msec I can provide other details, if needed. Thanks in advance. -- Fduch M. Pravking ---(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] Memory usage on subselect
On Sun, May 23, 2004 at 12:28:18PM -0400, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > I've got a memory usage problem when I try to do a subselect on the same > > table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's my query: > > ... > > The "services" table is indexed only on "id" column and has about a > > thousand tuples. sreq(integer, text, timestamptz) is a strict immutable > > function written in SQL. > > IIRC, there were intraquery memory leaks associated with SQL-language > functions until fairly recently. Can you try your problem case on 7.4? > Or see if you can rewrite the sreq function in plpgsql. Thanks, Tom, both 7.4.1 and plpgsql function on 7.3.4 work well. However, plpgsql function is a little slower, but that's another story. I also tried to turn off IMMUTABLE for the function, but it did not affect memory usage on 7.3.4. BTW, after cancelling the original query postgres freed all the memory, and used ~7M again, so the leak was not "forever". Good luck, thansk again ;) -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: RES: [SQL] Datetime problem
On Mon, Jun 14, 2004 at 01:20:14PM -0300, Eric Lemes wrote:
> Hello,
>
> - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
> - Timezone: Brazil (GMT-3, I think).
What's about daylight saving time for you?
I'm almost sure the DST boundary is near the date in your example.
However, with 7.3.4 on FreeBSD I get:
fduch=# SHOW TimeZone ;
TimeZone
---
Europe/Moscow
(1 row)
fduch=# SELECT to_timestamp('2004 10 31 00 00 00', ' MM DD HH MI SS');
to_timestamp
2004-10-31 00:00:00+04
(1 row)
fduch=# SELECT to_timestamp('2004 11 01 00 00 00', ' MM DD HH MI SS');
to_timestamp
2004-11-01 00:00:00+03
(1 row)
So both timestamps before and after boundary are parsed well for me.
> I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
> result is Ok. But I don't know how to work with time zones correctly.
>
> When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Hmm, 7.3 and 7.4 docs say that it returns timestamp (WITHOUT time zone
is default since 7.3 IIRC), but in fact it accepts and returns timestamp
WITH time zone. This is probably a documentation bug...
--
Fduch M. Pravking
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote: > Now you treat NULLs as false. Nope. NULL is neither true, nor false. It's "unknown", or "undefined". fduch=# SELECT 1 WHERE NULL::boolean; ?column? -- (0 rows) fduch=# SELECT 1 WHERE NOT NULL::boolean; ?column? -- (0 rows) So if you care, you SHOULD use IS [NOT] NULL, as Michael Glaesemann suggested. If you don't want expression to be calculated twice, use a temporary variable. -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] UPDATE ... WHERE (subselect on the same table)
I was looking for how can I give an alias for the table being updated (something like UPDATE table_name table_alias SET ...), but the current syntax doesn't allow that. What I need is to: fduch=# SELECT * from test order by typ, name; typ | name | x -+--+--- 1 | bar | 1 | baz | 1 | foo | 2 | baz | 2 | foo | (5 rows) fduch=# UPDATE test SET x = 't' fduch-# where typ = 1 and exists ( fduch(# SELECT 1 from test t2 fduch(# where t2.typ = 2 and t2.name = test.name fduch(# ); UPDATE 2 fduch=# SELECT * from test order by typ, name; typ | name | x -+--+--- 1 | bar | 1 | baz | t 1 | foo | t 2 | baz | 2 | foo | (5 rows) So I have two questions: Q1, cognitive. Why the alias for the updated table is restricted? Is there any reason for that or it's just not implemented? Q2, vital. Can I be sure that the syntax I used here will work correctly, i.e. will the "test.name" always refer the column in outer table, not inner (t2)? Thanks in advance. -- Fduch M. Pravking ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UPDATE ... WHERE (subselect on the same table)
On Tue, Jun 29, 2004 at 12:49:55PM -0400, Tom Lane wrote: > > So I have two questions: > > Q1, cognitive. Why the alias for the updated table is restricted? > > Because the SQL standard doesn't allow an alias there. We've talked > about allowing one anyway, but no one's gotten around to it. AFAICS > it would only be a marginal notational advantage, not allow you to > express queries you can't express today. > > > Q2, vital. Can I be sure that the syntax I used here will work > > correctly, i.e. will the "test.name" always refer the column in outer > > table, not inner (t2)? > > Yes. The alias *completely* hides the real name of that table > reference, so "test" will never refer to "test t2". As always, perfectly clear, thank you Tom :) I already found in docs on SELECT: When an alias is provided, it completely hides the actual name of the table or table function; /me should RTFM... (repeating hundred times) -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Indexable (constant LIKE prefix_keyfield) ?
I was impressed that LIKE operator can be indexed in 7.4 with non-C
locale. But how about the reverse condition?
What I need is to:
SELECT * FROM prefixes WHERE 'literal' LIKE prefix || '%';
or
SELECT * FROM prefixes WHERE 'literal' ~ ('^' || prefix);
Prefix is of type text (variable-length), which may contain only ASCII
chars (database runs under ru_RU.KOI8-R locale). Only the longest prefix
should be taken if more than one matches.
There's no strict definition for "prefixes" yet, and I seek for how to
make it possible to use an index by this query. The ways I see:
1. Sequentially rtrim('literal') and compare it to prefix.
Really bad idea.
2. Use 2 fields: prefix_le and prefix_gt, then
'literal' >= prefix_le AND 'literal' < prefix_gt
(or 'literal' ~>=~ prefix_le AND 'literal' ~<~ prefix_gt, but it
seems there's no need to).
a) supply both fields from outside (I don't like this idea).
b) supply only prefix (=prefix_le), and calculate prefix_gt (using
trigger?) as prefix_le "plus one".
Digging the backend sources, I've found make_greater_string used
to expand indexable LIKE or regexp condition. Can I use it for my
needs somehow? Or have I to write my own in this case?
3. Create some magical index I dunno about :)
4. SELECT * FROM prefixes
WHERE prefix <= 'literal' AND 'literal' LIKE prefix || '%'
ORDER BY prefix DESC LIMIT 1;
Looks like the best way, but I'm not sure this is always correct.
Comments, suggestions, please?
--
Fduch M. Pravking
---(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] Problem in age on a dates interval
On Mon, Jul 19, 2004 at 10:00:50AM +0100, Luis Sousa wrote: > I worked around this problem returning the difference between the two > dates, using extract doy from both. > Anyway, this will cause a bug on my code when changing the year. Any ideas? Why don't you use the minus operator? SELECT '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp; ?column? -- 86 days Or, if you need the age just in days: SELECT extract(day from '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp); date_part --- 86 or SELECT '2004-05-14 16:00'::date - '2004-02-18 16:00'::date; ?column? -- 86 Note that '2004-05-14 16:00'::date is actually '2004-05-14 00:00'::date, so the last two are not always equal. > Tom Lane wrote: > > >Theodore Petrosky <[EMAIL PROTECTED]> writes: > > > > > >>wow at first I thought I had my head around a leap > >>year problem so I advanced your query a year > >> > >> > > > >I think what's going on here is a difference of interpretation about > >whether an "M months D days" interval means to add the months first > >or the days first. For instance > > > >2005-02-18 plus 2 months = 2005-04-18, plus 24 days = 2005-05-12 > > > >2005-02-18 plus 24 days = 2005-03-14, plus 2 months = 2005-05-14 > > > >The timestamp-plus-interval operator is evidently doing addition the > >first way, but it looks like age() is calculating the difference in a > >way that implicitly corresponds to the second way. > > > >I have some vague recollection that this has come up before, but > >I don't recall whether we concluded that age() needs to be changed > >or not. In any case it's not risen to the top of anyone's to-do list, > >because I see that age() still acts this way in CVS tip. > > > > regards, tom lane -- Fduch M. Pravking ---(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] CHECK col A not NULL if col B='x'
On Tue, Sep 28, 2004 at 03:02:02PM +0100, T E Schmitz wrote: > Hello, > > Is it possible to set up a table CHECK, which ensures that column A is > NOT NULL if column B = 'x' ? Sure. [EMAIL PROTECTED] CREATE TABLE test ( [EMAIL PROTECTED](# a integer check (case when b = 'x' then a is not null else true end), [EMAIL PROTECTED](# b text); CREATE TABLE [EMAIL PROTECTED] INSERT INTO test VALUES (null, '123'); INSERT 107538 1 [EMAIL PROTECTED] INSERT INTO test VALUES (null, 'x'); ERROR: new row for relation "test" violates check constraint "test_a" [EMAIL PROTECTED] INSERT INTO test VALUES (1, 'x'); INSERT 107539 1 -- Fduch M. Pravking ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] TEXT::CIDR/INET::CIDR output confusion
It looks a bit strange that CIDR output depends on datatype it has been casted from: fduch=# SELECT '1.1.1.1'::cidr; cidr 1.1.1.1/32 (1 row) fduch=# SELECT '1.1.1.1'::inet::cidr; cidr - 1.1.1.1 (1 row) However these two seem to be 'equal' in terms of backend: fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::inet; ?column? -- t (1 row) fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::cidr; ?column? -- t (1 row) I'm just curious how can it even be... fduch=# SELECT version(); version PostgreSQL 7.4.5 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
