Re: [GENERAL] delete a file everytime pg server starts/crashes

That seems like an odd requirement and I don't think PostgreSQL can do it itself, because if postgresql should crash properly then the process that should write/remove that file would also crash The simplest way would be to write a cronjob that connects to the database and does a simple query

Re: [GENERAL] Multicolumn Index on OR conditions

On 2017-09-19 10:08, Job wrote: Hi, within a query with two or more conditions with "OR", example: "where a = 2 or b < 3" could be useful to speed up the query a multi-column index (a,b) even though the two conditions are in "OR" and not in "AND"? Thank you! F Having any kind of index

Re: [GENERAL] looking for a globally unique row ID

On 2017-09-14 15:06, Rafal Pietrak wrote: W dniu 14.09.2017 o 10:57, George Neuner pisze: On Thu, 14 Sep 2017 09:45:59 +0200, Rafal Pietrak wrote: Hello everybody, Can anybody help me find a way to implement an ID which: 1. guarantees being unique across multiple tables.

Re: [GENERAL] optimize pg_tables query ( text vs varchar ) ...why ?

On 2017-08-16 14:41, gmb wrote: Hi For DDL purposes we make significant use of pg_catalog tables/views. Were investigating performance issues in a typical function: CREATE FUNCTION tableexists( s TEXT , t TEXT ) returns boolean as $$ SELECT count(tablename) = 1 FROM pg_tables WHERE

Re: [GENERAL] Question about loading up a table

On 2017-07-31 11:02, Alex Samad wrote: Hi I am using pg_dump | psql to transfer data from my old 9.2 psql into a 9.6 psql. The new DB server is setup as master replicating to a hot standby server. What I have noticed is that the rows don't get replicated over until the copy from stdin is

Re: [GENERAL] Developer GUI tools for PostgreSQL

On 2017-07-28 06:31, Tim Uckun wrote: I think it's funny that after all these years pgadmin3 is still the most comprehensive GUI for postgres. Have you looked at EMS SQL-Manager, I don't remember PgAdmin having any where near hte features that it has :-P Even though it's prone to crashing

Re: [GENERAL] Developer GUI tools for PostgreSQL

On 2017-07-27 00:41, Tiffany Thang wrote: Hi, I'm new to PostgreSQL. I'm looking for a developer tool that works similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me to view and make DDL changes to database objects and create data models. It would be a plus if I can use the

Re: [GENERAL] Row based permissions: at DB or at Application level?

On 2017-07-27 10:27, Thomas Güttler wrote: Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) When I look at an example p

Re: [GENERAL] Row based permissions: at DB or at Application level?

On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) Regards, Thomas Güttler A quick brainstorm: You could, probably... but you'd have to create a separate database user for

Re: [GENERAL] Is it possible to define a constraint based on the values in other rows in the current table?

On 2017-07-19 13:37, Glen Huang wrote: Hi, I'd like to enforce that in a transaction, after a couple inserts & updates, a particular column has continuous values like 1, 2, 3, and never any gaps. Is it possible to do? I gave a concrete example here: https://stackoverflow.com/questions/45187113

Re: [GENERAL] dump to pg

On 2017-05-31 16:43, Nicolas Paris wrote: Hi, I have dumps from oracle and microsoft sql server (no more details). Is it possible to load them "directly" into postgres (without oracle/mssql license)? dump -> csv -> postgtres or something ? Thanks a lot A very, *very* short trip to google

Re: [GENERAL] Error that shouldn't happen?

On 2017-05-18 21:48, Rob Brucks wrote: Hello Everyone, I am unable to figure out how the trigger was able to successfully create the table, but then fail creating the index. I would have expected one thread to "win" and create both the table and index, but other threads would fail when

Re: [GENERAL] Python versus Other Languages using PostgreSQL

On 2017-05-09 11:26, Francisco Olarte wrote: Paul: On Tue, May 9, 2017 at 2:45 AM, Paul Hughes wrote: My question still remains though - why is it that all the largest web platforms that have used PostgreSQL *specifically* choose Python as their back-end language? Do

Re: [GENERAL] [OT] Help: stories of database security and privacy

On 2017-04-26 11:47, Lifepillar wrote: On 12/04/2017 10:57, vinny wrote: On 2017-04-12 09:09, Lifepillar wrote: So, I am here to ask if you have interesting/(in)famous stories to share on database security/privacy "gone wrong" or "done right"(tm), possibly with technica

Re: [GENERAL] Not sure this should be asked here but...

On 2017-04-23 12:31, Ron Ben wrote: A simple open source forum system can be enough simetng like php-bb example: warez-bb.org the installation of such system is like 1 hour of work. In my point of view something like stack overflow is the best but i'm not sure if it's open source. Setting

Re: [GENERAL] Large data and slow queries

On 2017-04-19 13:25, Martijn Tonies (Upscene Productions) wrote: Samuel, others, Perhaps I'm missing something, but I'd be interested in the reasoning behind this. For column 'what', it seems you have no index on all values, only indices with specific values for 'what'. How does this speed up

Re: [GENERAL] Large data and slow queries

On 2017-04-19 09:48, John R Pierce wrote: On 4/19/2017 12:31 AM, vinny wrote: Given the number of records, my first thought was either partitioning or partial-indexes. The fewer rows are in the index, the quicker it will be to check, and it's not a lot of work to create separate indexes

Re: [GENERAL] Large data and slow queries

On 2017-04-19 07:04, Samuel Williams wrote: Thanks John. Yes, you are absolutely right, you want the index to be bottom heavy so you can cull as much as possible at the top. I'm familiar with that, once implementing a brute-force sudoku solver, it has the same principle. I've been working on

Re: [GENERAL] [OT] Help: stories of database security and privacy

On 2017-04-12 09:09, Lifepillar wrote: Hi folks, in a few weeks I will start a short course on the basics of database security for a group of high-school students with a background in elementary relational theory and SQL. I plan to discuss the usage of grant/revoke, RBAC, DAC, and inference in

Re: [GENERAL] browser interface to forums please?

On 2017-04-05 15:11, Vincent Veyron wrote: On Tue, 04 Apr 2017 12:01:24 +0200 vinny <vi...@xs4all.nl> wrote: Every time I tell someone about the mailinglists I then have to explain how they can subscribe, how to create folders, filters etc. And more often than not they just say

Re: [GENERAL] browser interface to forums please?

On 2017-04-04 15:04, Stephen Frost wrote: Greetings, * vinny (vi...@xs4all.nl) wrote: And yes, I can probably setup my email to do something like that, the point is that I shouldn't have to. I'm all for improving things and adding automation where it'll help, but the infrastructure

Re: [GENERAL] browser interface to forums please?

On 2017-03-27 23:23, Steve Litt wrote: On Mon, 27 Mar 2017 11:31:02 +0900 Michael Paquier wrote: If you have subscribed to more mailing lists than -general, having one subfolder per list can also help a lot, grouping as well some of those having a low activity, for

Re: [GENERAL] "Reverse" inheritance?

ble. I'm not sure you can do it with windowing actually, given that you'd have to sort every record based on a match with the previous record. But I've never tried it because CTE's make it so easy :-) On Tue, Apr 4, 2017 at 6:43 PM, vinny <vi...@xs4all.nl> wrote: I agree with the bark

Re: [GENERAL] "Reverse" inheritance?

I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out. Also think about how you are going to do basic queries like listing all known unix

Re: [GENERAL] count case when - PG 9.2

On 2017-03-09 05:27, Patrick B wrote: Hi guys. How can I count using 'CASE WHEN'? Example: SELECT CASE WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day')) THEN 'trial' WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day')) THEN 'paying' END as

Re: [GENERAL] import CSV file to a table

On 2017-03-08 10:13, Günce Kaya wrote: Hi all, I want to import content of CSV file to a table via bash script without creating temporary table and I also want to skip some columns in CSV file (for instance, CSV file has 12 column and main table has only 2 column, If possible I would use only 2

Re: [GENERAL] Postgres, apps, special characters and UTF-8 encoding

On 2017-03-08 00:20, Ken Tanzer wrote: Hi. I've got a recurring problem with character encoding for a Postgres-based web PHP app, and am hoping someone can clue me in or at least point me in the right direction. I'll confess upfront my understanding of encoding issues is extremely limited.

Re: [GENERAL] Trim performance on 9.5

On 2016-11-18 15:06, William Ivanski wrote: Hi, I recently did major improvements on perfomance on our routines by simply removing the call for trim functions on specific bottlenecks. Please see images attached for a simple example. I'm using PostgreSQL version 9.5.5-1.pgdg80+1 on Debian 8.6.

Re: [GENERAL] Strange? BETWEEN behaviour.

On 2016-10-20 14:27, Bjørn T Johansen wrote: On Thu, 20 Oct 2016 14:04:51 +0200 vinny <vi...@xs4all.nl> wrote: On 2016-10-20 13:51, Bjørn T Johansen wrote: > I have the following SQL: > > SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 > 00:00:00','DD.

Re: [GENERAL] Strange? BETWEEN behaviour.

On 2016-10-20 13:51, Bjørn T Johansen wrote: I have the following SQL: SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016 23:59:59','DD.MM. HH24:MI:SS') date is of type timestamp. I was expecting to get all the

Re: [GENERAL] Performance question: Commit or rollback?

On Sun, 2011-12-18 at 14:05 +1100, Chris Angelico wrote: On Sun, Dec 18, 2011 at 2:02 PM, Chris Travers chris.trav...@gmail.com wrote: I do not believe there are performance penalties for either. All commit or rollback does is determine visibility of changes made. Thanks. (And thanks

Re: [GENERAL] Performance question: Commit or rollback?

On Sat, 2011-12-24 at 23:49 +1100, Chris Angelico wrote: On Sat, Dec 24, 2011 at 11:46 PM, vinny vi...@xs4all.nl wrote: The actual rollback won't hurt as long as you have not made any modificatons to any records. But opening the transaction could have side effects for other processes

Re: R: [GENERAL] Field's position in Table

On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote: On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: I can't really think of any real reason to put the field at a particular position, applications don't reallty care about the order of fields. Because it's very convenient for ad-hoc

Re: R: [GENERAL] Field's position in Table

On Thu, 20 Aug 2009 09:21:25 + (GMT), Scara Maccai m_li...@yahoo.it wrote: When adding a new field in the existing table, i want to add the filed in a particular position. I'm afraid the only way would be re-writing the whole table (pseudo sql): BEGIN; create table newtable as select

Re: [GENERAL] backups

On Wed, 30 Jun 2004 22:32:26 -0500, [EMAIL PROTECTED] (Bruno Wolff III) wrote: On Wed, Jun 30, 2004 at 18:23:08 -0500, [EMAIL PROTECTED] wrote: What do other sites with mondo databases do? There have been comments from people using storage systems that they can freeze the storage system and