Re: [GENERAL] Finally upgrading to 9.6!

On 19/10/17 10:34, Don Seiler wrote: On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing > wrote: On 10/18/2017 08:17 PM, Don Seiler wrote: > I disagree with this. It isn't my company's business to test the > Postgres

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

On 15/09/17 06:15, Kenneth Marshall wrote: On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote: As I said, I'm not looking for performance or "fair probability" of planetary-wide uniqueness. My main objective is the "guarantee". Which I've tried to indicate referring to "future

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 14/09/17 16:11, Yogesh Sharma wrote: >>What you could do is copying its contents to a large disk, and then allow it to recover from the crash.  I will copy the PGDATA into large disk. After that it is require to execute some specific command or automatically recovery will start? If any

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 14/09/17 15:29, Yogesh Sharma wrote: Dear All, Thanks in advance. We are using Postgres 8.1.18 version. In Postgres log, we found below logs. –- CONTEXT:writing block 0 of relation 1664/0/1260 ERROR: could not write block 0 of relation 1664/0/1260: Bad address

Re: [GENERAL] column names and dollar sign

On 18/05/17 11:59, John R Pierce wrote: On 5/17/2017 4:51 PM, Gavin Flower wrote: Variables ending in '$' date back to at least the early days of BASIC - long before the spectre of Microsoft loomed large, let alone 'Visual Basic'! I note even INT fields have $ names there... IBM used

Re: [GENERAL] column names and dollar sign

On 18/05/17 11:32, Neil Anderson wrote: "Armand Pirvu (home)" writes: Ran into the following statement CREATE TABLE test( Date$ date, Month_Number$ int, Month$ varchar(10), Year$ int ); A strange naming convention. It has a whiff of Visual Basic

Re: [GENERAL] Top posting....

On 12/05/17 05:04, Francisco Olarte wrote: Slightly unrelated... On Wed, May 10, 2017 at 11:21 PM, Gavin Flower <gavinflo...@archidevsys.co.nz> wrote: It is normal on this list not to top post, but rather to add comments at the end (so people can see the context) - though interspersed co

Re: [GENERAL] Python versus Other Languages using PostgreSQL

Hi Paul, See comments at the end... On 10/05/17 08:00, Paul Hughes wrote: Thank you all for taking the time to answer my questions. I've been out of the programming world for a long time, so I am back to being a newbie. Even if you stay in the game, technology changes - so one has to keep

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On 25/02/17 08:39, John McKown wrote: On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston >wrote: On Friday, February 24, 2017, Tom Lane > wrote: Justin Pryzby

Re: [GENERAL] What is the purpose of PostGIS on PostgreSQL?

On 25/01/17 20:14, Johann Spies wrote: On 25 January 2017 at 08:32, Gavin Flower <gavinflo...@archidevsys.co.nz <mailto:gavinflo...@archidevsys.co.nz>> wrote: What is 'shapefile'? I don't recall ever coming across that term!https://en.wikipedia.org/wiki/Shap

Re: [GENERAL] What is the purpose of PostGIS on PostgreSQL?

On 25/01/17 14:12, Andy Colson wrote: On 01/23/2017 11:08 AM, Kased, Razy (Chemistry and Geosciences) wrote: I recently came across this question: "What is the purpose of PostGIS on PostgreSQL?" and wanted to know what this mailing list had to

Re: [GENERAL] Performance PLV8 vs PLPGSQL

On 29/12/16 09:12, Francisco Olarte wrote: On Wed, Dec 28, 2016 at 5:53 PM, Jan de Visser wrote: .but the term "impedance mismatch" is at least 25 year old; Much older, I was told it in class at least 32 years ago. as far as I know it was coined _Borrowed_ from

Re: [GENERAL] About the MONEY type

On 19/12/16 14:17, Rob Sargent wrote: On Dec 18, 2016, at 5:23 PM, Gavin Flower <gavinflo...@archidevsys.co.nz> wrote: On 18/12/16 12:25, Bruce Momjian wrote: On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote: note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the ti

Re: [GENERAL] About the MONEY type

On 18/12/16 12:25, Bruce Momjian wrote: On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote: note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone... rather, it converts it to an internal representation of GMT, and then converts it back to display time at the

Re: [GENERAL] PostgreDB stores table name is lower case

On 24/11/16 20:52, wrote: Hello ?? I'am a Postgre fan. Now, I have a problem, the table name is stored in lower case , but i want to change it into upper case. Can i have a simple method? Such as modify a parameter. Thank you! Why? I can't see any practical

Re: [GENERAL] initdb createuser commands

On 30/10/16 11:25, John R Pierce wrote: On 10/29/2016 3:02 PM, Samuel Williams wrote: FYI,https://wiki.archlinux.org/index.php/PostgreSQL mentions initdb, createuser, createdb and several others. I think my suggestion is still relevant and something that would improve the system for new users

Re: [GENERAL] Chante domain type - Postgres 9.2

On 26/09/16 17:58, Patrick B wrote: Hi guys, I've got this domain: CREATE DOMAIN public.a_city AS character varying(80) COLLATE pg_catalog."default"; And I need to increase the type from character varying(80) to character varying(255). How can I do that? didn't find info

Re: [GENERAL] IDE for function/stored proc development.

On 08/09/16 19:42, Martijn Tonies (Upscene Productions) wrote: Hi, For what it's worth, Database Workbench with PostgreSQL support was released Yesterday. http://www.upscene.com/database_workbench/whatsnew With regards, Martijn Tonies Upscene Productions [...] Hmm... exe's don't work

Re: [GENERAL] C++ port of Postgres

On 16/08/16 18:24, dandl wrote: Just wondering what the end goal is for this project... Is it to just maintain an up to date Postgres fork that will compile with a C++ compiler? Is it to get a conversation going for a direction for Postgres itself to move? The former I don't see gaining

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28/07/16 17:52, Jason Dusek wrote: With regards to write amplification, it makes me think about about OIDs. Used to be, every row had an OID and that OID persisted across row versions. https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS Would

Re: [GENERAL] OT hardware recommend

On 18/06/16 14:23, Scott Marlowe wrote: On Fri, Jun 17, 2016 at 2:36 PM, Andy Colson > wrote: Hi all. I have access to quite a few laptop HD's (10 to 15 of them at least), and thought that might make a neat test box that might

Re: [GENERAL] OT hardware recommend

On 18/06/16 08:36, Andy Colson wrote: Hi all. I have access to quite a few laptop HD's (10 to 15 of them at least), and thought that might make a neat test box that might have some good IO speed. Needs to be cheap though, so linux with software raid, rack mount preferred but not required.

Re: [GENERAL] maximum "target list" (maximum columns)

On 16/06/16 16:39, Shaun Cutts wrote: The opinion of database developers is that using more than 1664 columns is bad design, so that the current maximum number of columns is not onerous. When I simply built applications on top of databases, I was of the same opinion. However, now my job is to

Re: [GENERAL] Members in the Middle East?

On 25/05/16 02:18, Umair Shahid wrote: Hi, Do we have folks in this group based out of the Middle East, preferably in UAE? We currently don't have a user group in the GCC region and I would like to help start one up. Thanks! - Umair All the best! What does 'GCC' stand for? My first

Re: [GENERAL] Fast way to delete big table?

On 16/05/16 17:09, Haiming Zhang wrote: Hi All, I have a big table (200G with 728 million records), the table slows down lots of things. It's time to clean the data up. The requirement is when I delete I must summarise few columns to a new table for backup purpose. So that mean I need to go

Re: [GENERAL] disable ipv6?

On 20/04/16 13:35, Jonathan Vanasco wrote: I'm running postgresql on ubuntu. the 9.4 branch from postgresql.org I think the only way to disable ipv6 is to edit postgresql.conf and explicitly state localhost in ipv4 as follows - listen_addresses = 'localhost' + listen_addresses =

Re: [GENERAL] $foo $bar is BAD

On 16/04/16 14:00, Peter Devoy wrote: Although people commonly use $foo $bar in examples, it is actually a misuse of a VERY rude acronym. The next time you need to make an example, please try being a little more original (or meaningful) with your variable names. In light of recent CoC

Re: [GENERAL] Let's Do the CoC Right

On 24/01/16 13:48, Regina Obe wrote: This is mostly in response to David's recent comments. I should say David, you are really beginning to make me feel unsafe. By unsafe I mean my mental safety of being able to speak truthfully without fear of being kicked out of a community I love. I do not

Re: [GENERAL] Code of Conduct: Is it time? (WIP CoC)

On 12/01/16 11:21, Karsten Hilbert wrote: On Mon, Jan 11, 2016 at 02:00:22PM -0800, Joshua D. Drake wrote: 3. A safe, respectful, productive and collaborative environment is free comments related to gender, sexual orientation, disability, physical appearance, body size or race. ... for of

Re: Things to notice (was Re: [GENERAL] Code of Conduct: Is it time?, broken thread I hope)

On 11/01/16 15:00, Andrew Sullivan wrote: Someone (never mind who, this isn't intended to be a blame-game message) wrote: Am I, as a mere male […] :-) It was me. The phrase "Mere Male" was title of a column I read in NZ Women's Weekly that my mother bought when I was a teenager. >>> An

Re: [GENERAL] Code of Conduct: Is it time?

On 11/01/16 19:13, Oleg Bartunov wrote: [...] Some people don't understand all these issues with she/he, for example, we in Russia are not really concern about this. [...] I started using 'Gender Appropriate' language long before this PC nonsense started up. Back in those days the word

Re: [GENERAL] Code of Conduct: Is it time?

On 10/01/16 22:55, John R Pierce wrote: On 1/9/2016 11:57 PM, Gavin Flower wrote: I was at the 2015 Australian Linux Conference (held in Auckland, NZ), when Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus remained calm and polite throughout, yet most people would have been

Re: [GENERAL] Code of Conduct: Is it time?

On 11/01/16 07:44, Regina Obe wrote: [...] This may come as a big shock to many of you, but as a contributor I don't care if you are racist, sexist, transphobic or whatever as long as you 1) Are helpful when I ask a question 2) Stick to the topic 3) Don't get into petty etiquettes like "Please

Re: [GENERAL] Code of Conduct: Is it time?

On 10/01/16 21:31, Regina Obe wrote: I was at the 2015 Australian Linux Conference (held in Auckland, NZ), when Sarah Sharp harangued Linus Torvalds for over 20 minutes. Linus remained calm and polite throughout, yet most people would have been obviously annoyed within the first 5 minutes. As

Re: [GENERAL] Code of Conduct: Is it time?

On 10/01/16 20:37, Regina Obe wrote: Josh informed me you guys are thinking about a CoC. Let me start off by saying that I don't think you need one and in fact having one may be dangerous. I fear for your safety. I think Roxanne mentioned some good points in an earlier thread that you should

Re: [GENERAL] Any way to selectively color query output in psql?

On 08/01/16 10:25, David G. Johnston wrote: Basically I want to write this: psql -c "SELECT E'\e[1;33m Some Text Here \e[0m';" And have just the text "Some Text Here" colored while everything else is default white. The \e constructs are the ANSI color escapes which work when using echo -e

Re: [GENERAL] RAM of Postgres Server

On 07/01/16 18:39, Sachin Srivastava wrote: Dear Team, Please suggest, how much RAM and core should be define for New Postgres database server, if we will use Postgres 9.3 and above. If suppose my postgres database size will be near about 300 to 500 GB for future. There is any document

Re: [GENERAL] Recurring and non recurring events.

Pleas don't top post - see comment at the bottom of this email. On 27/12/15 01:03, Kevin Waterson wrote: Thanks, as I am new to postgres, I was unaware of this function. To go with this, I guess I will need a table with which to store intervals, start and end dates? eg CREATE table events(

Re: [GENERAL] Recursive Arrays 101

On 06/11/15 04:33, Rob Sargent wrote: On 11/05/2015 04:56 AM, Achilleas Mantzios wrote: On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies,

Re: [GENERAL] Recursive Arrays 101

I would suggest that you use int or bigint for primary keys, and have mapping tables to convert the scientific term to the surrogate primary key. If the mapping table has additional attributes, like date of change & reason, then you can also print a history of changes. Then the relationships

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 09:29, David Blomstrom wrote: [...] Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save it as a CSV file and import it into a database table. It would be very hard to break with that tradition, because I don't know of any other

Re: [GENERAL] Recursive Arrays 101

Hi David, Please don't top post! On 27/10/15 09:42, David Blomstrom wrote: I've created my first table in postgreSQL. I'd like to ask 1) if you see any errors, 2) do you have any suggestions for improving it, and 3) can you give me the code I need to paste into the shell (or whatever you

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 10:26, David Blomstrom wrote: Here's what it looks like now: CREATE TABLE public.gz_life_mammals ( id integer NOT NULL, taxon text NOT NULL, parent text NOT NULL, slug text, namecommon text, plural text, extinct smallint NOT NULL, rank smallint NOT NULL, key

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 10:17, David Blomstrom wrote: What does "top post" mean? And what do you mean by "embedded spaces"? Are you referring to the underscores in the TABLE name? On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower <gavinflo...@archidevsys.co.nz <mailto:gavinflo...@

Re: [GENERAL] Importing CSV File

On 27/10/15 10:45, David Blomstrom wrote: I tried to import a CSV file into a PostgreSQL table using pgAdmin III. I got an error message: "extra data after last column." All my spreadsheets have an "end of data" column that has /r/n in each cell. When I import a CSV file into a MySQL table,

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:18, David Blomstrom wrote: I pasted this into the shell... CREATE ROLE david LOGIN CREATEDB; CREATE DATABASE GeoZoo2 OWNER david; and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there are no new databases. And when I paste this in, it

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 12:27, David Blomstrom wrote: LOL - This is precisely why I prefer GUI's. ;) I have two PostgreSQL icons on my taskbar - a blue elephant (pgAdmin III) and a little monitor (PSQL). When I click on PSQL, it always opens at least two windows or instances. When I type things in and

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 13:29, John R Pierce wrote: On 10/26/2015 5:20 PM, David G. Johnston wrote: What exactly are you showing us here? he's demonstrating a lack of reading comprehension. I'm done. thread on ignore. I think its proof that Apple products rot your brain! -- Sent via

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:18, David Blomstrom wrote: [...] CREATE DATABASE GeoZoo2 [...] Would strongly advise NOT using capital letters in names of databases in pg! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:57, David Blomstrom wrote: When I type in /l, it just says "database Postgres," even though I can see TWO databases in pgAdmin III. When I type in /dt, it says Username [postgres]. On Mon, Oct 26, 2015 at 3:40 PM, Gavin Flower <gavinflo...@archidevsys.co.nz <

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 11:18, David Blomstrom wrote: I pasted this into the shell... CREATE ROLE david LOGIN CREATEDB; CREATE DATABASE GeoZoo2 OWNER david; and I did it again, replacing LOGIN with my password, but when I refresh pgAdmin III, there are no new databases. And when I paste this in, it

Re: [GENERAL] Recursive Arrays 101

On 27/10/15 12:15, David Blomstrom wrote: Server [localhost]: \c geozoo2 david Database [postgres]: *Shell2* Username [postgres]: \c geozoo2 david psql: warning: extra command-line argument "david" ignored psql: warning: extra command-line argument "l" ignored psql: invalid port number:

Re: [GENERAL] ID column naming convention

On 18/10/15 00:13, Karsten Hilbert wrote: On Fri, Oct 16, 2015 at 04:59:12PM -0500, Jim Nasby wrote: BTW, I found Karsten's idea of using 'pk' for the surrogate key, and fk_table_name interesting. It helps avoid ambiguity from externally generated ID values. That's the point :-) Here's a

Re: [GENERAL] ID column naming convention

On 16/10/15 13:09, Jim Nasby wrote: On 10/13/15 2:34 PM, Gavin Flower wrote: My practice is to name the PRIMARY KEY as id, and foreign keys with the original table name plus the sufiix_id. By leaving the table name off the primary key name, and just using id, makes it more obvious

Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

On 12/10/15 22:52, Steve Petrie, P.Eng. wrote: [...] BTW -- this PostgreSQL newbie (bye bye mysql) is getting a nice warm fuzzy feeling, about PostgreSQL and its amazingly helpful community :) [...] I can attempt to remedy your 'nice warm fuzzy feeling'! :-) More seriously: (1) why did you

Re: [GENERAL] ID column naming convention

On 14/10/15 06:36, droberts wrote: Hi, is there a problem calling ID's different when used as a FK vs table ID? For example mydimtable () ID name description myfacttable () my_dim_id # FK to ID above total_sales I 'think' if I don't enforce foreign key constraints, then this

Re: [GENERAL] table dependencies

On 07/09/15 19:44, Raymond O'Donnell wrote: On 06/09/2015 22:59, FarjadFarid(ChkNet) wrote: No worries. I found a way. Would you share it, for the archives? Ray. I think I can do it relatively simply, in a reasonable general fashion. if it is of real interest let me know, and I'll see

Re: [GENERAL] Public facing PostgreSQL hosting ?

On 31/08/15 19:03, essam Ganadily wrote: hi i do develop on PostgreSQL from home and from work. i need public facing PostgreSQL , something i can use Pgadmin from anywhere. performance and scalability is not important because i will be running like few operations per day. any idea where can

Re: [GENERAL] PostgreSQL Developer Best Practices

On 27/08/15 00:03, Vincent de Phily wrote: On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: Actually I would suggest standardising on singular names, not JUST because that this the standard I prefer! :-) But (also) because: 1. Singular words tend to be shorter 2. plurals

Re: [GENERAL] PostgreSQL Developer Best Practices

On 27/08/15 06:59, Raymond O'Donnell wrote: On 26/08/2015 19:54, Gavin Flower wrote: On 27/08/15 00:03, Vincent de Phily wrote: On Wednesday 26 August 2015 10:27:04 Gavin Flower wrote: Actually I would suggest standardising on singular names, not JUST because that this the standard I prefer

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 12:17, Melvin Davidson wrote: [...] So for the sake of argument, a natural key is something that in itself is unique and the possibility of a duplicate does not exist. Before ANYONE continues to insist that a serial id column is good, consider the case where the number of tuples

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 19:04, Karsten Hilbert wrote: On Tue, Aug 25, 2015 at 02:02:17PM +1200, Gavin Flower wrote: On 25/08/15 01:15, Ray Cote wrote: On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert karsten.hilb...@gmx.net mailto:karsten.hilb...@gmx.net wrote: [...] 9. Do NOT arbitrarily assign

Re: [GENERAL] PostgreSQL Developer Best Practices

NB the attribution colours seems to be mixed up a bit here, but this all dialogue between me Adrian. On 26/08/15 09:48, Adrian Klaver wrote: On 08/25/2015 02:23 PM, Gavin Flower wrote: On 26/08/15 08:56, Adrian Klaver wrote: [...] have all gone to the same seminar on how to be Walmart

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 04:33, Marc Munro wrote: On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote: I've been searching for a PostgreSQL Developer Best Practices with not much luck, so I've started my own. At the risk of stirring up a storm of controversy, I would appreciate additional suggestions

Re: [GENERAL] Why does splitting $PGDATA and xlog yield a performance benefit?

On 26/08/15 05:54, David Kerr wrote: On Tue, Aug 25, 2015 at 10:16:37AM PDT, Andomar wrote: However, I know from experience that's not entirely true, (although it's not always easy to measure all aspects of your I/O bandwith). Am I missing something? Two things I can think of: Transaction

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 03:40, Melvin Davidson wrote: [...] IOW: If we have an account table, then the account_id or account_no would be the primary key. There is no need to have a separate serial id as the primary key. [...] Account numbers are externally generated, and may potentially change.

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 11:34, Adrian Klaver wrote: [...] Agreed, but it happens. When Lowes took over a local hardware chain(Eagles) here in Washington state they moved very quickly on changing the account numbers. The company I worked for who supplied Eagles and then Lowes sat on a check for $22,000 that

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 08:56, Adrian Klaver wrote: On 08/25/2015 08:40 AM, Melvin Davidson wrote: Adrian, Stop being so technical. When we/I speak of natural keys, we are talking about the column that would NATURALly lend itself as the primary key. Pretty sure this is a technical list:) Don't let

Re: [GENERAL] PostgreSQL Developer Best Practices

On 26/08/15 02:17, Adrian Klaver wrote: [...] 2) One of the older unique natural keys (genus, species) is not so unique. I am a fisheries biologist by training and in my time the 'unique' identifier for various fishes has changed. Now that ichthyologists have discovered DNA testing, it can

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 02:58, John Turner wrote: [...] Conversely, if synthetic keys are chosen as Primary, they must be accompanied by a legitimate Unique natural key. Agreed, but only where appropriate. Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 01:15, Ray Cote wrote: On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert karsten.hilb...@gmx.net mailto:karsten.hilb...@gmx.net wrote: [...] 9. Do NOT arbitrarily assign an id column to a table as a primary key when other columns are perfectly suited as a unique

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 04:26, Joshua D. Drake wrote: On 08/24/2015 08:56 AM, Melvin Davidson wrote: The serial key is the default primary key amongst every single web development environment in existence. Methinks thou doest take too much for granted. Yes, serial has it's purpose, but I sincerely doubt

Re: [GENERAL] PostgreSQL Developer Best Practices

On 25/08/15 14:45, David G. Johnston wrote: On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower gavinflo...@archidevsys.co.nz mailto:gavinflo...@archidevsys.co.nzwrote: Also the best practice is to make the primary key name 'id' as you do know the table it is in, so prepending the table

Re: [GENERAL] Re: INSERT a real number in a column based on other columns OLD INSERTs

On 25/06/15 16:06, litu16 wrote: SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND (t.time_type = 'Start' OR time_type = 'Lap') ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix; Please format your SQL, as it makes it easier to read! But, you don't have to follow my

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 03/06/15 08:40, Andreas Ulbrich wrote: On 02.06.2015 22:12, Melvin Davidson wrote: Your problem is in your design. If you do it like this: CREATE TABLE A ( p_col serial PRIMARY KEY, acol integer ); CREATE TABLE B() INHERITS (A); INSERT INTO A(acol) VALUES (1); INSERT INTO B(acol) VALUES

Re: [GENERAL] Testing Views

On 20/05/15 19:47, Andy Chambers wrote: Hello All, I have a problem for which a view seems like a nice solution. Basically we want to see all records in some table that are older than 5 days and haven't yet gone through further processing. This particular view is probably simple enough that

Re: [GENERAL] How to clear buffer

On 13/05/15 20:13, Ravi Krishna wrote: I am writing bench mark scripts and as part of it would like to clear the cache programmatically. This is to ensure that when we run select queries the data is not read read from the cache. Does PG provide any easy way to do it other than the obvious way to

Re: [GENERAL] documenting tables version control

See comments at bottom, On 06/05/15 20:47, Tim Clarke wrote: We keep the DDL statements for any tables in our subversion code repository just like any other programming code. Takes only a moment then all history or changes is available and comparable. svnserve on our linux server, svn

Re: [GENERAL] Postgresql Development Options

On 06/04/15 08:46, Ray Madigan wrote: The application will be very small, its main function is as a data acquision tool that reads data from an c api and does minimal processing and feeds it off to a postgresql database for offline processing. I haven't written the PC side application yet,

Re: [GENERAL] how would you speed up this long query?

On 28/03/15 10:10, zach cruise wrote: select sub_query_1.pid, sub_query_1.tit, sub_query_1.num, sub_query_3.cid, sub_query_3.id, sub_query_3.c, sub_query_3.s, sub_query_3.z, sub_query_3.cy, sub_query_3.cd,

Re: [GENERAL] PostgreSQL-related legal question

On 12/03/15 01:55, Bill Moran wrote: On Wed, 11 Mar 2015 13:50:02 +0100 Dorian Hoxha dorian.ho...@gmail.com wrote: I don't see how it could have negative impact on the postgresql project? It's not like your job will be to find vulnerabilities and not disclose them ? I don't think I should

Re: [GENERAL] SQL solution for my JDBC timezone issue

On 25/02/15 04:29, Adrian Klaver wrote: On 02/24/2015 06:25 AM, George Woodring wrote: -- In your original post you mentioned that access to the databases is through a Web server. -- Is there just one Web server with one time zone? We have 2 web servers that are clustered together. They

Re: [GENERAL] Postgres architecture for multiple instances

On 23/02/15 15:53, Samuel Smith wrote: On 02/21/2015 05:25 PM, David Steele wrote: On 2/21/15 6:08 PM, Adrian Klaver wrote: Currently the built in replication solutions work at the cluster level, not at the database level. There are third party tools, Slony and Bucardo come to mind, that can

Re: [GENERAL] Postgres architecture for multiple instances

On 23/02/15 17:21, Samuel Smith wrote: On 02/22/2015 01:53 PM, Scott Marlowe wrote: I'd run a debian based distro (Ubuntu or Debian work well) and use the pg_* commands to create the clusters the same way. Gives you the maximum separation for clients. pg_createcluster Usage:

Re: [GENERAL] Hardware requirements for a PostGIS server

On 12/02/15 12:38, Mathieu Basille wrote: Thanks to everyone who contributed to this thread, either on the PostGIS [1] or the PostgreSQL [2] mailing lists. I will try to summarize everything in this message, which I will actually post on both lists to give an update to everyone. I hope it can

Re: [GENERAL] Hardware requirements for a PostGIS server

On 12/02/15 12:38, Mathieu Basille wrote: [...] [1] Start of the thread here: http://lists.osgeo.org/pipermail/postgis-users/2015-February/040120.html [...] http://lists.osgeo.org/pipermail/postgis-users/2015-February/040134.html [...] * About usage being mostly read: this will be true for

Re: [GENERAL] Hardware requirements for a PostGIS server

On 11/02/15 13:52, Mathieu Basille wrote: Dear PostgreSQL users, I am posting here a question that I initially asked on the PostGIS list [1], where I was advised to try here too (I will keep both lists updated about the developments on this issue). I am currently planning to set up a

Re: [GENERAL] Stability of JSON textual representation

On 03/02/15 00:06, David Evans wrote: I've noticed that when representing lists as JSON, Postgres 9.4 sometimes outputs spaces after commas, and other times does not. # SELECT array_to_json(array[1, 2, 3]), json_build_array(1, 2, 3); array_to_json | json_build_array

Re: [GENERAL] Error: Out of memory while reading tuples. in pushing table from SAS to PostgreSQL on Mac

On 15/12/14 04:44, Andy Colson wrote: On 12/13/2014 10:03 PM, wetter wetterana wrote: Hi, I'm passing rows from SAS to PostgreSQL (I assign a libname and use a PROC APPEND). This works fine with smaller tables (~below 1 million rows). However, as tables get larger I receive the following

Re: [GENERAL] Stored procedure workflow question

On 11/12/14 13:53, 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 above

Re: [GENERAL] Avoiding deadlocks when performing bulk update and delete operations

On 24/11/14 16:51, Sanjaya Vithanagama wrote: Hi All, We have a single table which does not have any foreign key references. id_A (bigint) id_B (bigint) val_1 (varchar) val_2 (varchar) The primary key of the table is a composite of id_A and id_B. Reads and writes of this table are highly

Re: [GENERAL] table versioning approach (not auditing)

On 08/10/14 13:29, Jim Nasby wrote: On 10/6/14, 6:10 PM, Gavin Flower wrote: Even if timestamps are used extensively, you'd have to be careful joining on them. You may have information valid at T1 and changing at T3, but the transaction has T2, where T1 T2 T3 - the appropriate set of data

Re: [GENERAL] table versioning approach (not auditing)

On 07/10/14 10:47, Jim Nasby wrote: On 10/2/14, 9:27 AM, Adam Brusselback wrote: i've also tried to implement a database versioning using JSON to log changes in tables. Here it is:

Re: [GENERAL] table versioning approach (not auditing)

On 29/09/14 15:00, Abelard Hoffman wrote: Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples:

Re: [GENERAL] Postgres as key/value store

On 28/09/14 12:48, snacktime wrote: I'm looking for some feedback on the design I'm using for a basic key/value storage using postgres. Just some quick background. This design is for large scale games that can get up to 10K writes per second or more. The storage will be behind a

Re: [GENERAL] advice sought - general approaches to optimizing queries around event streams

On 27/09/14 09:02, Jonathan Vanasco wrote: I have a growing database with millions of rows that track resources against an event stream. i have a few handfuls of queries that interact with this stream in a variety of ways, and I have managed to drop things down from 70s to 3.5s on full scans

Re: [GENERAL] password in recovery.conf [SOLVED]

On 27/09/14 11:56, John R Pierce wrote: On 9/26/2014 4:40 PM, John R Pierce wrote: I'd consider using `mkpasswd -l 15 -s 0` just to avoid any such problems. 15 random alphanumerics is already plenty complex, 62^15th possible combinations, without needing to mix in special characters. $

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

On 19/09/14 19:32, Marius Grama wrote: Hello, i am using Postgres 9.2 and I'd like to perform the following ALTER statement on a database table with about 30M entries : ALTER TABLE images ALTER COLUMN mask_descriptors TYPE VARCHAR(1024); The mask_descriptors field is currently having the

Re: [GENERAL] About limit on cube dimensions

On 01/09/14 09:05, Shida Sato wrote: Hi Why is there limit on the number of cube dimensions? It is bit strange because ARRAY has no such limit which is similar to cube. Does it relate to Rtree? Can I use 1 dimensional cube without R-tree? --- sato Have you calculated how much disc space

Re: [GENERAL] Random-looking primary keys in the range 100000..999999

Please don't top post! See below for my comments. On 09/07/14 07:04, Kynn Jones wrote: Thanks to Gavin and Martijn for their suggestions. They're both simple good-ol' LCGs, and both avoid the need to check for collisions. I ultimately went with a multiplicative LCG, like Martijn's, mostly

  1   2   3   >