Re: [GENERAL] Support functions for GiST index on citext

Have you considered normalizing? Here's a SQLFiddle example: http://sqlfiddle.com/#!15/61897/3/0. It uses text instead of citext, but I imagine your results should be similar. Also, I think usage of citext is generally recommended against. The basic idea is to not use an array but use a second ta

Re: [GENERAL] oracle to postgres

> > BEGIN > EXECUTE IMMEDIATE 'DROP TABLE CONTAINER'; > EXCEPTION > WHEN OTHERS THEN >IF SQLCODE != -942 THEN > RAISE; >END IF; > END; > Jim nailed it. In PostgreSQL, this is just DROP TABLE IF EXISTS CONTAINER; One line. No dynamic SQL, exception block, or even

Re: [GENERAL] How do I bump a row to the front of sort efficiently

On Mon, Feb 2, 2015 at 1:16 AM, Sam Saffron wrote: > However, the contortions on the above query make it very un-ORM > friendly as I would need to define a view for it but would have no > clean way to pass limits and offsets in. > This is why ORMs are bad. They make hard problems *much* harder,

Re: [GENERAL] How do I bump a row to the front of sort efficiently

On Tue, Feb 3, 2015 at 9:33 PM, BladeOfLight16 wrote: > This is why ORMs are bad. They make hard problems *much* harder, and the > only benefit is that they maybe make easy problems a little quicker. The > cost/savings is *heavily* skewed toward the cost, since there's no upper

Re: [GENERAL] How do I bump a row to the front of sort efficiently

On Tue, Feb 3, 2015 at 11:28 PM, Sam Saffron wrote: > Note: I still consider this a bug/missing feature of sorts since the > planner could do better here, and there is no real clean way of > structuring a query to perform efficiently here, which is why I > erroneously cross posted this to hacker

Re: [GENERAL] Change postgresql encoding

On Sun, Feb 8, 2015 at 2:20 PM, Oliver wrote: > If I want change postgresql encoding, I have understood that I should > reinstall postgresql (I do installation from rpm official binary files for > red hat) > I can't answer your question about whether the encodings need to be the same, but I'm pr

Re: [GENERAL] Failure loading materialized view with pg_restore

On Wed, Feb 18, 2015 at 5:48 AM, Brian Sutherland wrote: > # dump and reload > pg_dump --username super --format c -f dump.dump orig > createdb copied > It might be helpful to dump in the plain SQL format and look at what it's doing.

Re: [GENERAL] Application written in pure pgsql, good idea?

On Sat, Feb 28, 2015 at 3:39 PM, inspector morse wrote: > > Is it a good idea to write a simple application (consisting of just data > entry interfaces) in pure pgsql? > > Basically, we would have each page has a stored function in postgresql > that is called by php+apache (the http get/post valu

[GENERAL] point_ops with GiST PostGIS Spatial Index

I posted this question on StackOverflow, and the only person to answer recommended I ask these lists for more details and link to the question: http://stackoverflow.com/questions/16927331/postgresql-point-ops-with-gist-postgis-spatial-index My question is: The 9.0 release notes

Re: [GENERAL] [postgis-users] point_ops with GiST PostGIS Spatial Index

ronic mail > or telephone, of any unintended recipients and delete the original message > without making any copies. > > ** ** > > *From:* postgis-users-boun...@lists.osgeo.org [mailto: > postgis-users-boun...@lists.osgeo.org] *On Behalf Of *BladeOfLight16 >

Re: [GENERAL] Update big table

I don't believe you can use JOIN explicitly in this situation because it's an UPDATE, but I believe you can accomplish the same effect with FROM and WHERE. UPDATE table SET column1 = TRUE FROM table2 WHERE table1.event_id = table2.event_id; I would make sure there's an index on table2.event_id if

[GENERAL] Add a NOT NULL column with default only during add

When I want to add a new column with a NOT NULL constraint, I need to specify a DEFAULT to avoid violations. However, I don't always want to keep that DEFAULT; going forward after the initial add, I want an error to occur if there are inserts where this data is missing. So I have to DROP DEFAULT on

Re: [GENERAL] Add a NOT NULL column with default only during add

On Thu, Aug 1, 2013 at 7:10 PM, Richard Broersma wrote: > Notice : > http://www.postgresql.org/docs/9.3/static/sql-altertable.html > After you add a column to your table, you can latter *alter* this column > to add, change, or remove the default expression. There's no need add > temporary column

Re: [GENERAL] demystifying nested loop vs. merge join query plan choice

On Thu, Aug 1, 2013 at 10:25 AM, Sandeep Gupta wrote: > @Jeff : Thanks for pointing this out. Turns out that was the case. > > @Tom: Thank you for the reference to random_page_cost parameters. It would > be very useful for us. Would go through the rest of the documentation as > well. > I can't sa

Re: [GENERAL] Add a NOT NULL column with default only during add

On Thu, Aug 1, 2013 at 7:53 PM, Adrian Klaver wrote: > It fails because > > ALTER TABLE x > ADD COLUMN data2 VARCHAR(10) NOT NULL DEFAULT 'foo', > > end in , instead of ; > > You have to add the column before you can alter it. > =/ That's the way I have it in the SQL Fiddle sample I provided. I w

Re: [GENERAL] Add a NOT NULL column with default only during add

On Thu, Aug 1, 2013 at 8:15 PM, Adrian Klaver wrote: > What you want is a default that only works during ALTER ADD COLUMN. At > that point though, there is no data added and DEFAULT only works with > INSERTS. Your example of USING with ALTER data_type works because there > actually may be rows alr

Re: [GENERAL] postgres FDW cost estimation options unrecognized in 9.3-beta1

On Fri, Jul 26, 2013 at 6:28 PM, Tom Lane wrote: > > > I think we could do with both more documentation, and better error > messages for these cases. In the SET-where-you-should-use-ADD case, > perhaps > > ERROR: option "use_remote_estimate" has not been set > HINT: Use ADD not SET to define a

Re: [GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)

On Fri, Aug 2, 2013 at 10:18 AM, Adrian Klaver wrote: > You can't do that and have it work. \base is just part of the puzzle, you > need the complete \data directory for Postgres to work. > > The problem(as stated before) is you have two distinct installations of > the Postgres data directory, one

Re: [GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)

On Sat, Aug 3, 2013 at 7:16 AM, Alban Hertroys wrote: > They are cluster specific, as the roles are stored in the database. If you > switch between different data directories, that means you're switching the > available roles as well. And their details, such as passwords. You're also > switching

Re: [GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)

On Mon, Aug 5, 2013 at 2:21 AM, Thomas Kellerer wrote: > Stephen Brearley, 02.08.2013 11:40: > Why don't you use the official way to re-configure the Windows service and > use pg_ctl unregister and pg_ctl register > to make it use the correct data directory. There is no need to manually > change

[GENERAL] Staging Database

The company I work for has a client who has expressed interest in having a staging database for their data. Staging as in they add data, do some QCing, then push to a database with an identical schema to make it public. Fortunately, we're not doing that right now, but it's something they may want l

Re: [GENERAL] Staging Database

On Wed, Aug 7, 2013 at 4:43 AM, Luca Ferrari wrote: > Not really helpful, but here are my considerations. > The low frequency and the preference for a single server suggest me a > dump and restore cycle on two databases, assuming this is possible due > to not high volume data. > I would also cons

Re: [GENERAL] Seemingly inconsistent ORDER BY behavior

On Wed, Aug 14, 2013 at 2:56 PM, Tom Lane wrote: > Our interpretation is that a bare column name ("ORDER BY foo") is resolved > first as an output-column label, or failing that as an input-column name. > However, as soon as you embed a name in an expression, it will be treated > *only* as an inpu

Re: [GENERAL] Denormalized field

On Mon, Aug 19, 2013 at 4:27 AM, Vik Fearing wrote: > Yes, I would use a trigger for this. > > > This is definitely the right answer, but keep in mind that this will slow down your inserts since it calls slow_function for each insert. Make sure you can afford that performance hit.

Re: [GENERAL] Column names for INSERT with query

On Thu, Aug 22, 2013 at 6:36 PM, Ian Lawrence Barwick wrote: > INSERT INTO t (t_field1, t_field2, t_field3) > SELECT r.field1, r.field2, (x+y) > FROM r > Obligatory link to documentation: http://www.postgresql.org/docs/9.2/static/sql-insert.html. See where it says [ ( *column_name* [, ...]

Re: [GENERAL] how to use aggregate functions in this case

On Sun, Aug 25, 2013 at 5:59 PM, Janek Sendrowski wrote: > SELECT v_rec1.user, > sum(CASE WHEN v_rec_fts.lev BETWEEN 0 AND 25 THEN 1 ELSE 0 END) as > "0 to 25", > sum(CASE WHEN v_rec_fts.lev BETWEEN 25 AND 50 THEN 1 ELSE 0 END) > as "25 to 50", > sum(CASE WHEN v_rec_fts.l

Re: [GENERAL] how to use aggregate functions in this case

On Sun, Aug 25, 2013 at 8:36 PM, BladeOfLight16 wrote: > This appears to be some kind of equal interval problem. > > SELECT v_rec1.user, > WIDTH_BUCKET(v_rec_fts.lev, 0, 100, 4) AS bucket > COUNT(*) as count, > FROM v_rec2 > GROUP BY user, bucket; > >

Re: [GENERAL] Please help me regarding the WITH RECURSIVE query

On Mon, Aug 26, 2013 at 3:17 AM, gajendra s v wrote: > Please explain me why it is ? > A good place to start would be removing all the parts here that don't seem to matter. Your problem seems to be with the recursive query (since that is the part you're changing). Cut off everything else and com

Re: [GENERAL] A QUESTION ABOUT evaluation of two-dimensional Associative Arrays

On Tue, Aug 27, 2013 at 9:24 AM, superzhangfei wrote: > When the package be excused,an error occured. > Hello. First, can you reproduce the error without the PACKAGE? As in just CREATE TYPE and CREATE PROCEDURE statements? If so, what are the errors? And I think you mean "executed" instead of "e

Re: [GENERAL] SQL Path in psql

On Fri, Sep 6, 2013 at 1:58 PM, David Kerr wrote: > I suspect this feature makes more sense on a windows platform. On linux > where we can > go psql -f ${SQLPATH}/file.sql . it becomes less pressing. Even Oracle > on unix/linux where you can go sqlplus < makes > it less a requirement. > I don't

Re: [GENERAL] Sum of columns

On Mon, Sep 9, 2013 at 8:12 AM, Marc Mamin wrote: > hi, > > in addition to the others comments, you can also remove " ELSE 0 " from > your query. > > It will result in values that are discarded by SUM. > > For that matter, you could clean this up by using COUNT as your aggregate with a

Re: [GENERAL] Incorrect index being used

On Fri, Oct 11, 2013 at 9:32 AM, Jesse Long wrote: > explain select * from archive_document_index where node_id = 29 and value > = 'BSH70002152'; > QUERY PLAN > --**--** > -

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

I've only skimmed this thread, but clearly, this is why using functions with side effects in the middle of complex queries is a bad idea. =) Something like SELECT func_with_side_effect(1); is probably fine, but beyond that, put the function in the middle of a DO block or something and actually code

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

On Mon, Oct 21, 2013 at 6:52 PM, BladeOfLight16 wrote: > In my opinion, the simplest and most correct way to handle this is to > document that there are no guarantees about what will happen with volatile > functions in these strange cases. PostgreSQL shouldn't have to make >

Re: [GENERAL] pg_dumpall from a script

On Tue, Oct 22, 2013 at 1:20 AM, James Sewell wrote: > That looks great, but it doesn't really help with my problem unless I'm > missing something (very possible!) > > I need a way to backup either from SQL in PSQL (possibly \!) or from a > PG/PLSQL function to a file with a name set from a :varia

Re: [GENERAL] Bug? Function with side effects not evaluated in CTE

On Tue, Oct 22, 2013 at 3:15 PM, Moshe Jacobson wrote: > > Here is the full code. It is not “minimal”, but actually what we are using. > fn_get_create_or_update_space_sku() will create a non-existent row, or > update it with the passed-in data if it already exists. > You’ll notice that in this ver

Re: [GENERAL] changing port numbers so pgbouncer can read geoserver and postgres

On Fri, Nov 1, 2013 at 9:29 AM, Birta Levente wrote: > I don't know what is this geoserver, but this port 8080 I think it's not > relevant in this. > I might be able to help: http://geoserver.org/display/GEOS/Welcome. GeoServer is a Java web application that retrieves GIS data (as in PostGIS in