Re: [SQL] update column based on postgis query on anther table

.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

Re: [SQL] delete where not in another table

.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

Re: [SQL] array_agg() with join question

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: [SQL] Correct implementation of 1:n relationship with n>0?

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

Re: [SQL] Using Ltree For Hierarchical Structures

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-i

Re: [SQL] Using Ltree For Hierarchical Structures

, 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

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

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: > &

Re: [SQL] using ordinal_position

> 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

Re: [SQL] map row in one table with random row in another table

s > > 1;'cob0002' > 2;'cob0008' > 3;'cob0006' > 4;'cob0004' > 5;'cob0002' > 6;'cob0007' > 7;'cob0003' > 8;'cob0004' > 9;'cob0009' > 10;'cob0001' > Try this: Select

Re: [SQL] SQL View to PostgreSQL View

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

Re: [SQL] Concurrent Reindex on Primary Key for large table

> 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

Re: [SQL] which is better: using OR clauses or UNION?

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

Re: [SQL] which is better: using OR clauses or UNION?

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

Re: [SQL] to_char() accepting invalid dates?

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

Re: [SQL] After insert trigger and select

-- > > 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

Re: [SQL] Function compile error

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

Re: [SQL] sqlplus reporting equivalent in postgres?

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

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 functi

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

Re: [SQL] Use "CREATE USER" in plpgsql function

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

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-sq

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 i

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] [mailt

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@postgresq

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