[SQL] problem with select where like ']'
hi, i have a table (view actually) which contains field "article_name" which is written like: [AGD]|[Kuchenki Mikrofalowe]|[Samsung AKMS1] i.e. some string within "[]" delimited by "|" i want to select all record that have "[AGD]|[" in front of them so i tried select * from my_view where article_name like '[AGD]|['; this doesn't work. no errors, but no tuples returned. i checked that the problem is character ']'. '[' works o.k. '|' - i guess works too. but inserting "]" makes the whole expression doesn't work i tried escaping of it like \], \\] or even \\\] but none of these worked. right now i'm using comparison: where substring (...) = '...' and it works even with those "]" signs. but i'm just wondering why like ']' doesn't work. any ideas? depesz p.s. sorry for my english. -- hubert depesz lubaczewski <=--=> adres www usuniêty na pro¶bê Asi ¦l. najwspanialsz± rzecz± jak± da³o nam nowoczesne spo³eczeñstwo, jest niesamowita wrêcz ³atwo¶æ unikania kontaktów z nim ...
Re: [SQL] Synonyms
On Mon, Oct 30, 2000 at 01:17:24PM +, Carl Shelbourne wrote: > * Is there a way of creating synonyms of tables within one database in another > database in Postgres similar to the Informix CREATE SYNONYM? > * If not and related to the post on 2000-10-28 by Sivagami, is there a way, > within a select I can make a query from mulitple databases. ( the usual > DB.Table or DB:table or DB@table dont seem to work) don't know how informix works, but why dont you just use a view? create view synonym as select * from table; am i missing something? depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
[SQL] Time penalty on VIEWS on VIEWS
Hi, My situation: VIEW A is grouping information from 2 tables (B,C). I have to make another VIEW with additional WHERE statement compared in VIEW A. I can make this as a VIEW groupping tables B and C, or makeing VIEW of VIEW A. I know that second sollution is more flexible but there should be some kind of time penalty. I'm wondering how much it is. regards, depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
[SQL] Question about ordering views
Hi, I'm wondering if it is possible to make view (or procedure or triger or rule or something) to force SELECTs to this view (rule, procedure or something) to force always using predefined ORDER BY statement without removing possibility to use WHERE statement to this SELECT. regards, depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
[SQL] how to write it in most efficient way?
hi. i have database with two tables like this:
database=> \d groups
Table "groups"
Attribute | Type | Modifier
---+-+--
id| integer | not null default nextval('groups_seq'::text)
parent_id | integer | not null default 0
image_id | integer | not null default 0
name | text| not null default ''
database=> \d g_order
Table "g_order"
Attribute | Type | Modifier
---+-+---
id| integer | not null default nextval('g_order_seq'::text)
group_id | integer | not null default 0
data inside are (for test purposes):
database=> select * from groups;
id | parent_id | image_id | name
+---+--+--
0 | 0 |0 |
1 | 0 |0 | RTV
2 | 0 |0 | AGD
3 | 0 |0 | MP3
4 | 1 |0 | Audio
5 | 2 |0 | Lodwki
6 | 2 |0 | Kuchenki Mikrofalowe
7 | 4 |0 | Sony
8 | 4 |0 | Panasonic
(9 rows)
database=> select * from g_order;
id | group_id
+--
1 |2
2 |6
3 |5
4 |3
5 |1
6 |4
7 |8
8 |7
(8 rows)
the table g_order allows me to change order of displaying groups without changing
main groups table. just like this:
database=> select g.id, getgrouppath(g.id,'/') from groups g, g_order o where
g.id = o.group_id order by o.id;
id | getgrouppath
+--
2 | AGD
6 | AGD/Kuchenki Mikrofalowe
5 | AGD/Lodwki
3 | MP3
1 | RTV
4 | RTV/Audio
8 | RTV/Audio/Panasonic
7 | RTV/Audio/Sony
(8 rows)
o.k. and now comes my problem:
i need to know which group (groups.id) is first (after ordering) subgroup of
group ... for example 4 (rtv/audio).
i'm doing it now with:
SELECT
go.group_id
FROM
g_order go
WHERE
go.id = (
SELECT
min(o.id)
FROM
groups g,
g_order o
WHERE
g.id = o.group_id and
g.parent_id=4 and
g.id <> 0
)
;
but i feel that there should be a better/faster way to do it.
my tables have primary keys, foreign key (groups.id <=> g_order.group_id),
indices.
any idea how to write a better select to do what i need? or maybe the one i
wrote is the best one?
depesz
--
hubert depesz lubaczewski
najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [SQL] subselects
On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote: > I tried to do this: > SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep > WHERE m IN(190); why dont you use simple join? like: select rep.r, rep.a, rep.cdate, dir.u, dir.re from rep, dir where rep.m in (190) and rep.a = dir.u; this should (i guess) work perfectly depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [SQL] How to represent a tree-structure in a relational database
somebody already showed table structure, but i'll ad some more code to this:
table:
CREATE TABLE groups (
id INT4 NOT NULL DEFAULT NEXTVAL('groups_seq'),
parent_idINT4 NOT NULL DEFAULT 0,
name TEXT NOT NULL DEFAULT '',
active BOOL NOT NULL DEFAULT 't'::bool,
PRIMARY KEY (id)
);
INSERT INTO groups (id) VALUES (0);
ALTER TABLE groups ADD FOREIGN KEY (parent_id ) REFERENCES groups (id);
CREATE UNIQUE INDEX groups_pn_u ON groups (parent_id, name, active);
at this point it seems to be pretty easy and obvious.
in my case i got to the point that i needed some more info about the branch of
tree. so i wrote:
REATE function getgrouppath(int4, text) returns text as '
DECLARE
sep ALIAS FOR $2;
aid int4;
wynik TEXT;
temp RECORD;
b BOOL;
BEGIN
b:=''t'';
wynik:='''';
aid:=$1;
while b loop
SELECT name, parent_id INTO temp FROM groups WHERE id=aid;
IF NOT FOUND THEN
return wynik;
END IF;
if wynik = '''' THEN
wynik:=temp.name;
else
wynik:=temp.name||sep||wynik;
END if;
IF temp.parent_id = 0 THEN
b:=''f'';
ELSE
aid:=temp.parent_id;
END if;
end loop;
return wynik;
END;
' language 'plpgsql';
(sorry for polish variable names)
this function does one nice trick
when having structure like:
=> select id, parent_id, name, active from groups;
id | parent_id | name | active
+---+--+
0 | 0 | | t
1 | 0 | RTV | t
2 | 0 | AGD | t
3 | 0 | MP3 | t
4 | 1 | Audio| t
5 | 2 | Lodówki | t
6 | 2 | Kuchenki Mikrofalowe | t
7 | 4 | Sony | t
8 | 4 | Panasonic| t
(9 rows)
i can:
=> select id, parent_id, name, active, getgrouppath(id, '/') from
groups;
id | parent_id | name | active | getgrouppath
+---+--++--
0 | 0 | | t |
1 | 0 | RTV | t | RTV
2 | 0 | AGD | t | AGD
3 | 0 | MP3 | t | MP3
4 | 1 | Audio| t | RTV/Audio
5 | 2 | Lodówki | t | AGD/Lodówki
6 | 2 | Kuchenki Mikrofalowe | t | AGD/Kuchenki Mikrofalowe
7 | 4 | Sony | t | RTV/Audio/Sony
8 | 4 | Panasonic| t | RTV/Audio/Panasonic
since for some reasons (indenting) i needed the level of branch i wrote:
CREATE FUNCTION grouplevel(int4) returns int4 AS '
DECLARE
baseid ALIAS FOR $1;
currid INT4;
reply INT4;
BEGIN
reply:=1;
if baseid = 0 then return 0; END if;
SELECT parent_id INTO currid FROM groups where id=baseid;
while currid>0 loop
reply:=reply+1;
SELECT parent_id INTO currid FROM groups where id=currid;
END loop;
return reply;
END;
' language 'plpgsql';
which also seems pretty obvious.
to be complete i wrote two triggers which made me happy:
CREATE FUNCTION trg_recurs_act_g() RETURNS OPAQUE AS '
BEGIN
IF NEW.active=''f''::bool and OLD.active=''t''::bool THEN
UPDATE articles SET active=''f''::bool WHERE group_id=NEW.id;
UPDATE groups SET active=''f''::bool WHERE parent_id=NEW.id and id<>0;
ELSE
IF NEW.active=''t''::bool and OLD.active=''f''::bool AND NEW.id<>0 THEN
UPDATE groups SET active=''t''::bool WHERE id=NEW.parent_id;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION trg_recurs_act_a() RETURNS OPAQUE AS '
BEGIN
IF NEW.active=''t''::bool and OLD.active=''f''::bool THEN
UPDATE groups SET active=''t''::bool WHERE id=NEW.group_id;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER groups_update_trg BEFORE UPDATE ON groups FOR EACH ROW EXECUTE
PROCEDURE trg_recurs_act_g();
CREATE TRIGGER articles_update_trg BEFORE UPDATE ON articles FOR EACH ROW EXECUTE
PROCEDURE trg_recurs_act_a();
as you can see those triggers use article table which structure is not
important at this moment (let's assume it has id, group_id, name and active).
i hope this code will help you a bit.
depesz
--
hubert depesz lubaczewski
najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
jest niesamowita wręcz łatwość unikania kontaktów z nim ...
[SQL] how to alter/drop check contraint?
the subject should be self-explanatory, but: i have table: create table a (b text check (length(b)<10)); and for some reason i want to drop this check or alter this to length(b)<20. how can i do so? or maybe using trigger in plpgsql will be better? how to make trigger which will stop insert or update when something occurs? depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
[SQL] creating tables with different character set?
hi, i have a problem. i'm living in poland, which has its national characters. of course they work great under postgresql, but: when i use non-C locale all ~ '^xxx' and like 'xxx%' searches are not using index scan. this is paintful. for some of the tables i dont need all national characters. in fact i use only a-z0-9 characters which are subset of standard C-locale character set. so my question is. is it possible to make a table that way, that it will allow using indexing when searching for first x characters of string. at the moment the only solution i got is to make another database (with another postmaster process), but this is definetly not easy way. any other options? depesz -- hubert depesz lubaczewski http://www.depesz.pl/ najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [SQL] SQL problem with aggregate functions.
On Tue, Jul 09, 2002 at 10:36:17AM +0200, David BOURIAUD wrote:
> Field group | count of D | count of R | count of X.
if you want this that way, i suggest using subselects.
like:
select
distinct field_group,
(select count(*) from table t2 where t2.field_group = t1.field_group and
t2.field='D') as count_of_d,
(select count(*) from table t2 where t2.field_group = t1.field_group and
t2.field='R') as count_of_r,
(select count(*) from table t2 where t2.field_group = t1.field_group and
t2.field='X') as count_of_x
from
table;
should work the way you want it.
anyway, i belive that making this:
select field_group, field, count(*) from table where field in
('D','R','X') group by field_group, field;
and then processing results in client application, should be a little
bit better/faster solution.
depesz
--
hubert depesz lubaczewski http://www.depesz.pl/
Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
coś do powiedzenia. (c) 1998 depesz
msg06810/pgp0.pgp
Description: PGP signature
Re: [SQL] RFC822 Checker
On Fri, Aug 30, 2002 at 02:07:28PM +, Matthew Price wrote: > Does anyone know of an SQL function that will check a string for > compliance with the RFC822 mail address spec? I have a script that > sends mail from a db (no, I am not a spammer) but I often have mails > sitting in my queue because the MTA (correctly) refuses to process > some of the bogus things that users enter by mistake. you might want to check: ftp://ftp.cpan.org/CPAN/authors/Tom_Christiansen/scripts/ckaddr.gz since this is in pure perl, making a pl/perl function should be pretty simple. depesz -- hubert depesz lubaczewski http://www.depesz.pl/ Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam coś do powiedzenia. (c) 1998 depesz msg07278/pgp0.pgp Description: PGP signature
[SQL] tree structures in sql - my point of view (with request of comment from joe celko)
hi i recently spent some time on tree-structures in sql. i started with simple id/parent_id approach, used by nearly everyone, then i stopped at joe celko's nested sets, but i found it not very usable. then i found my own (maybe someone wrote it before, but i haven't read it, so idea is mine) way. in my way we have two tables: create table data (id serial, name text); create table structure (parent_id int8, child_id int8, depth int8); structure table represents all paths in tree. for example for this tree: sql / \ postgresqloracle-__ |/|\ linux scolinux windows / \ glibc1 glibc2 (sorry for used data - it is just template, and personally i don't like oracle). so, for this tree we would populate the tables this way: data: id | name + 1 | sql 2 | postgresql 3 | oracle 4 | linux 5 | sco 6 | linux 7 | windows 8 | glibc1 9 | glibc2 structure: parent_id | child_id | depth ---+--+--- 1 |1 | 0 2 |2 | 0 3 |3 | 0 4 |4 | 0 5 |5 | 0 6 |6 | 0 7 |7 | 0 8 |8 | 0 9 |9 | 0 1 |2 | 1 1 |3 | 1 1 |4 | 2 2 |4 | 1 1 |5 | 1 1 |6 | 1 1 |7 | 1 3 |5 | 2 3 |6 | 2 3 |7 | 2 1 |8 | 3 1 |9 | 3 3 |8 | 2 3 |9 | 2 6 |8 | 1 6 |9 | 1 (records with depth 0 are technologically not necessary, but they simplify and speedup some queries). with this data layout (easily indexable) you can fetch any data with just one select statement (no recursion needed in any case): - fetching parent - fetching childs - fetching "from id up" - fetching "from id down" also when you need to get indirect parents/childs or when you need only some of the data (from me, up, but not more then to my grand-grand-grand-father). i'd like to get some comments on this - how do you see my idea, is it worth it, do you know any better way to store trees in sql? best regards depesz -- hubert depesz lubaczewski http://www.depesz.pl/ Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam coś do powiedzenia. (c) 1998 depesz msg07280/pgp0.pgp Description: PGP signature
Re: [SQL] Best Fit SQL query statement
On Mon, Aug 06, 2007 at 01:57:07AM -0700, Kiran wrote: > Could anyone help me in writing Best Fit SQL statement. > Suppose we have table t1 with coloumn t1 (text) with following rows. > 98456 > 98457 > 9845 > 9846 > 984 > 985 > 98 > 99 > and if I query on 98456 the result must be 98456, > However if I query on 98455 the result must be 9845 > and If I query 9849 the result must be 984 select t1.t1 from t1 where '98456' like t1.t1||'%' order by length(t1.t1) desc limit 1; should be ok. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Using function like where clause
On Mon, Aug 06, 2007 at 04:44:29PM -0300, Ranieri Mazili wrote: > 1) Can I use a function that will return a string in a where clause like > bellow? > 2) Can I use a function that will return a string to return the list of > columns that I want to show like below? not in sql. you can in pl/pgsql. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] Best Fit SQL query statement
On Fri, Aug 10, 2007 at 04:40:34PM -0300, Fernando Hevia wrote:
> Found your query is shorter and clearer, problem is I couldn't have it use
> an index. Thought it was a locale issue but adding a 2nd index with
> varchar_pattern_ops made no difference.
> In result, it turned out to be too slow in comparison to the function. Am I
> missing something?
> rd=# explain select prefijo
> rd-# FROM numeracion
> rd-# WHERE '3514269565' LIKE prefijo || '%'
> rd-# ORDER BY LENGTH(prefijo) DESC
> rd-# LIMIT 1;
unfortunatelly this query will be hard to optimize.
i guess that functional approach will be the fastest, but you can try
with something like this:
select prefijo
from numeracion
where prefijo in (
select substr('3514269565',1,i)
from generate_series(1, length('3514269565')) i
)
order by length(prefijo) desc LIMIT 1;
it should be faster then the previous approach, but it will most
probably not be as fast as function.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Best Fit SQL query statement
On Fri, Aug 10, 2007 at 08:13:46PM -0500, Rodrigo De León wrote: > On 8/10/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote: > > unfortunatelly this query will be hard to optimize. > > Uh, how about > > SELECT MAX(t1) > FROM t1 > WHERE '9849' LIKE t1 || '%'; it will not help much as the main burden is the where clause, which is not indexable. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Index usage in order by with multiple columns in order-by-clause
On Fri, Aug 10, 2007 at 04:53:12PM +0200, Andreas Joseph Krogh wrote: > I have the following test-case: > > CREATE TABLE test( > name varchar PRIMARY KEY, > value varchar NOT NULL, > created timestamp not null > ); > > create index test_lowernamevalue_idx ON test ((lower(name) || lower(value))); > create index test_lowernamevaluecreated_idx ON test ((lower(name) || > lower(value)), created); > andreak=# EXPLAIN ANALYZE select * from test order by lower(name) || > lower(value) ASC, created DESC; >QUERY PLAN > > Sort (cost=60.39..62.32 rows=770 width=72) (actual time=0.034..0.034 rows=0 > loops=1) >Sort Key: (lower((name)::text) || lower((value)::text)), created >-> Seq Scan on test (cost=0.00..23.47 rows=770 width=72) (actual > time=0.004..0.004 rows=0 loops=1) > Total runtime: 0.123 ms > (4 rows) > In my application I often have a need to sort by more than 3 columns, so I'm > really wondering if there is a way to make sorting of multiple columsn (each > which may have different sort-order) use an index? Preferrably without having > to create 2^N indexes. first of all - you can try with separate indexes on lower()||lower(), and created. then - you can use a trick. create a function that will reverse order of your date (using a simple "-" operator) and then index your lower() and output of this function. you will need to modify the query, but it's perfectly doable. for example: create function test_ts(timestamp) returns interval as $BODY$ begin return '2000-01-01 00:00:00'::timestamp-$1; end; $BODY$ language plpgsql immutable; of course this particular date is irrelevant, we just have to substract from something. then: create index test_lowernamevaluecreated_idx2 ON test ((lower(name) || lower(value)), test_ts(created)); and change your query to: select * from test order by lower(name) || lower(value) ASC, test_ts(created); it would show you what you need. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] Trigger Procedure Error: NEW used in query that is not in a rule
On Sat, Aug 11, 2007 at 02:45:09AM -0500, Javier Fonseca V. wrote: > I think that it's working alright except for the next line: doing this in plpgsql is very complicated (or even impossible assuming that any table can have the same trigger). i would rather suggest using pl/perl - writing something like this in pl/perl is very simple. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] fetch first rows of grouped data
On Mon, Aug 27, 2007 at 06:36:47PM +0200, Claudia Kosny wrote: > Is there a way to do this with one query? > I am using PostgreSQL 7.4. you can try to use "distinct on". depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] fetch first rows of grouped data
On Tue, Aug 28, 2007 at 08:00:42AM -0500, Michael Glaesemann wrote: > >you can try to use "distinct on". > I considered that as well, but couldn't think of a way to return more you're right and i was wrong. i simply missed the word "two" in original question. sorry. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Optimize querry sql
On Fri, Sep 14, 2007 at 12:26:00PM +0200, Stanislas de Larocque wrote: > Explain my sql querry : did you notice, that andreas asked: > > Show us the output from EXLAIN ANALYSE . depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] Optimize querry sql
On Fri, Sep 14, 2007 at 03:02:59PM +0200, Stanislas de Larocque wrote:
> I want to optimize my query sql (execution time : 2665 ms) :
SELECT
b.idxreseller,
sum(a.nbrq),
b.namereseller
from
stat a,
reseller b
where
b.asp=6
and a.idxreseller=b.reseller
and a.month=date_part('month',now() - interval '1 month')
and a.year=date_part('year',now() - interval '1 month')
GROUP BY
b.idxreseller,b.namereseller limit 15;
1. cast all date_parts to int4, like in:
a.month = cast( date_part('month',now() - interval '1 month') as int4)
2. why there is a limit without any order by?
3. change it to get namereseller from subselect, not from join.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Optimize querry sql
On Mon, Sep 17, 2007 at 09:17:58AM +0200, Stanislas de Larocque wrote: > Have you advices to optimize the query please ? for some many rows the 400ms looks quite reasonable. the best thing you can make to speed things up is to calculate the counts with triggers. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ALL() question
On Wed, Nov 14, 2007 at 02:39:02PM +0100, Julien Cigar wrote: > With the following I got a syntax error: > select specimen_id > from specimen_test_bits > where test_bit_id = all(1,2,3,4); where test_bit_id in (1,2,3,4) group by specimen_id having count(distinct test_bit_id) = 4; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] statement-level trigger sample out there?
On Thu, Nov 29, 2007 at 04:04:54PM +0100, Christian Kindler wrote: > I have a realy big table (> 2'000'000 records). every second there are > several inserts and updates. the thing is i need a last row reference > depending on a foreing_key. > > something like this: > > id, foreign_key, last_id, value1, value1, date > >1<, 3,null, 12, 13, 2007-01-01 > >2<, 4,null, 11, 10, 2007-01-01 > 4, 3, >1<, 12, 13, 2007-01-02 > 5, 4, >2<, 11, 10, 2007-01-02 > ... > > of course the sequence can have holes so I have to calculate the real last > row id. for now i calculate for each row by invoking a "select max(id) where > foreign_key = $1" but this cost a lot of performance. I could do this easily > with one update for the whole query - if i could know which foreign_key and > which date range was performed. create index q on table (foreing_key, id); and then: select max(id) from table where foreing_key = ?; should be very fast. if it is not: select id from table where foreing_key = ? order by foreing_key desc, id desc limit 1; will be fast. of course - remember about vacuum/analyze. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] currval() within one statement
On Tue, Jan 22, 2008 at 10:16:30AM +0300, silly_sad wrote:
> INSERT INTO ttt (a,b) SELECT currval('ttt_id_seq'), 'const' FROM ttt2;
> Where the trigger before insert on ttt is defined and this trigger calls
> nextval('ttt_id_seq').
> I was surprised having different values of currval() in ttt.a
> Is this the normal behavior ? Where is it described ?
currval is volatile function:
select provolatile from pg_proc where proname = 'currval';
it means it is called for every row separately.
and since it is inserted, it's evaluation is (i guess):
get 1 row from select
insert
get next row from select
insert
...
which means, that the sequence gets updated in mean time (by trigger).
if you want to have the same currval, i would suggest to do:
INSERT INTO ttt (a,b) SELECT (select currval('ttt_id_seq')), 'const' FROM ttt2;
(which should work).
or (and this would be definitely the best way) seriously rethink the
schema.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Create on insert a unique random number
On Tue, Mar 18, 2008 at 01:40:42PM -0500, Campbell, Lance wrote: > This is not a security approach. It is more about not giving obvious > access to people that want to mess around. 1. keep primary key using standard serial. it will make your life a bit simpler. 2. add column for text random identifiers (it doesn't have to be number, and adding characters makes for much better "randomness"). 3. check this: http://www.depesz.com/index.php/2007/06/25/random-text-record-identifiers/ depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- 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] Having a mental block with (self) outer joins
On Mon, Apr 21, 2008 at 03:48:23PM +0200, Thomas Kellerer wrote: > name, id, parent_id > ROOT, 1, NULL > CHILD1, 2, 1 > CHILD2, 3, 1 > > I would have expected the following result: > > ROOT, NULL > ROOT, CHILD1 > ROOT, CHILD2 > > but the row with (ROOT,NULL) is not returned. why would you expect it? the columns are: parent and child (on your output). you dont have any row that has *parent_id = 1* and id = NULL. you can get this output though: NULL, ROOT ROOT, CHILD1 ROOT, CHILD2 with this query: select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- 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] After updating dataset the record goes to the end of the dataset
On Tue, Apr 22, 2008 at 12:10:41PM +0200, Nacef LABIDI wrote: > Yes I don't issue any sort statement, and I indeed want the data to be show > as it is stored in the database. But after updating a row (I don't update > the ID, just some fields), it keeps its same place on the DB but jumps to > the end of the dataset and by the way to the end of the DBGrid. why do you think it stays in the same place in db? besides - without "order by" you cannot depend on the order of rows. basically i treat them as in "random" order (which is not true, but helps me remember to never count on the "default" ordering. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- 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] After updating dataset the record goes to the end of the dataset
On Tue, Apr 22, 2008 at 12:31:54PM +0200, Nacef LABIDI wrote: > When I have tested this with SQLServer it works well, since the rows doesn't > change position on the DB. > I hope that you understand my issue and I will provide any explanations if > someting isn't clear enough. well. in postgresql rows do change position. and even in mssql depending on position from table files is a very big mistake. suggestion - add default "order by" by some id or timestamp column, and modify it to fit your user preferences. for example: let's say that you have rows with ids: 1,2,3 by default you get them in order: 1,2,3. if user wants to change the ordering to 2,3,1, store his preferences in some other table and do it like this: select t.* from table t join preferences p on t.id = p.id_in_table where p.user = 'current user' order by p.ordering; regards, depesz -- 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] Difference in columns
On Sun, May 11, 2008 at 01:37:52PM -0400, Mag Gam wrote: > Any thoughts about this? 1. will there be any gaps in between dates? if yes, what should be diff be then? 2. can't you calculate it in client application? 3. is usage of pl/pgsql acceptable (i think this will be the fastest way to do it in postgresql itself). depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- 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] Find all instances of a column in the entire database.
On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote: > I am hoping that you might be able to give me some assistance with the > following task! > I have a database with nearly 200 tables and I need to find all tables > that contain a column of myColumnName. select * from information_schema.columns where column_name = 'myColumnName'; depesz -- 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] Unable to create function which takes no arguments
On Mon, Jun 09, 2008 at 12:05:52PM -0400, Michael Eshom wrote: > I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, which > will return the current timestamp. However, whenever I try to add this > function in phpPgAdmin, it says 'Syntax error at or near ")" at > character 28'. yes, but the problem is not in this line: > CREATE FUNCTION unix_timestamp() RETURNS integer AS ' it is in this: > SELECT current_timestamp()::int4 AS result; # CREATE FUNCTION unix_timestamp() RETURNS integer AS ' SELECT current_timestamp()::int4 AS result; ' LANGUAGE SQL; ERROR: syntax error at or near ")" LINE 2: SELECT current_timestamp()::int4 AS result; ^ what's more, when you fix () issue inside of function it will still be broken: # CREATE FUNCTION unix_timestamp() RETURNS integer AS 'SELECT current_timestamp::int4 AS result;' LANGUAGE SQL; ERROR: cannot cast type timestamp with time zone to integer LINE 1: ...p() RETURNS integer AS 'SELECT current_timestamp::int4 AS re... ^ (it might work in older postgresql versions, i'm not sure). to make it sane write it that way: CREATE FUNCTION unix_timestamp() RETURNS integer AS ' SELECT extract(epoch from current_timestamp)::int4; ' LANGUAGE SQL; depesz -- 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] GROUP BY on a column which might exist in one of two tables
On Wed, Jun 25, 2008 at 09:50:18AM -0400, Mark Stosberg wrote: > hits > hit_id > partner_id > > views > view_id > partner_id > > There is of course a "partners" table with a "partner_id" column. > > My target result is more like > > partner_id > total_views > total_hits select coalesce(h.partner_id, v.partner_id) as partner_id, coalesce(v.count, 0) as total_views, coalesce(h.count, 0) as total_hits from (select partner_id, count(*) from hits group by partner_id) as h full outer join (select partner_id, count(*) from views group by partner_id) as v on h.partner_id = v.partner_id ; depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
