Re: [SQL] update column based on postgis query on anther table
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of ssylla > Sent: Tuesday, July 16, 2013 3:58 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] update column based on postgis query on anther table > > Hi Tom, > > I tried changing the trigger to be BEFORE instead of AFTER: > > create trigger test1_point_get_id_test1_poly > before insert or update on test1_point for each row execute procedure > test1_point_get_id_test1_poly(); > > But the problem persits, the column id_test1_poly remains empty. > > Stefan > > Stefan, Probably, you need to change something in your trigger logic, because like Tom stated it's too late to change NEW values in AFTER record, and in BEFORE trigger the record with NEW value doesn't exist yet, so: select test1_poly.id from test1_poly,test1_point where ST_Within( test1_point.the_geom, test1_poly.the_geom) and test1_point.id=$1; with $1 being NEW.id returns NULL (record with test1_point.id = NEW.id isn't written yet into test1_point table). May be this trigger function is what you need: create or replace function test1_point_get_id_test1_poly() returns trigger as $$ begin select test1_poly.id INTO new.id_test1_poly from test1_poly where ST_Within( NEW.the_geom, test1_poly.the_geom); return new; end; $$ language plpgsql volatile; Still there is an issue. What if your point falls within multiple polygons (multiple records in test1_poly satisfy your criteria)? In this case, select from test1_poly should return multiple records. This will break trigger function code. Regards, Igor Neyman -- 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] delete where not in another table
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Campbell, Lance Sent: Tuesday, July 09, 2013 3:25 PM To: pgsql-sql@postgresql.org Subject: [SQL] delete where not in another table DELETE FROM T1 WHERE T1.user_id NOT IN (SELECT user_id FROM T2 WHERE T2.user_id=T1.user_id); Thanks, Lance Campbell Software Architect Web Services at Public Affairs 217-333-0382 -- Try: DELETE FROM t1 USING t2 WHERE t1.user_id != t2.user_id; Test it before running on production db. Regards, Igor Neyman -- 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] array_agg() with join question
On Tue, May 14, 2013 at 10:08 AM, George Woodring <
george.woodr...@iglass.net> wrote:
> To summarize my question at the top, why is it that when I did the JOIN,
> the array_agg results reversed order?
>
> I had a function that ran the following query:
>
> SELECT timeslot, pollgrpid, array_agg(outval)
>FROM
> (SELECT * FROM rrd.a_current WHERE pollgrpid=8 ORDER BY timeslot,
> dsnum) AS foo
>WHERE timeslot >= now() - '1 hour'::interval AND timeslot <= now() + '1
> hour'::interval
>GROUP BY timeslot, pollgrpid
>ORDER BY timeslot;
>
> timeslot| pollgrpid | array_agg
> +---+
> 2013-05-14 08:58:00-04 | 8 | {1,1,0.00125217437744141}
> 2013-05-14 09:00:00-04 | 8 | {1,1,0.00027520751953}
> 2013-05-14 09:02:00-04 | 8 | {1,1,0.00318312644958496}
> 2013-05-14 09:04:00-04 | 8 | {1,1,0.000761985778808594}
> 2013-05-14 09:06:00-04 | 8 | {1,1,0.000777959823608398}
> 2013-05-14 09:08:00-04 | 8 | {1,1,0.101096868515015}
> 2013-05-14 09:10:00-04 | 8 | {1,1,0.86168384552002}
> 2013-05-14 09:12:00-04 | 8 | {1,1,0.00656795501708984}
> 2013-05-14 09:14:00-04 | 8 | {1,1,0.102259159088135}
> 2013-05-14 09:16:00-04 | 8 | {1,1,0.000636100769042969}
>
> I wanted to include missing timestamps in my results, so I joined it with
> generate_series.
>
> SELECT timeslot, pollgrpid, array_agg(outval)
>FROM
> ( SELECT generate_series(rrd_timeslot('avail', now() - '58
> minutes'::interval), now() + '1 hour'::interval, '2 minutes') AS timeslot )
> AS bar
>LEFT JOIN
> (SELECT * FROM rrd.a_current WHERE pollgrpid=8 AND timeslot >= now()
> - '1 hour'::interval AND timeslot <= now() + '1 hour'::interval ORDER BY
> timeslot, dsnum) AS foo
>USING(timeslot)
>GROUP BY timeslot, pollgrpid
>ORDER BY timeslot;
>
> timeslot| pollgrpid | array_agg
> +---+
> 2013-05-14 09:02:00-04 | 8 | {0.00318312644958496,1,1}
> 2013-05-14 09:04:00-04 | 8 | {0.000761985778808594,1,1}
> 2013-05-14 09:06:00-04 | 8 | {0.000777959823608398,1,1}
> 2013-05-14 09:08:00-04 | 8 | {0.101096868515015,1,1}
> 2013-05-14 09:10:00-04 | 8 | {0.86168384552002,1,1}
> 2013-05-14 09:12:00-04 | 8 | {0.00656795501708984,1,1}
> 2013-05-14 09:14:00-04 | 8 | {0.102259159088135,1,1}
> 2013-05-14 09:16:00-04 | 8 | {0.000636100769042969,1,1}
> 2013-05-14 09:18:00-04 | 8 | {0.000638008117675781,1,1}
> 2013-05-14 09:20:00-04 | 8 | {0.174574136734009,1,1}
> 2013-05-14 09:22:00-04 | 8 | {0.1006920337677,1,1}
> 2013-05-14 09:24:00-04 | 8 | {0.00069117546081543,1,1}
> 2013-05-14 09:26:00-04 | 8 | {0.114289045333862,1,1}
> 2013-05-14 09:28:00-04 | 8 | {0.116230010986328,1,1}
> 2013-05-14 09:30:00-04 | 8 | {0.0349528789520264,1,1}
>
> The array_agg results are reversed. I had to ODER BY timeslot, dsnum desc
> on the right of the join to make it match. I am curious as to why this
> happened. I am running 9.2.4.
>
> Thanks,
> Woody
>
> iGLASS Networks
> www.iglass.net
>
As always (with databases) order is not guaranteed unless you specify
"ORDER BY ...".
So, specify whatever order you want inside aggregate function:
array_agg(outval order by column1)
Check the docs:
http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-AGGREGATES
Igor Neyman
Re: [SQL] Correct implementation of 1:n relationship with n>0?
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Wolfgang Keller > Sent: Tuesday, April 30, 2013 2:19 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Correct implementation of 1:n relationship with n>0? > > > It hit me today that a 1:n relationship can't be implemented just by > a > > single foreign key constraint if n>0. I must have been sleeping very > > deeply not to notice this. > > > > E.g. if there is a table "list" and another table "list_item" and the > > relationship can be described as "every list has at least one > > list_item" (and every list_item can only be part of one list, but > this > > is trivial). > > > > BTW: If every list_item could be part of any number (>0) of lists, you > get a n:m relationship with a join table and then the issue that each > list_item has to belong to at least one list arises as well. > > Maybe there should also be a standard solution documented somewhere for > this case, too. > > > > Sincerely, > > Wolfgang > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Wolfgang, If it is n:m relationship, than appearance of the LIST in list_item table : (list_id int not null, item_id int not null, constraint PK_list_item Primary Key (list_id, item_id), constraint FK_ItemList_List Foreign Key (list_id) references List (list_id) on delete cascaded on update restrict, constraint FK_ItemList_Item Foreign Key (item_id) references Item (item_id) on delete cascaded on update restrict) means that this LIST has at least one ITEM assigned to it. Same goes for the ITEM: if it is assigned to at least one List it should appear in this "cross table". It is application responsibility to populate this table, when Items assigned to Lists. It is database responsibility (through declarative foreign keys) to make sure that Lists and Items used in "cross table" have corresponding records in "parent" tables. Using triggers (which is SQL extension implemented differently in every DBMS) database also can support such feature, as: "when last Item removed from the List - drop now "empty" List. Which I don't consider a good idea - what if you'll need this list in the future? Why re-create it? As for your original problem with 1:n relationship, n should be starting from 0 for the case when new List is created and there is no Items to assign to this new List, yet. In this case, FK on Items table referencing List table makes sure that every Item references existing (valid) List. Regards, Igor Neyman -- 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] Using Ltree For Hierarchical Structures
From: Don Parris [mailto:parri...@gmail.com] Sent: Tuesday, February 26, 2013 4:55 PM To: pgsql-sql@postgresql.org Subject: Re: Using Ltree For Hierarchical Structures ... I am unaware of the connect_by module, so will have to look into it. ... Cheers! Don For that look into tablefunc Extension. It also has other useful functions, such as crosstab. Regards, Igor Neyman
Re: [SQL] Using Ltree For Hierarchical Structures
From: Don Parris [mailto:parri...@gmail.com] Sent: Tuesday, February 26, 2013 1:23 PM To: Misa Simic Cc: Igor Neyman; pgsql-sql@postgresql.org Subject: Re: [SQL] Using Ltree For Hierarchical Structures Hi Igor, As Misa points out, my original design used 2 tables - category & line-items. Either way it's two tables. The biggest difference lies in the approach to summing line-items by various levels of category - with each branch of the tree having different levels. I cannot speak to performance, but understanding CTEs has been difficult for me. Ltree is so much simpler. I am certain there is a place for CTEs, but why torture myself trying to hash out a CTE when ltree makes the queries so much easier to write? Don, To answer this question... Before I worked (mostly) with Oracle which has "connect by" construct to work with hierarchies. So, when I switched to Postgres I was happy to find "connect_by" contrib. modul. And with more recent PG versions, it was just natural transition from contrib. module to recursive CTEs. Igor -- 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] Using Ltree For Hierarchical Structures
From: Don Parris [mailto:parri...@gmail.com] Sent: Sunday, February 24, 2013 5:21 PM To: pgsql-sql@postgresql.org Subject: Using Ltree For Hierarchical Structures Hi all, With many thanks to Misa and others who helped out with my question about working with hierarchical data, I have now written a blog post on how I implemented the ltree module to solve my problem. http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/ Frankly, if you work with hierarchical data, I'm not sure I could recommend it strongly enough. I should think that even experienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly table designs and recursive queries in order to work with hierarchical structures. I really hope this blog post will help others in the same boat. Regards, Don It's all "in the eyes of beholder". IMHO, recursive CTEs are perfect for hierarchical structures, and much cleaner than 2-table design using ltree, that you show in the blog. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to compare two tables in PostgreSQL
From: saikiran mothe [mailto:saikiran.mo...@gmail.com] Sent: Saturday, November 10, 2012 10:14 PM To: pgsql-sql@postgresql.org Subject: How to compare two tables in PostgreSQL Hi, How can i compare two tables in PostgreSQL. Thanks, Sai Here is simple sql to show data in table1, but not in table2: SELECT from table1 EXCEPT SELECT from table2; And this sql shows data in table2 but not in table1: SELECT from table2 EXCEPT SELECT from table1; Or, you could combine them in one statement, adding "indicator" column: SELECT , 'not in table2' as indicator from table1 EXCEPT SELECT , 'not in table2' as indicator from table2 UNION SELECT , 'not in table1' as indicator from table2 EXCEPT SELECT , 'not in table1' as indicator from table1; Regards, Igor Neyman -- 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] using ordinal_position
> -Original Message- > From: John Fabiani [mailto:jo...@jfcomputer.com] > Sent: Monday, June 11, 2012 11:11 AM > To: Igor Neyman > Cc: pgsql-sql@postgresql.org > Subject: Re: using ordinal_position > > On 06/11/2012 06:53 AM, Igor Neyman wrote: > > > >> -Original Message- > >> From: John Fabiani [mailto:jo...@jfcomputer.com] > >> Sent: Thursday, June 07, 2012 7:18 PM > >> To: pgsql-sql@postgresql.org > >> Subject: using ordinal_position > >> > >> I'm attempting to retrieve data using a select statement without > >> knowing the column names. I know the ordinal position but not the > >> name of the column (happens to be a date::text and I have 13 > fields). > >> > >> Below provides the name of the column in position 3: > >> > >> select column_name from (select column_name::text, ordinal_position > >> from information_schema.columns where > >> table_name='wk_test') as foo where ordinal_position = 3; > >> > >> But how can I use the above as a column name in a normal select > >> statement. > >> > >> Unlike other databases I just can't use ordinal position in the > >> select statement - RIGHT??? > >> > >> Johnf > > David gave you already pretty complete answer. > > I just wonder what are these "other" RDBMSs that allow to use ordinal > column positions in a query? > > I am familiar with a few (in addition to Postgress), and none of them > does that, not in "select" list., though everybody allow ordinal > position from "select" list in "order by" and "group by". > > > > Regards, > > Igor Neyman > > > > > > > VFP uses position (you might not consider DBF a database). MsSQl > (ordinal_position). Those are the only two I'm aware of. > > Johnf Did you mean MySQL or MS SQL? Because MS SQL Server does not allow ordinal position AFAIK. Igor -- 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] using ordinal_position
> -Original Message- > From: John Fabiani [mailto:jo...@jfcomputer.com] > Sent: Thursday, June 07, 2012 7:18 PM > To: pgsql-sql@postgresql.org > Subject: using ordinal_position > > I'm attempting to retrieve data using a select statement without > knowing the column names. I know the ordinal position but not the name > of the column (happens to be a date::text and I have 13 fields). > > Below provides the name of the column in position 3: > > select column_name from (select column_name::text, ordinal_position > from information_schema.columns where > table_name='wk_test') as foo where ordinal_position = 3; > > But how can I use the above as a column name in a normal select > statement. > > Unlike other databases I just can't use ordinal position in the select > statement - RIGHT??? > > Johnf David gave you already pretty complete answer. I just wonder what are these "other" RDBMSs that allow to use ordinal column positions in a query? I am familiar with a few (in addition to Postgress), and none of them does that, not in "select" list., though everybody allow ordinal position from "select" list in "order by" and "group by". Regards, Igor Neyman -- 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] map row in one table with random row in another table
> -Original Message- > From: rverghese [mailto:ri...@hotmail.com] > Sent: Tuesday, March 06, 2012 4:01 PM > To: pgsql-sql@postgresql.org > Subject: map row in one table with random row in another table > > Hi, I am trying to map every row in one table with a random row in > another. > So for e.g. , for each network in 1 table I am trying to map random > segments from the other table. I have this sql below, but it always > applies the same random segment that it picks to all the rows for the > network. I want each row to have a random segment value. I'm just using > the generate_series function to generate id's as an e.g. > Any suggestions? > > My Query > select id, seg_list from (select generate_series(1,10) as id) as X, > (select segment from segments order by random() limit 1 ) as Y > > I get > > 1;'cob0002' > 2;'cob0002' > 3;'cob0002' > 4;'cob0002' > 5;'cob0002' > 6;'cob0002' > 7;'cob0002' > 8;'cob0002' > 9;'cob0002' > 10;'cob0002' > > What I want is > > 1;'cob0002' > 2;'cob0008' > 3;'cob0006' > 4;'cob0004' > 5;'cob0002' > 6;'cob0007' > 7;'cob0003' > 8;'cob0004' > 9;'cob0009' > 10;'cob0001' > Try this: Select distinct on (id) id, segment From (select generate_series(1,10) as id) as X, (select segment from segments) as Y Order by id, random(); Depending on the size of your tables, performance could become an issue. Regards, Igor Neyman -- 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] SQL View to PostgreSQL View
From: Rehan Saleem [mailto:pk_re...@yahoo.com]
Sent: Sunday, February 26, 2012 1:50 PM
To: pgsql-sql@postgresql.org
Subject: SQL View to PostgreSQL View
Hi ,
I am trying to convert sql view to postgresql view but i am getting the
following error i dont know how to handle dbo. in postgresql and when i remove
dbo. from table name then view got created but it does not show any data, while
this is working perfectly fine in sql, here is my code and error details
CREATE OR REPLACE VIEW vwkbcomparesites as
select a.kbid kb_a, b.kbid kb_b, a.chr chr_a, a.start start_a, a."end" end_a,
(a."end" - a.start)+1 tagsize_a,
b.chr chr_b, b.start start_b, b."end" end_b, (b."end" - b.start)+1
tagsize_b,
abs((a."end" + a.start)/2 - (b."end" + b.start)/2) centredistance,
case
when a."end" <= b."end" and a.start >= b.start
then (a."end" - a.start)
when b."end" <= a."end" and b.start >= a.start
then (b."end" - b.start)
when a."end" <= b."end" and a.start <= b.start
then (a."end" - b.start)
when a."end" >= b."end" and a.start >= b.start
then (b."end" - a.start)
end bpoverlap
from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr
inner join dbo.kbdetails kbd on a.kbid=kbd.kbid
where kbd.active='1' ;
i am getting this error , how can i fix this.
ERROR: schema "dbo" does not exist
LINE 15: from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr
^
** Error **
ERROR: schema "dbo" does not exist
SQL state: 3F000
Character: 761
>>
First, I assume you are converting your view from SQL Server, not from SQL.
SQL Server is RDBMS, while SQL is a language being used by multiple RDBMSs
including PostgreSQL.
Second, there is no "standard" dbo ("database owner") role in Postgres.
Before converting from one RDBMS to another you need to do some basic (at
least) documentation reading on "target" RDBMS (in this case - PostgreSQL).
Otherwise, you will stumble on every step.
Regards,
Igor Neyman
--
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] Concurrent Reindex on Primary Key for large table
> -Original Message- > From: rverghese [mailto:ri...@hotmail.com] > Sent: Thursday, February 09, 2012 12:49 PM > To: pgsql-sql@postgresql.org > Subject: Re: Concurrent Reindex on Primary Key for large table > > Thanks! That worked. > > Any thoughts about containing index bloat. I thought the autovac would > clean it up a bit more. Would any tweaks to my settings improve autovac > performance? I am still doing a couple of concurrent reindexes per week > otherwise performance degrades over a couple of days. > > Thanks > RV > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Concurrent-Reindex-on-Primary- > Key-for-large-table-tp5467243p5470216.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. Did you try to make autovacuum "more aggressive" like lowering autovacuum_vacuum_threshold from 2? Regards, Igor Neyman -- 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] which is better: using OR clauses or UNION?
> -Original Message-
> From: adam_pgsql [mailto:adam_pg...@witneyweb.org]
> Sent: Tuesday, August 16, 2011 11:56 AM
> To: Tom Lane
> Cc: pgsql-sql
> Subject: Re: which is better: using OR clauses or UNION?
>
>
> On 16 Aug 2011, at 15:09, Tom Lane wrote:
>
> > adam_pgsql writes:
> >> I have a query hitting a table of 25 million rows. The table has a
> >> text field ('identifier') which i need to query for matching rows.
> The
> >> question is if i have multiple strings to match against this field
I
> >> can use multiple OR sub-statements or multiple statements in a
> >> UNION. The UNION seems to run quicker is this to be expected?
> >
> > Your test cases don't seem exactly comparable; in particular I think
> the
> > second one is benefiting from the first one having already read and
> > cached the relevant disk blocks. Notice how you've got, eg,
> >
> >> -> Bitmap Index Scan on in_dba_data_base_identifier
> (cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347
> rows=318 loops=1)
> >>Index Cond: (lower(identifier) ~=~ 'sptigr4-2210
> (6f24)'::character varying)
> >
> > versus
> >
> >> -> Bitmap Index Scan on
> in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0)
> (actual time=0.178..0.178 rows=318 loops=1)
> >>Index Cond: (lower(identifier) ~=~
> 'sptigr4-2210 (6f24)'::character varying)
> >
> > Those are the exact same subplan, so any honest comparison should be
> > finding them to take the same amount of time. When the actual
> readings
> > are different by a factor of several hundred, there's something
wrong
> > with your measurement process.
> >
> > In the end this comes down to whether duplicates will be eliminated
> more
> > efficiently by a BitmapOr step or by sort/uniq on the resulting
rows.
> > I'd have to bet on the BitmapOr myself, but it's likely that this is
> > down in the noise compared to the actual disk accesses in any
> > not-fully-cached scenario. Also, if you don't expect the sub-
> statements
> > to yield any duplicates, or don't care about seeing the same row
> twice
> > in the output, you should consider UNION ALL instead of UNION.
>
>
> Thanks guys, I'll give some of those options a try and see which ones
> improve performance
>
> (Tom, yes i ran those queries after each other so there was caching
> going on. However, I had noticed a difference in performance when
> spacing the queries before and after a few other big queries to help
> clear the cache).
>
> adam
Adam,
Did you verify that your cache is "cleared"? Like using pg_buffercache
contrib. module?
Besides, there is also OS cache...
Regards,
Igor Neyman
--
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] which is better: using OR clauses or UNION?
=62) (actual > time=2.660..2.834 rows=318 loops=1) > Sort Key: bioassay_id, identifier, ratio, log_ratio, > p_value > -> Append (cost=32.88..15185.06 rows=3856 width=62) > (actual time=0.320..2.131 rows=318 loops=1) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.041..0.041 > rows=0 loops=1) > Filter: (lower(identifier) ~~ > 'bugs001884677'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.036..0.036 rows=0 loops=1) > Index Cond: (lower(identifier) ~=~ > 'bugs001884677'::character varying) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.010..0.010 > rows=0 loops=1) > Filter: (lower(identifier) ~~ > 'bugs001884678'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.008..0.008 rows=0 loops=1) > Index Cond: (lower(identifier) ~=~ > 'bugs001884678'::character varying) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.010..0.010 > rows=0 loops=1) > Filter: (lower(identifier) ~~ > 'bugs001884679'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.008..0.008 rows=0 loops=1) > Index Cond: (lower(identifier) ~=~ > 'bugs001884679'::character varying) > -> Bitmap Heap Scan on dba_data_base a > (cost=32.88..3786.62 rows=964 width=62) (actual time=0.255..1.676 > rows=318 loops=1) > Filter: (lower(identifier) ~~ 'sptigr4-2210 > (6f24)'::text) > -> Bitmap Index Scan on > in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) > (actual time=0.178..0.178 rows=318 loops=1) > Index Cond: (lower(identifier) ~=~ > 'sptigr4-2210 (6f24)'::character varying) > Total runtime: 4.174 ms > > Also which should scale better if I add more strings to match? would > there be any better design patterns for this problem? > > Thanks for any help > > Adam > > select version(); >version > > PostgreSQL 8.2.12 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > Adam, There is something strange in your 2 execution plans. Exactly the same operation: -- first plan Bitmap Index Scan on in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual time=71.347..71.347 rows=318 loops=1) Index Cond: (lower(identifier) ~=~ 'sptigr4-2210 (6f24)'::character varying) -- second plan Bitmap Index Scan on in_dba_data_base_identifier (cost=0.00..32.64 rows=964 width=0) (actual time=0.178..0.178 rows=318 loops=1) Index Cond: (lower(identifier) ~=~ 'sptigr4-2210 (6f24)'::character varying) takes quite different time: 71.347 versus 0.178 and basically makes all the difference between duration of your first and second statement. I think, what you are seeing here is data being cached in memory (when you executed "union" statement after "or" statement). Other than that, looking at 2 execution plans, I'd say that in general "or" should run faster than "union", at least because it does "Bitmap Heap Scan on dba_data_base" only once, while "union" statement does this heap scan 4 times (once per "unionized" select). HTH, Igor Neyman -- 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] to_char() accepting invalid dates?
-Original Message-
From: Bruce Momjian [mailto:br...@momjian.us]
Sent: Monday, July 18, 2011 6:02 PM
To: Thomas Kellerer
Cc: pgsql-sql@postgresql.org
Subject: Re: to_char() accepting invalid dates?
Thomas Kellerer wrote:
> Jasen Betts wrote on 18.07.2011 11:23:
> >> postgres=> select to_date('20110231', 'mmdd');
> >>
> >> to_date
> >>
> >>2011-03-03
> >> (1 row)
> >>
> >> is there a way to have to_date() raise an exception in such a case?
> >
> > it's possible the odd behaviour you get is required by some
standard.
>
> That would be *very* odd indeed.
>
>
> > jasen=# select '20110303'::date;
> Thanks for the tip, this was more a question regarding _why_ to_char()
behaves this way.
> Well, to_char() is based on Oracle's to_char(). How does Oracle
handle
> such a date?
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
> + It's impossible for everything to be true. +
This (from Oracle) makes me think, that it's implemented differently:
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select to_date('20110231', 'mmdd') from dual;
select to_date('20110231', 'mmdd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL>
Regards,
Igor Neyman
--
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 insert trigger and select
> -Original Message- > From: Rok Jaklič [mailto:r...@rasca.net] > Sent: Wednesday, February 16, 2011 5:35 PM > To: pasman pasmański; pgsql-sql > Subject: Re: After insert trigger and select > > On 02/16/2011 08:46 PM, pasman pasmański wrote: > >> If I have after insert trigger on some table which updates > some data > >> in that same table, will be the select statement from some other > >> client executed after all statements in that trigger? > >> > > select statement is fired before commit ? > > > > pasman > For example let us say that trigger takes a long time to end. > Are all statements in trigger executed before select from > "outside" if select is called somewhere between executing of > the trigger? > With MVCC "writers" don't block "readers", and "readers" don't block "writers". Read PG docs on MVCC. Regards, Igor Neyman -- 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] Function compile error
> -Original Message- > From: Sivannarayanreddy [mailto:sivannarayanre...@subexworld.com] > Sent: Wednesday, February 16, 2011 7:36 AM > To: pgsql-sql@postgresql.org > Subject: Function compile error > > Hello, > I am trying to create the function as below but it is > throwing error 'ERROR: syntax error at or near "DECLARE"', > Could some one help me please > > CREATE FUNCTION check_password(databasename text, tablename > text, indexname text)RETURNS VOID AS > DECLARE v_count INTEGER; > BEGIN > select count(1) into v_count from pg_index inx where > inx.indexrelid in > (select oid from pg_class where relname=$3 and relowner in > (select oid from pg_authid where rolname=$1)) > and inx.indrelid in > (select oid from pg_class where relname=$2 and relowner in > (select oid from pg_authid where rolname=$1)); > if v_count = 0 then > execute immediate 'create unique index $3 on $2 (acn_id)'; > end if; > END; > > > > Sivannarayanareddy Nusum | System Analyst(Moneta GDO) > > > > Subex Limited, Adarsh Tech Park, Outer Ring Road, > Devarabisannalli, Bangalore - 560037, India. > Phone: +91 80 6696 3371; Mobile: +91 9902065831 Fax: +91 80 > 6696 ; > > Email: sivannarayanre...@subexworld.com > <mailto:email...@subexworld.com> ; URL: www.subexworld.com > <http://www.subexworld.com/> > > > > Disclaimer: This e-mail is bound by the terms and conditions > described at http://www.subexworld.com/mail-disclaimer.html > <http://www.subexworld.com/mail-disclaimer.html> > CREATE FUNCTION check_password(databasename text, tablename text, indexname text) RETURNS VOID AS $body$ DECLARE v_count INTEGER; BEGIN select count(1) into v_count from pg_index inx where inx.indexrelid in (select oid from pg_class where relname=$3 and relowner in (select oid from pg_authid where rolname=$1)) and inx.indrelid in (select oid from pg_class where relname=$2 and relowner in (select oid from pg_authid where rolname=$1)); if v_count = 0 then execute immediate 'create unique index $3 on $2 (acn_id)'; end if; END; $body$LANGUAGE PLPGSQL; Regards, Igor Neyman -- 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] sqlplus reporting equivalent in postgres?
-Original Message- From: Tony Capobianco [mailto:tcapobia...@prospectiv.com] Sent: Thu 12/9/2010 4:47 PM To: pgsql-sql@postgresql.org Subject: sqlplus reporting equivalent in postgres? We're in the process of porting our Oracle 10.2 datawarehouse over to PostGres 8.4. One thing we rely upon are daily/hourly reports that are run and formatted in sqlplus (break on report, compute sum, etc.). Is there an equivalent available in postgres? Thanks. Tony psql - not as advanced, doesn't have all the features SQL*Plus has. Igor Neyman
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
From: Asko Oja [mailto:asc...@gmail.com] Sent: Wednesday, September 15, 2010 2:29 PM To: Igor Neyman Cc: Tatarnikov Alexander; pgsql-sql@postgresql.org Subject: Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body And dynamic SQL leads easily to SQL injection so quoting is required there. execute 'create user ' || quote_ident(i_username) || ' password ' || quote_literal(i_password); On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman wrote: > -Original Message- > From: Tatarnikov Alexander [mailto:cank...@gmail.com] > Sent: Wednesday, September 15, 2010 12:05 AM > To: pgsql-sql@postgresql.org > Subject: Use "CREATE USER" in plpgsql function > > Hello! > > I have function wich check user credentials and if test > passed function must create new user with generated username > and password. > > Language is plpgsql. > > For example: > > > DECLARE creds RECORD; > ... > SELECT * INTO creds FROM ... > > creds is Record with fields userName(VARCHAR) and > userPassword(VARCHAR) > > so when i use CREATE USER creds."userName" WITH PASSWORD > creds."userPassword" > > i get an error, because creds."userName" is VARCHAR and thus > when function runs it will be look like this: > CREATE USER 'user_1' > but right command is > "CREATE USER user_1" OR " CREATE USER "user_1" " > > so question is how to "unembrace" this parameter (i mean > creds."userName")? > > Thanks > -- > -- > Alexander > It is called "dynamic" sql: EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' || userPassword; Read about "dynamic" sql in PG docs: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html Specifically: "38.5.4. Executing Dynamic Commands" Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql [I.N.] Opps. Missed quote_ident() in your message, sorry.
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
> -Original Message- > From: Asko Oja [mailto:asc...@gmail.com] > Sent: Wednesday, September 15, 2010 2:29 PM > To: Igor Neyman > Cc: Tatarnikov Alexander; pgsql-sql@postgresql.org > Subject: Re: [SQL] Use "CREATE USER" in plpgsql function - > Found word(s) list error in the Text body > > And dynamic SQL leads easily to SQL injection so quoting is > required there. > > execute 'create user ' || quote_ident(i_username) || > ' password ' || quote_literal(i_password); > > > On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman > wrote: > That's too "generic". I was answering specific question. Now, yes, dynamic sql could be used for SQL injection, if not used carefully. But, it exists for a reason. And in this particular case userName and userPassword retrieved from a table. So, care should be taken (appropriate checks to be done) when these values inserted into the table. Btw., do you have another answer to OP question? Regards, Igor Neyman -- 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] Use "CREATE USER" in plpgsql function
> -Original Message- > From: Tatarnikov Alexander [mailto:cank...@gmail.com] > Sent: Wednesday, September 15, 2010 12:05 AM > To: pgsql-sql@postgresql.org > Subject: Use "CREATE USER" in plpgsql function > > Hello! > > I have function wich check user credentials and if test > passed function must create new user with generated username > and password. > > Language is plpgsql. > > For example: > > > DECLARE creds RECORD; > ... > SELECT * INTO creds FROM ... > > creds is Record with fields userName(VARCHAR) and > userPassword(VARCHAR) > > so when i use CREATE USER creds."userName" WITH PASSWORD > creds."userPassword" > > i get an error, because creds."userName" is VARCHAR and thus > when function runs it will be look like this: > CREATE USER 'user_1' > but right command is > "CREATE USER user_1" OR " CREATE USER "user_1" " > > so question is how to "unembrace" this parameter (i mean > creds."userName")? > > Thanks > -- > -- > Alexander > It is called "dynamic" sql: EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' || userPassword; Read about "dynamic" sql in PG docs: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html Specifically: "38.5.4. Executing Dynamic Commands" Regards, Igor Neyman -- 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] row not deleted but updated (trigger?)
"delete" trigger should return "old".
In your code you return "new" for both: "update" and "delete"
Igor
-Original Message-
From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Ivan Sergio
Borgonovo
Sent: Friday, February 27, 2009 6:56 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] row not deleted but updated (trigger?)
I've:
create or replace function FT1IDX_catalog_brands_update() returns
trigger as $$ begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
Authors, '')
where BrandID=old.BrandID;
else
if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
Name, Authors, new.Name)
where BrandID=new.BrandID;
end if;
end if;
return new;
end $$ language plpgsql volatile;
create trigger FT1IDX_catalog_brands_update_trigger before update or
delete on catalog_brands for each row execute procedure
FT1IDX_catalog_brands_update();
I do something
update catalog_brands set name='zzz' where brandid=1234;
1 row get updated.
When I do
delete from catalog_brands where brandid=1234; no row get deleted and no
error get reported.
what did I miss?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
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] prepared query plan did not update
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Wednesday, September 17, 2008 2:55 PM To: pgsql-sql@postgresql.org Subject: [SQL] prepared query plan did not update Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? Thanks alot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql WHERE col1 AND col2 = '$1' ; Are you looking for both: col1 and col2 - to be equal to '$1'? Then it should be: WHERE col1 = '$1' AND col2 = '$1'; -- 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] Aggregates in WHERE clause?
select employee,count(distinct tasks), greatest(max(last_job_date),max(last_position_date)) from job group by employee having greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1'; From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ruben Gouveia Sent: Wednesday, September 10, 2008 7:11 PM To: pgsql-sql Subject: [SQL] Aggregates in WHERE clause? I tried to do the following and got the following error message: select employee,count(distinct tasks) from job where greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1' group by employee; ERROR: aggregates not allowed in WHERE clause ** Error ** ERROR: aggregates not allowed in WHERE clause SQL state: 42803 Is there away around this? Should i create a function to populate a variable that can be used in it's place...will that even work?
Re: [SQL] column names with - and (
Maria,
Try "" (double quotes:
select x1 as "IL-a", x2 as "IL-a(p30)" from abc
should help.
Igor
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of maria s
Sent: Tuesday, July 29, 2008 12:07 PM
To: Osvaldo Rosario Kussama; pgsql-sql@postgresql.org; Pavel Stehule;
Ivan Sergio Borgonovo
Subject: [SQL] column names with - and (
Hi All,
When I am fetching the data from a table,
I am giving the column names with - and ( and ) like IL-a, IL-a(p30)
etc..
select x1 as IL-a, x2 as IL-a(p30) from abc
But I am getting
ERROR: syntax error at or near "-" and also t "(" , ")"
Can anyone help me to fix this?
Thank you,
Maria
Re: [SQL] A table with two names or table alias
There are no synonyms in Postgres (like in Oracle). To implement what you want, you need to use views and rules. From Postgres docs: "Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables." Read docs on views and rules. HTH, Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tk421 Sent: Friday, July 25, 2008 8:13 AM To: pgsql-sql@postgresql.org Subject: [SQL] A table with two names or table alias Can I create a table name alias? What i want is to access the same table with two different names. An example: I've the table CITY with fields code and name. I want to know if i can create an alternative name to table CITY, like LOCATION, to make possible these two sentences: insert into CITY values (10, 'New York'); insert into LOCATION values (11, 'Paris') Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
