Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

On 10/21/2010 10:45 AM, Scott Marlowe wrote: > On Thu, Oct 21, 2010 at 10:37 AM, Rob Sargent wrote: >> >> >> On 10/21/2010 10:27 AM, Scott Marlowe wrote: >>> On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt wrote: >>>>> >>>>> There are onl

Re: [GENERAL] Performance implications of creating many, many sequences

Is this "invoice_number" just an id or what might appear an a bill (in some pretty form etc)? If the former, just get a unique id over all invoices. At the very least it will save time i) in writing where clauses ii) re-creating the correct id once some one assigns an invoice to the wrong custome

Re: [GENERAL] exceptionally large UPDATE

Ivan Sergio Borgonovo wrote: I've to make large UPDATE to a DB. The largest UPDATE involve a table that has triggers and a gin index on a computed tsvector. The table is 1.5M records with about 15 fields of different types. I've roughly 2.5-3Gb of ram dedicated to postgres. UPDATE queries are

Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table

This one aught to be good! The tool is after all called "pgAdmin" rather that say "pgBrowser". I think you have a "teaching opportunity" here. There is a feature for getting the first N rows that might help (a lot). There is query-by-example as well. I can't really imagine the value of being ab

Re: [GENERAL] Implementing replace function

\df *update* Alexander Farber wrote: I've created a function now (below) and can call it as well, but how can I see it at the psql prompt? Is there a \d command for that or should I dump the database to see my declarations? And is my function atomic? I.e. can't it happen, that FOUND is not true

Re: [GENERAL] Temporary schemas

On 11/01/2010 04:13 PM, Merlin Moncure wrote: > On Mon, Nov 1, 2010 at 4:27 PM, Thomas Kellerer wrote: >> The problem is, that the JDBC driver only returns information about the temp >> tables, if I specify that schema directly. > > Have you filed a bug report to jdbc yet? :-D. > > merlin >

Re: [GENERAL] Return key from query

On 11/02/2010 02:43 PM, Jonathan Tripathy wrote: > Hi everyone, > > When adding a new record, we run an insert query which auto-increments > the primary key for the table. However the method (in java) which calls > this query must return the newly created key. > > Any ideas on how to do this, p

Re: [GENERAL] Return key from query

On 11/03/2010 02:08 AM, Szymon Guz wrote: > > > On 3 November 2010 00:41, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > > > > On 11/02/2010 03:03 PM, Szymon Guz wrote: > > > > > > On 2 November 2010 21:59

Re: [GENERAL] Finding rank of a single record

Alexander Farber wrote: Hello, I have 2 tables with user infos (please see \d output at the bottom) and would like to find their rank depending on their "money". When I select all records, the rank() works fine: pref=> select u.first_name, u.city, m.money, rank() over (order by money desc) fro

Re: [GENERAL] Finding rank of a single record

Alexander Farber wrote: And: pref=> select * from ( select u.id, u.first_name, u.city, m.money, rank() over (order by money desc) from pref_users u, pref_money m where m.yw=to_char(current_timestamp, '-IW') ) ar where ar.id='OK1382399

Re: [GENERAL] I guess I'm missing something here WRT FOUND

On 11/08/2010 09:11 PM, Ralph Smith wrote: > How is "COLLEEN" not there and there at the same time? > - > NOTICE: did not = 11K = 42 > CONTEXT: PL/pgSQL function "get_word" line 37 at perform > NOTIC

Re: [GENERAL] Basic Tutorials for 9.0

ray wrote: I just downloaded 9.0 onto my laptop and desktop Windows XP machines. Nether one could run the stack builder. Windows would fault any time I tried to run it. I tried inputing the proxy server but that did not help. I have found many tutorials but none look like the 9.0. Many seem

Re: [GENERAL] Schema tool

On 11/11/2010 09:50 AM, Aram Fingal wrote: > A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL > which had a feature where it would display a graphic schema of whatever > database you connect to but I can't seem to find it again (web searching.) > I did come acros

Re: [GENERAL] More then 1600 columns?

On 11/12/2010 08:38 AM, Mark Mitchell wrote: > Yes I understand that this is "bad design" but what we are doing is storing > each form field in a survey in its own column. For very long surveys we end > up with thousands of elements. > I know storing in an array is possible but it makes it so

Re: [GENERAL] More then 1600 columns?

On 11/12/2010 02:25 PM, Clark C. Evans wrote: > On Fri, 12 Nov 2010 21:10 +, "Dann Corbit" wrote: >> If (for access) the single table seems simpler, then >> a view can be used. > > Even if you "partition" the columns in the instrument > over N tables, you still can't query it in a single >

Re: [GENERAL] if-clause to an exiting statement

Jasen Betts wrote: On 2010-12-07, Kobi Biton wrote: hi i am a newbie to sql statments , I am running postgres 8.1 with application called opennms version 1.8.5 due to an application bug queries that I execute aginst the DB which returns raw-count=0 are being ignored and will not process a

Re: [GENERAL] 2 versions of an entity worth distinct table?

But then a) because you can't guarantee this design won't 'improve' and b) you would like to look in one place for all addresses, normalize now. Thomas Kellerer wrote: gvim wrote on 27.12.2010 02:47: If a table representing contact details can have 2 but no more than 2 email addresses is it re

Re: [GENERAL] UUID column as pimrary key?

On 01/05/2011 08:55 AM, Bill Moran wrote: > In response to Scott Ribe : > >> On Jan 5, 2011, at 8:05 AM, Bill Moran wrote: >> >>> Beyond that, the namespace size for a UUID is so incomprehensibly huge >>> that the chance of two randomly generated UUIDs having the same value >>> is incomprehensib

Re: [GENERAL] UUID column as pimrary key?

On 01/05/2011 11:57 AM, Bill Moran wrote: > In response to Rob Sargent : > >> >> >> On 01/05/2011 08:55 AM, Bill Moran wrote: >>> In response to Scott Ribe : >>> >>>> On Jan 5, 2011, at 8:05 AM, Bill Moran wrote: >>>> >>&g

Re: [GENERAL] UUID column as pimrary key?

On 01/05/2011 01:43 PM, Bill Moran wrote: > In response to Rob Sargent : >>> >>> In our case (and I expect it's the case with most people considering UUIDs) >>> we're talking about independent devices that occasionally synchronize >>> data betw

Re: [GENERAL] UUID column as pimrary key?

On 01/05/2011 03:39 PM, Alban Hertroys wrote: >>> From wikipedia, "only after generating 1 billion UUIDs every second for >> the next 100 years, the probability of creating just one duplicate would >> be about 50%. The probability of one duplicate would be about 50% if >> every person on earth ow

Re: [GENERAL] CRUD functions, similar to SQL stored procedurs, for postgresql tables?

margaretgil...@chromalloy.com wrote: We use some SQLserver databases that have stored procedures for all C.R.U.D. functions so the same code is used no matter what language the developer is working in. The procedures are built by a master package that reads the table structures and creates the

Re: [GENERAL] Schema version control

Top-posting is frowned upon by some (not me), but since Bill started it... I for one will be waiting to see your dbsteward. How does it compare functionally or stylistically with Ruby's migration tools (which I found to be pretty cool and frustrating all in one go). On 02/10/2011 03:18 PM, Bill

Re: [GENERAL] Schema version control

On 02/10/2011 03:59 PM, Bill Moran wrote: > In response to Rob Sargent : > >> Top-posting is frowned upon by some (not me), but since Bill started it... > > Oops ... the weird thing is that I'm usually really anal about not top- > posting ... > >> I

Re: [GENERAL] Schema version control

On 02/10/2011 02:38 PM, Royce Ausburn wrote: > Hi all, > > My company is having trouble managing how we upgrade schema changes across > many versions of our software. I imagine this is a common problem and > there're probably some neat solutions that we don't know about. > > For the last 1

Re: [GENERAL] Schema version control

On 02/10/2011 04:44 PM, Bill Moran wrote: > In response to Rob Sargent : >> >> On 02/10/2011 03:59 PM, Bill Moran wrote: >>> In response to Rob Sargent : >>>> I for one will be waiting to see your dbsteward. How does it compare >>>> functiona

Re: [GENERAL] help understanding explain output

Luca Ferrari wrote: Hello, I've got a doubt about partial indexes and the path chosen by the optimizer. Consider this simple scenario: CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY KEY (pk) ); INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,100),

Re: [GENERAL] Permission denied when inserting

On 02/28/2011 07:37 AM, Borek Lupomesky wrote: > Hello, > >I have a database app that worked fine until we reinstalled the > server with the related DB dump and restore. Most of the stuff works > fine after the reinstall, but one particular insert gives very cryptic > (for me) message: > > sp

[GENERAL] close connection more expensive that open connection?

A developer here accidentally flooded a server with connection opens and closes, essentially one per transaction during a multi-threaded data migration process. We were curious if this suggests that connection clean up is more expensive than creation thereby exhausting resources, or if perhaps the

[GENERAL] opening connection more expensive than closing connection?

A developer here accidentally flooded a server with connection opens and closes, essentially one per transaction during a multi-threaded data migration process. We were curious if this suggests that connection clean up is more expensive than creation thereby exhausting resources, or if perhaps the

Re: [GENERAL] opening connection more expensive than closing connection?

top posting my self to clarify, since the title is in fact inverted: close might be more expensive the open. On 03/03/2011 08:28 AM, Rob Sargent wrote: > A developer here accidentally flooded a server with connection opens and > closes, essentially one per transaction during a multi-th

[GENERAL] closing connection more expensive than opening connection?

A developer here accidentally flooded a server with connection opens and closes, essentially one per transaction during a multi-threaded data migration process. We were curious if this suggests that connection clean up is more expensive than creation thereby exhausting resources, or if perhaps the

Re: [GENERAL] updating all records of a table

On 03/04/2011 04:54 AM, Vibhor Kumar wrote: > > On Mar 4, 2011, at 5:17 PM, Andrew Sullivan wrote: > >> On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote: >>> Hi: >>> >>> I have to update all the records of a table. I'm worried about what the >>> table will look like in terms of

Re: [GENERAL] School teacher in need of HELP

hbaile...@nyc.rr.com wrote: Hello, I have no IT experience and no funding to hire a technical person or resources to take a training course. Also my friends who have programming background have not been reliable. I am willing to spend the necessary months in trying to learn how to use an SQL

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed.

Brian Hirt wrote: On Mar 13, 2011, at 12:05 PM, Dmitriy Igrishin wrote: Hey Viktor, 2011/3/13 Viktor Nagy > hi, when trying to insert a long-long value, I get the following error: index row size 3120 exceeds maximum 2712 for index "ir_tran

[GENERAL] psql can't subtract

Running 9.0.3 (client and server) Seems I cannot subtract 1 from the result of position. select distinct substring( substring(xml_text,1,300), position( 'xmlns=' in substring(xml_text,1,300)) + length('xmlns="'), position( '"' in (substring(substring

Re: [GENERAL] psql can't subtract

On 03/25/2011 09:57 AM, Richard Huxton wrote: On 25/03/11 15:29, Rob Sargent wrote: Running 9.0.3 (client and server) select distinct substring( ... position( 'xmlns=' in substring(xml_text,1,300)) + length('xmlns="'), 100))) -1 ... gives ERROR: negative sub

Re: [GENERAL] psql can't subtract

On 03/25/2011 10:59 AM, hubert depesz lubaczewski wrote: On Fri, Mar 25, 2011 at 09:29:57AM -0600, Rob Sargent wrote: Running 9.0.3 (client and server) Seems I cannot subtract 1 from the result of position. select distinct substring( substring(xml_text,1,300

[GENERAL] scary xpath_table behaviour

When using pipe separated xpath statements to fill multiple columns of output, there is no guarantee of correlation among the columns of the rows returned. Using locally built 9.0.3 (--with-libxml) I'm dealing with an element which has one fixed attribute (@page-layout) and either (@collecti

Re: [GENERAL] scary xpath_table behaviour

On 04/04/2011 03:12 PM, Tom Lane wrote: Rob Sargent writes: When using pipe separated xpath statements to fill multiple columns of output, there is no guarantee of correlation among the columns of the rows returned. Yeah, this is a known problem mentioned in our TODO list. Nobody has any

Re: [GENERAL] unique amount more than one table

On 04/05/2011 04:02 PM, Perry Smith wrote: I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables. I thought I could first create a view with something like: SELECT name, 'table1' as type from table1 UNION

Re: [GENERAL] temp tables not dropping at end of script

On 04/06/2011 08:12 AM, Davenport, Julie wrote: Since we upgraded from postgres 8.0 to 8.4, every script where we have queries that use temp tables now has to have an explicit drop of the temp table at the end of the script, or it will blow up the next time it runs, saying it cannot create the t

Re: [GENERAL] Auto Adjust Age

On 04/06/2011 08:53 AM, Carlos Mennens wrote: I've only been using PostgreSQL since Oct 2010 and it's my first experience with SQL or any ORDBMS. I've searched on the web and been creating my own database users, databases, tables from scratch which has been interesting to say the least but now

Re: [GENERAL] Auto Adjust Age

On 04/06/2011 08:53 AM, Carlos Mennens wrote: I've only been using PostgreSQL since Oct 2010 and it's my first experience with SQL or any ORDBMS. I've searched on the web and been creating my own database users, databases, tables from scratch which has been interesting to say the least but now

Re: [GENERAL] PostgreSQL backend process high memory usage issue

On 04/07/2011 03:46 PM, John R Pierce wrote: On 04/07/11 1:42 PM, Shianmiin wrote: Since the connection pool will be used by all tenants, eventually each connection will hit all the tables/views. don't all connections in a given pool have to use the same user credentials? won't that be prob

Re: [GENERAL] Howto sort the result of UNION (without modifying its type)?

On 04/08/2011 03:44 PM, Clemens Eisserer wrote: Hi, I have a query where I UNION several select statements which return IDs of type INTEGER. This works well, if the IDs don't need to be sorted: SELECT id FROM table WHERE id IN ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WH

Re: [GENERAL] Rename or Re-Create Constraints?

Carlos Mennens wrote: On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: I believe you can rename the underlying indexes and the constraints will follow them. (This works in HEAD anyway, not sure how far back.) I'm sorry but I don't understand what that means or how to relate that to

Re: [GENERAL] interpret bytea output as text / double encode()

Sent from my iPhone > On Jun 6, 2014, at 7:00 AM, Stefan Froehlich > wrote: > > In one of my databases, I have mistakenly double encoded bytea > values (i.e. the content is literally '\x202020...' which would have > to be decoded once more to get the actually desired content). > > But how to

Re: [GENERAL] Overlapping ranges

On 06/18/2014 05:47 PM, Jason Long wrote: I have a large table of access logs to an application. I want is to find all rows that overlap startdate and enddate with any other rows. The query below seems to work, but does not finish unless I specify a single id. select distinct a1.id from t_acce

Re: [GENERAL] how to create multiple databases running in different dirs

On 06/30/2014 05:58 PM, frank ernest wrote: Hi, I'm new to postgresql and sql in general. I desired to write a program in C that used an sql data base for IPC and because multiple copies of my program might run on the same machine I wanted a way to ensure that only one copy of each multithreade

Re: [GENERAL] How to implement a uniqueness constraint across multiple tables?

On 07/31/2014 12:38 PM, Kynn Jones wrote: I want to implement something akin to OO inheritance among DB tables. The idea is to define some "superclass" table, e.g.: CREATE TABLE super ( super_id INT PRIMARY KEY, ... -- other columns ); CREATE TABLE sub_1 (

Re: [GENERAL] How to implement a uniqueness constraint across multiple tables?

On 07/31/2014 01:16 PM, Marti Raudsepp wrote: On Thu, Jul 31, 2014 at 9:38 PM, Kynn Jones wrote: Does PostgreSQL have a good way to enforce the uniqueness of super_id values across multiple tables? Well that's easy: no. Regards, Marti That might be a little hasty. There are conditional def

Re: [GENERAL] create function : change name of input parameter

Include the types in the drop Sent from my iPhone > On Aug 20, 2014, at 7:59 AM, Adrian Klaver wrote: > >> On 08/20/2014 06:51 AM, Lori Corbani wrote: >> >> I *am* definitely dropping the function first. I still get the same error. > > Well we need to see the actual sequence to figure this

Re: [GENERAL] Feature request: temporary schemas

Interesting enough concept. Please don't forget to test against a realistic data set as well. It does seem to me that the devs can easily make, fill, clean up their own db. And a central builder (eg Jenkins?) can do the same with, importantly using ALL tests. Then again using real data. > On

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 09/15/2014 11:49 AM, cowwoc wrote: Hi Pavel, On 15/09/2014 1:40 PM, Pavel Stehule wrote: The main drivers are: 1. Not having to learn yet another language. I find the expressiveness and readability of the other scripting languages very clunky compared to Java. PLpgSQL

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 09/19/2014 02:10 PM, Dmitriy Igrishin wrote: 2014-09-15 9:22 GMT+04:00 cowwoc >: Hi, Out of curiosity, why is Postgresql's Java support so poor? I am specifically looking for the ability to write triggers in Java. I took a look at the PL

Re: [GENERAL] ALTER TEXT field to VARCHAR(1024)

On 09/22/2014 09:40 AM, John McKown wrote: On Mon, Sep 22, 2014 at 10:31 AM, Merlin Moncure wrote: On Fri, Sep 19, 2014 at 7:16 AM, Bill Moran wrote: On Fri, 19 Sep 2014 09:32:09 +0200 Marius Grama wrote: Can anybody explain me what happens in the background when the alter statement is exec

Re: [GENERAL] Any postgres API available to get errorcode for PQerrorMessage

On 10/15/2014 02:17 AM, Roopeshakumar Narayansa Shalgar (rshalgar) wrote: HI, PQerrorMessage pirints the error message like (no space available,etc). Does postgres provide any API which gives the error code listed in the below appendix; http://www.postgresql.org/docs/9.1/static/errcodes-ap

Re: [GENERAL] COPY data into a table with a SERIAL column?

On 10/16/2014 10:33 AM, Steve Wampler wrote: Hi, This is with Postgresql 9.3.5. I'm looking at using a COPY command (via jdbc) to do bulk inserts into a table that includes a BIGSERIAL column. Is there a way to mark the data in that column so it gets assigned a new value on entry - akin to

Re: [GENERAL] COPY data into a table with a SERIAL column?

On 10/16/2014 11:04 AM, Steve Wampler wrote: On 10/16/2014 09:42 AM, Rob Sargent wrote: On 10/16/2014 10:33 AM, Steve Wampler wrote: This is with Postgresql 9.3.5. I'm looking at using a COPY command (via jdbc) to do bulk inserts into a table that includes a BIGSERIAL column. Is th

Re: [GENERAL] COPY data into a table with a SERIAL column?

On 10/16/2014 11:38 AM, David G Johnston wrote: Steve Wampler wrote Let me generalize the problem a bit: How can I specify that the default value of a column is to be used with a COPY command when some rows have values for that column and some don't? If you provide a value for a column, includ

Re: [GENERAL] COPY data into a table with a SERIAL column?

On 10/16/2014 11:52 AM, David G Johnston wrote: On Thu, Oct 16, 2014 at 11:44 AM, lup [via PostgreSQL] <[hidden email] >wrote: I appreciate the vastness of bigserial but I think it starts at 1. Are negative numbers even allowed? ​http://www.postgresql.org/docs/9.3/static/sql-create

Re: [GENERAL] Tablespace limit feature

On 11/04/2014 10:09 AM, Guillaume Lelarge wrote: Le 4 nov. 2014 18:00, "Alejandro Carrillo" > a écrit : > > I need to limit the tablespace file because I need to control the hard disk space used in a tablespace. Anybody knows how to do this? Or if it this in the Pos

Re: [GENERAL] Modeling Friendship Relationships

On 11/11/2014 03:38 PM, Robert DiFalco wrote: I have a question about modeling a mutual relationship. It seems basic but I can't decide, maybe it is 6 of one a half dozen of the other. In my system any user might be friends with another user, that means they have a reciprocal friend relationsh

Re: [GENERAL] [sfpug] Linuxfest 2015 Call for Papers

On 11/19/2014 12:57 PM, Adrian Klaver wrote: On 11/19/2014 11:47 AM, Josh Berkus wrote: On 11/18/2014 04:23 PM, Joshua D. Drake wrote: Forwarding this because they are having a dedicated PostgreSQL track put on by PgUS. I hope all will submit papers. This is a great conference of about 1500. I

Re: [GENERAL] Stored procedure workflow question

On 12/10/2014 05:53 PM, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my "DB Commands" text file - Copy and paste function into my development database - Test - repeat abo

Re: [GENERAL] Blocking access by remote users for a specific time period

Turn the app off? Seems that's one of the selling points of web apps. Throw up a "under maint" page. Sent from my iPhone > On Dec 13, 2014, at 9:13 PM, Michael Nolan wrote: > > I have several web apps that access our Postgresql database that I'd like to > lock out of the database for about

Re: [GENERAL] localtime(0)

On 12/29/2014 09:32 AM, Suresh Raja wrote: Hi: I'm using localtime(0) in various part of the function, and updating table with the time. I use localtime at places like the start of the function and at the end of the function. The function takes about 5 mins to run,. The issue is that the fun

Re: [GENERAL] PSQL/pgAdmin - Column Completion

On 12/31/2014 12:51 PM, Tom Lane wrote: dvlsg writes: I have been having issues with autocomplete in pgAdmin. After some searching, I found it was my mistake and that pgAdmin doesn't actually support column autocompletion in select statements. I found that pgAdmin uses the autocomplete code dir

Re: [GENERAL] PSQL/pgAdmin - Column Completion

On 12/31/2014 01:55 PM, John R Pierce wrote: On 12/31/2014 11:56 AM, Rob Sargent wrote: I think I see the autocompleters lining up now: just "my" schemas we have single schemas with 100s of tables. I'll type the from clause first but, the sql command is SELECT field,fi

Re: [GENERAL] Advice for using integer arrays?

On 01/06/2015 10:09 AM, Michael Heaney wrote: I'm fairly new to Postgres, and have a design issue for which an array of integers might be a good solution. But I'd like to hear from the experts before proceeding down this path. Essentially, I'm trying to model the relationship between a group

Re: [GENERAL] Indexing large table of coordinates with GiST

On 01/15/2015 05:44 AM, Daniel Begin wrote: Hi, I'm trying to create an index on coordinates (geography type) over a large table (4.5 billion records) using GiST... CREATE INDEX nodes_geom_idx ON nodes USING gist (geom); The command ran for 5 days until my computer stops because a power outage!

Re: [GENERAL] partitioning query planner almost always scans all tables

I don't understand having both UUID and time stamp in your PK? The first is by defn. unique and the second might be. Sent from my iPhone > On Jan 19, 2015, at 6:12 AM, Spiros Ioannou wrote: > > Hello group, > we have a timeseries table, and we tried to partition it by month (with > pg_partma

Re: [GENERAL] Fwd: Ask for a question

On 01/21/2015 11:02 AM, Pierre Hsieh wrote: Hi Raymond, Thanks for your reply. Please see detail as following. Thanks again. Pierre Inline image 1 On Thu, Jan 22, 2015 at 1:48 AM, Raymond O'Donnell > wrote: On 21/01/2015 17:32, Pierre Hsieh wrote: > Hi guys,

Re: [GENERAL] Fwd: Ask for a question

On 01/21/2015 11:31 AM, Pierre Hsieh wrote: updated rule Inline image 1 On Thu, Jan 22, 2015 at 2:28 AM, Pierre Hsieh > wrote: Thanks for your reply. Let me to describe the purpose for this calculation roughly. Column B is for the price of stock.

Re: [GENERAL] Some indexing advice for a Postgres newbie, please?

On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel

Re: [GENERAL] Some indexing advice for a Postgres newbie, please?

On 02/19/2015 10:19 AM, brian wrote: On Thu, 19 Feb 2015 09:30:57 -0700, you wrote: On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend

Re: [GENERAL] query by partial timestamp

On 01/08/2013 03:39 PM, Raymond O'Donnell wrote: On 08/01/2013 22:26, Raymond O'Donnell wrote: On 08/01/2013 22:19, Kirk Wythers wrote: I have a column of type TIMESTAMP, I'd like to query all records from 2011. If it were text I could use a partial such as: WHERE text ~ '2011' There must be

Re: [GENERAL] SELECT * and column ordering

On 01/15/2013 05:22 PM, Meta Seller Dev/Admin wrote: Hi! (I'm Chris Angelico posting from a work account - usually I'm here under the name ros...@gmail.com.) I've run into a bit of an awkwardness in PostgreSQL setup and am hoping for some expert advice. Several of the tables I work with have tw

Re: [GENERAL] SELECT * and column ordering

On 01/15/2013 07:08 PM, Meta Seller Dev/Admin wrote: On Wed, Jan 16, 2013 at 11:45 AM, Rob Sargent wrote: What environment are you in. In jdbc you can address the resultset by column name. C++, so it's quite inefficient to always use names. Chris Angelico Craft you're own metad

Re: [GENERAL] DB alias ?

On 01/23/2013 02:10 PM, Gauthier, Dave wrote: Nope. Think of it this way, a new DB is created on day 1 of every month. So there's a DB called JAN, another called FEB, etc... . The DB name used in the connect is picked up from the current date/time. But January is oevr and I don't want to c

Re: [GENERAL] seeking SQL book recommendation

On 01/23/2013 04:19 PM, John R Pierce wrote: On 1/23/2013 2:56 PM, Scott Ribe wrote: For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long tim

Re: [GENERAL] table dump function

On 03/07/2013 09:42 AM, Little, Douglas wrote: Thanks for the suggestion Adrian, Unfortunately, my process is executing from psql, so to start pg_dump from within psql is a bit challenging. We have 1 instance where we start an OS process from a function, but it's new territory for us. My fu

[GENERAL] regexp_replace failing on 9.0.4

On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine This is may statement update cms.segment_data s set text = regexp_replace(s.text, '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') from

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I&#

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/18/2013 02:40 PM, Tom Lane wrote: Rob Sargent writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no

Re: [GENERAL] State of the art re: group default privileges

On 03/20/2013 02:24 PM, Michael Orlitzky wrote: On 03/20/2013 04:12 PM, Alvaro Herrera wrote: Michael Orlitzky wrote: I'm running into this exact situation: http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com We really need to be able to hav

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/18/2013 02:40 PM, Tom Lane wrote: Rob Sargent writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no

Re: [GENERAL] regexp_replace failing on 9.0.4

On 03/21/2013 06:25 PM, Tom Lane wrote: Rob Sargent writes: For fun I decided to install 9.2 and thought I would try my luck there. Here's was I saw (apologies for the wide output). << simple update in place>> update cms.segment_data set text = regexp_replace(te

Re: [GENERAL] Referencing parts captured by round brackets in a regex in 8.4.13

On 03/22/2013 08:53 AM, Alexander Farber wrote: Hello, how to get rid of this warning on a PostgreSQL 8.4.13 prompt? # select 'axyz' ~ '(.)\1\1'; WARNING: nonstandard use of escape in a string literal LINE 1: select 'axyz' ~ '(.)\1\1'; ^ HINT: Use the esc

Re: [GENERAL] After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries

On 04/09/2013 02:29 PM, Giovanni Martina wrote: Hi, I'm trying to upgrade our database server from postgresql 32-bit 8.2.4 running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu server 12.04.02 LTS. I have dumped one of our databases from our windows server and restored it on the p

Re: [GENERAL] After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries

On 04/09/2013 02:29 PM, Giovanni Martina wrote: Hi, I'm trying to upgrade our database server from postgresql 32-bit 8.2.4 running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu server 12.04.02 LTS. I have dumped one of our databases from our windows server and restored it on the p

Re: [GENERAL] postgres and xquery

On 05/14/2013 08:48 AM, kristian kvilekval wrote: I was really hoping to see be able to store several ten's of millions XML documents in postgres, but I would also like to use Xquery to retrieve results. Back in 2010 there was some mailing list discussion about using integrating the xquery pro

Re: [GENERAL] dynamic table names

On 07/17/2013 02:39 PM, John Smith wrote: guys, have to use legacy 8.1. i have 100,000 tables in a schema that need to be queried (optimizing this by combining them into one will have to wait). so my query goes like so: > execute 'select * from ' || tabname::regclass || ' where firstname =

Re: [GENERAL] dynamic table names

On 07/17/2013 02:39 PM, John Smith wrote: guys, have to use legacy 8.1. i have 100,000 tables in a schema that need to be queried (optimizing this by combining them into one will have to wait). so my query goes like so: > execute 'select * from ' || tabname::regclass || ' where firstname =

[GENERAL] trouble with pam building 9.3beta2

Should I just bail on PAM? My system: 3.2.0-49-generic #75-Ubuntu SMP Tue Jun 18 17:39:32 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux NAME="Ubuntu" VERSION="12.04.2 LTS, Precise Pangolin" My configuration: ./configure --prefix=/usr/local/pgsql-9.3b2 --with-python --with-openssl --with-pam --with-

[GENERAL] more fun with building 9.3beta2

I'm not getting the xml2 and uuid-ossp control files delivered to the extension directory I've moved to a CentOS box (and dropped pam): cat /etc/system-release CentOS release 6.4 (Final) uname -a Linux co-app-jl-d001 2.6.32-358.11.1.el6.x86_64 #1 SMP Wed Jun 12 03:34:52 UTC 2013 x86_64 x86_64

Re: [GENERAL] more fun with building 9.3beta2

On 07/30/2013 08:25 PM, Adrian Klaver wrote: On 07/30/2013 07:15 PM, Rob Sargent wrote: I'm not getting the xml2 and uuid-ossp control files delivered to the extension directory I've moved to a CentOS box (and dropped pam): cat /etc/system-release CentOS release 6.4 (Final) uname -

[GENERAL] reindex log

Any interest out there in having the table named before the re-index begins? Now we see 'NOTICE: table "pg_catalog.pg_db_role_setting" was reindexed' I would rather see 'NOTICE: reindexing table "pg_catalog.pg_db_role_setting"' When the output stalls on a larger table, I would like to know w

Re: [GENERAL] How To Install Extension Via Script File?

On 08/08/2013 03:13 PM, Don Parris wrote: Hi all, I have a database that uses the ltree extension. I typically create a new database like so (as a normal user), using my script file: CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8'; And then su to postgres, login and install the

<    1   2   3   4   >