.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 multi
.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
528789520264,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, 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 L
Extension.
It also has other useful functions, such as crosstab.
Regards,
Igor Neyman
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-i
, 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
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@postgres
> -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:
> &
> 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 everybo
s
>
> 1;'cob0002'
> 2;'cob0008'
> 3;'cob0006'
> 4;'cob0004'
> 5;'cob0002'
> 6;'cob0007'
> 7;'cob0003'
> 8;'cob0004'
> 9;'cob0009'
> 10;'cob0001'
>
Try this:
Select
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
> 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" lik
t;
> (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
ase_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
kes 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
--
> > 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
rom 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
ng 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
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 functi
> -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
exander
>
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
"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
-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 i
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]
[mailt
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: Osva
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
Subjec
for almost any long-running query.
I'm moving those servers to PG, due to this (concurrency) and other reasons.
My top 3 reasons are: a much better concurrency (even with bg vacuums
running :-), a much better planner, and PG's rich feature set.
--
Best Regards,
Igor Shevchenk
Sophie,
The sql like this:
select * from tbl1 where (a, b) in ((1, 20), (2, 30), (3, 50));
works very well in PostgreSQL 8,
Sincerely,
Igor Katrayev, Data Systems Manager
North Pacific Research Board
1007 West Third Avenue, Suite 100
Anchorage, AK 99501
Phone: 907-644-6700
Fax: 907-644-6780
DROP TABLE "Facility Info"
Thank you,
Igor
"Michael Fuhr" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Fri, Apr 22, 2005 at 03:24:10PM +1000, Igor Kryltsov wrote:
> >
> > Please help to drop table with soace inside name.
> &
you,
Igor
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
which will insert value into PostgreSQL
> table 'test_p' from database 'test_db' running on host '10.3.2.5'.
>
> Can this be achieved with PostgreSQL ODBC driver? If yes, please post
> template of such trigger.
>
>
> Thank you,
>
>
> Igor
>
>
er? If yes, please post
template of such trigger.
Thank you,
Igor
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
script file which recreates all triggers in MSSQL
Hope it will save some time for somebody.
Igor
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so
p s2 WHERE s2.group_id = s1.group_id AND
facility_id = 999)
works but what if I need to find groups where membership is (facilityN1,
facilityN100)??
Thank you,
Igor K
---(end of broadcast)---
TIP 6: Have you searched our lis
eference manual and wasn't able to figure out a
solution, so here I am .. can aonyone help me? Which type should I use to
receive the return from the query? Are cast operations (for type conversions)
supported in PL/PgSQL?
Thanks for all, please help!
Regards,
Igor--[EMAIL PROTECTED]
messageinfo WHERE user_id = 2::bigint and msgstatus
=
0::smallint;
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> Artimenko Igor wrote:
> > I could force Postgres to use the best index by removing condition "msgstatus =
> > CAST( 0 AS
> > smallint );&
Copy from .. works fine for 1000 records to load. For data set of 6 records I
could never get
it finish. And I'm planing to reload 1000,000 records.
So there is a limit somewhere between 1,000 & 60,000 since it starts working slower.
The only
question for me left is. What config parameter(s)
UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime =
vlastreceivedtime WHERE
injector.id = vID::int8;
END IF;
RETURN NULL;
END;
'
LANGUAGE 'plpgsql';
=
Thanks a lot
Igor Artimenko
I specialize in
Java, J2EE, Unix, Linux, HP, AIX, Solari
v3 protocol with binary format (PostgreSQL 7.4+), the
overhead is minimal both ways.
--
Best regards,
Igor Shevchenko
---(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
Title: AIP - Assessoria Informática e Proteção LTDA
Someone knows how is going the implementation of cross database references
in pgsql ?
Would have some future release with this great functionality ?
Thanks,
Pedro Igor
ON depend.subfunction_dep_id =
b.subfunction_id;
"Igor" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]...
> Hi,
>
> I have following SQL statement which does not report any errors:
>
> test=# SELECT a.subfunction_file, b.subfunction_file
> test-# FR
I´m trying to config PG with SSL, but i got a
error. I create the key and the certificate and put both in $PGDATA
directory.
I also enabled the ssl option in
postgresql.conf.
But when i run postmaster i got a error saying that
server.key has wrong permissions.
Thanks,
Pedro Igor
How is this function ?
plpgsql_call_handler() RETURNS language_handler AS 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE;
---Outgoing mail is certified Virus Free.Checked by
AVG anti-virus system (http://www.grisoft.com).Version: 6.0.443 /
Virus Database: 248 - Release Date:
1/10/2003
I would like to know in how can i reference a table
in a database A from a database B.
In resume, i want to separate the data in my
database in two others databases and make references for them.
Thanks,
Pedro Igor
---Outgoing mail is certified Virus
Free.Checked by AVG anti-virus
I have tried : check (select
count(b.id) from B b where b.id = id) <> 0) but doesn´t work
..
I can use trigger here, but i
don´t know if is the best solution .
Thanks,
Pedro
Igor
Have someone used Resin EE with PostgreSQL or
actually use both for building applications ? About PostgreSQL i know that
is one of the best options for back-end, but what about Resin EE
?
Thanks ...
Pedro Igor
Does have PostgreSQL some option to allow me
execute selects accent-insensitive ?
I can´t find any reference, including the manual
Pedro Igor
Someone knows how config the postgresql for
case-insensitive mode ?
Pedro Igor
bles and not only to tbale
teste.
Someone know how ???
Pedro Igor
I´m looking for the name of the table that contains
all databases in my system. I already see this in the postgre manual, but i´m
forgot where
Tanks ...
Pedro Igor
Max Pyziur wrote:
> On Thu, 31 Aug 2000, Igor N. Avtaev wrote:
>
> > [EMAIL PROTECTED] wrote:
> >
> > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org
> > > on a RH6.2 install.
> > >
> > > I have a few questions on
[EMAIL PROTECTED] wrote:
> Currently, I'm using the the 7.0.2 rpms from the postgresql.org
> on a RH6.2 install.
>
> I have a few questions on it and the use of the -E flag.
>
> 1 - can 7.0.2 be optimized for i686 architecture or is
> it only possible to compile for i386 architecture?
Yes. Chang
53 matches
Mail list logo
