Re: [GENERAL] Postgres and data warehouses

I strongly suspect many would like further details on your implementation. A user conference session well recorded perhaps? > On Mar 8, 2015, at 4:57 PM, John R Pierce wrote: > >> On 3/8/2015 7:40 AM, Nigel Gardiner wrote: >> I'm looking at making a data warehouse to address our rapidly spirall

Re: [GENERAL] Postgres and data warehouses

I expected the latter but … some proprietors like to brag :) Standing down. > On Mar 8, 2015, at 9:56 PM, John R Pierce wrote: > > On 3/8/2015 8:24 PM, Rob Sargent wrote: >> I strongly suspect many would like further details on your implementation. A >> user conference

Re: [GENERAL] Finding values of bind variables

> On Apr 8, 2015, at 3:43 PM, Vasudevan, Ramya > wrote: > > Thank You John. > > > the state_change to idle-in-transaction took place about 0.0001 seconds > > later (so the insert took all of a millisecond). > Yes, the DML “finished” but stayed uncommitted. > > > What date/time was it when y

Re: R: [GENERAL] Index on integer or on string field

Are you saying your indexed field has only 50 distinct values? Seems a horrible candidate for an index. Might be good to partition on those fifty values but ten million records probably doesn't warrant that. Sent from my iPhone > On May 15, 2015, at 9:34 AM, Job wrote: > > Hello Arthur! > >

Re: [GENERAL] Error prone compilation of stored procedure

And are your colleagues offering to pay for an Oracle license? > On Jul 6, 2015, at 5:52 AM, pinker wrote: > > Pavel Stehule wrote >> PLpgSQL doesn't check a identifiers inside embedded SQL before execution. >> In this case j_var can be theoretically some SQL identifiers - the >> possibility or

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you a

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 12:25 PM, Adrian Klaver wrote: On 07/23/2015 11:15 AM, Rob Sargent wrote: On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a s

Re: [GENERAL] Delete rule does not prevent truncate

On 07/23/2015 06:27 PM, Adrian Klaver wrote: On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clau

Re: [GENERAL] PostgreSQL Developer Best Practices

> On Aug 24, 2015, at 6:53 PM, Melvin Davidson wrote: > > You are right, he was probably talking about FK's. I was just so frustrated > about people insisting that using "ID" as the primary key in every table is a > "good" idea, > I didn't bother to reply previously. I stand firm on my belief

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 09: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. No one ever said a number is not natural. just that there is no need to duplicate uniqueness w

Re: [GENERAL] PostgreSQL Developer Best Practices

On 08/25/2015 04:27 PM, Gavin Flower wrote: 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 controv

Re: [GENERAL] Most effective settings for deleting lots of data?

On 08/26/2015 02:34 PM, Alan Hodgson wrote: On Wednesday, August 26, 2015 08:25:02 PM Cory Tucker wrote: What settings would you recommend? Also, it just occurred to me that I should try to disable/drop all indexes (especially since they will be recreated) later so that those are not updated in

Re: [GENERAL] get first and last row in one sql as two columns

On 09/02/2015 05:14 PM, Tom Smith wrote: Hi: I need to get the first and last tow in one sql like below select first(col1), last(col1) from table order by col1 I saw some posting in wiki with a custom function (or C extention) to do this. Is it widely used and reliable? https://wiki.postgres

Re: [GENERAL] get first and last row in one sql as two columns

On 09/02/2015 05:14 PM, Tom Smith wrote: Hi: I need to get the first and last tow in one sql like below select first(col1), last(col1) from table order by col1 I saw some posting in wiki with a custom function (or C extention) to do this. Is it widely used and reliable? https://wiki.postgres

Re: [GENERAL] Import Problem

On 09/16/2015 09:27 AM, Ramesh T wrote: Hi All, I'm using or2pg tool ,I exported data but I have to change the schema import to postgres database.exported data more than gb. Can you please let me know, how to do the change the name in data script..? sed is your friend, if you have a

Re: [GENERAL] using postgresql for session

On 10/07/2015 10:53 AM, Bill Moran wrote: On Wed, 7 Oct 2015 09:58:04 -0600 "john.tiger" wrote: has anyone used postgres jsonb for holding session ? Since server side session is really just a piece of data, why bother with special "session" plugins and just use postgres to hold the data and r

Re: [GENERAL] ID column naming convention

On 10/13/2015 11:36 AM, 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 t

Re: [GENERAL] Simple way to load xml into table

On 10/15/2015 11:38 AM, Emi wrote: Hello, For psql 8.3, is there a simple way to load xml file into table please? E.g., True test1 e1 false test2 Results: t1 (c1 text, c2 text, c3 text): c1| c2 | c3 - true| test1 | e

Re: [GENERAL] Where do I enter commands?

ok. now who has the url to the pithy heres-why-you-really-want-the-command-line. It distills to something about actually knowing what you’re doing. > On Oct 24, 2015, at 9:29 PM, David Blomstrom > wrote: > > Good tip; I can now see the database I created. Thanks. > > On Sat, Oct 24, 2015

Re: [GENERAL] Where do I enter commands?

> On Oct 25, 2015, at 3:21 AM, Joshua D. Drake wrote: > > I would ignore Rob, he obviously is suffering from a lack of coffee. Our > community always tries to help new users. It is great to see you here. > > Sincerely, > > JD > Always the best advice :) OK, Coffee’d up now. Apologies for

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8:10 PM, David Blomstrom wrote: @ Adrian Klaver: Oh, so you're suggesting I make separate tables for kingdoms, classes and on down to species. I'll research foreign keys and see what I can come up with. I hope I can make separate tables for ma

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 09:22 AM, Adrian Klaver wrote: On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8:10 PM, David Blomstrom wrote: @ Adrian Klaver: Oh, so you're suggesting I make separate tables for kingdoms, classes and on down to species.

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 11:14 AM, Adrian Klaver wrote: On 10/26/2015 08:32 AM, Rob Sargent wrote: On 10/26/2015 09:22 AM, Adrian Klaver wrote: On 10/26/2015 08:12 AM, Rob Sargent wrote: On 10/26/2015 08:43 AM, Jim Nasby wrote: On 10/25/15 8:10 PM, David Blomstrom wrote: @ Adrian Klaver: Oh, so you&#x

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 02:29 PM, David Blomstrom wrote: Sorry for the late response. I don't have Internet access at home, so I only post from the library or a WiFi cafe. Anyway, where do I begin? Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program) to organize data. I then save

Re: [GENERAL] Recursive Arrays 101

altername-name back to species may get you multiple species. Or, welcome to postgres' arrays-as-column: you can have one column, maybe called aliases which is an array of string. It gets still more complicated when you get into "specialist names." ;) But the system I've se

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 03:21 PM, Gavin Flower wrote: 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

Re: [GENERAL] Recursive Arrays 101

On 10/26/2015 05:28 PM, David Blomstrom wrote: No, I'm on a Mac running OS X El Capitan. I don't have my mac with me today so this is a little rough. Cmd-Spacebar Terminal This should find the actual normal terminal. Click on it. at the prompt therein: psql --username davdi --host localhos

Re: [GENERAL] Recursive Arrays 101

You will do what you need to do but please do not claim that pg is not Mac compatible. Many on this thread are regular Mac/pg users. We all thought we were doing you a favour in trying to free you from GUI tools. For us they are restraining. For you they may be liberating. Stick with pgAdmin and

Re: [GENERAL] Recursive Arrays 101

the operating system. psql from there gets you to a database shell talking to your server. You can indeed do a lot of what you want from pgadmin. I'm mostly to blame for this thread so if you would like to take this up with me privately you're welcome to do so. > >> On Mon, Oct 2

Re: [GENERAL] Importing CSV File

On 10/27/2015 10:04 AM, Adrian Klaver wrote: On 10/27/2015 08:44 AM, Jeff Janes wrote: On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom mailto:david.blomst...@gmail.com>> wrote: I tried to import a CSV file into a PostgreSQL table using pgAdmin III. I got an error message: "extra data a

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint using a regexp to limit it to containing digits and '.'. However I am finding I can add values with other characters to a column of this type. Is this to be ex

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 12:29 PM, Eric Schwarzenbach wrote: Thank you! (Slapping head) Your regexp seems to do the trick. On 10/29/2015 01:49 PM, Rob Sargent wrote: On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 04:29 PM, Eric Schwarzenbach wrote: On 10/29/2015 03:44 PM, Alvaro Herrera wrote: segment needing to be filled with zeros to a fixed length.) (Also FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$') Cheers, Eric So it can start with a dot, but not end with

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 05:01 PM, Tom Lane wrote: Eric Schwarzenbach writes: ... (Also FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$') Um, that's not gonna do what you want at all. Outside brackets, a dot is a wildcard. (Regex syntax is a mess :-(.) regard

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid integ

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid integ

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:38 PM, David Blomstrom wrote: Ah, yes - "Execute SQL." It created the table this time. Awesome. One other question - when I close the SQL window, it asks me if I want to save the file. Is there any special reason for saving it? It looks like it simply saved a copy of the query

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:55 PM, David Blomstrom wrote: The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:55 PM, David Blomstrom wrote: The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

Re: [GENERAL] Taking lot time

On 11/03/2015 07:42 AM, Ramesh T wrote: I have a Query it taking a lot of time to fetch results so,explain query gave "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)" " Hash Cond: (itd.tran_id = iad._adj__id)" " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029 wid

Re: [GENERAL] Recursive Arrays 101

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, what we have been successfully doing for this problem for our app is do it this way : parents int[] -- where parents stores the path

Re: [GENERAL] Recursive Arrays 101

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, what we have been successfully doing for

Re: [GENERAL] Recursive Arrays 101

On 11/05/2015 11:08 AM, Gavin Flower wrote: 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

Re: [GENERAL] Automate copy - Postgres 9.2

> On Jun 8, 2016, at 8:04 PM, Patrick B wrote: > > > > 2016-06-09 13:58 GMT+12:00 John R Pierce >: > On 6/8/2016 6:47 PM, Patrick B wrote: > > 21 is the number of IDS that I wanna perform that COPY command > > that didn't answer my question. if you call your

Re: [GENERAL] How to pass jsonb and inet arguments to a stored function with JDBC?

> On Jun 14, 2016, at 7:33 AM, Alexander Farber > wrote: > > Dear PostgreSQL users, > > I have a stored procedure defined as: > > CREATE OR REPLACE FUNCTION words_merge_users( > IN in_users jsonb, > IN in_ip inet, > OUT out_uid integer) > RETURNS integer AS >

Re: [GENERAL] Stored procedure version control

On 07/01/2016 06:17 PM, Jim Nasby wrote: On 6/30/16 9:16 AM, Merlin Moncure wrote: It's not really necessary to create version down scripts. In five years of managing complex database environments we've never had to roll a version back and likely never will; in the event of a disaster it's pr

Re: [GENERAL] pg_dump fundenental question

On 07/05/2016 10:54 AM, David G. Johnston wrote: On Tue, Jul 5, 2016 at 10:54 AM, J. Cassidy >wrote: Hello all, I have hopefully an "easy" question. If I issue the pg_dump command with no switches or options i.e. /usr/local/pgsql/bin/pg_dump -v dbab

Re: [GENERAL] pasting a lot of commands to psql

If, on the wild chance you're an emacs user, if the section is in a ".sql" buffer Ctrl-C Ctrl-B will send the entire buffer. Not sure if there's a practical limit on the number of line/statements, but I've used this with a couple of "pages" worth of sql. On 07/07/2016 11:48 AM, Dmitry Shalash

Re: [GENERAL] Detecting if current transaction is modifying the database

On 08/05/2016 01:48 PM, Christian Ohler wrote: Thanks, fair point. I should have mentioned that I know about triggers but was hoping to find a less invasive mechanism (IIUC, I'd have to install a trigger on every table) – it seems to me that Postgres should just be able to tell me whether CO

Re: [GENERAL] Detecting if current transaction is modifying the database

On 08/05/2016 02:15 PM, Christian Ohler wrote: On Fri, Aug 5, 2016 at 12:55 PM, Rob Sargent wrote: What sort of interface are you looking for. Where/When would you grab the information? Do what with it? Log triggers are the typical pattern here (with packages just for that sort of thing

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

On 08/23/2016 07:44 AM, Francisco Olarte wrote: Hi pinker: On Tue, Aug 23, 2016 at 2:26 PM, pinker wrote: I am just surprised by the order of magnitude in the difference though. 2 and 27 minutes that's the huge difference... I did another, simplified test, to make sure there is no duplicates

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

On 08/23/2016 08:34 AM, Francisco Olarte wrote: On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent wrote: On 08/23/2016 07:44 AM, Francisco Olarte wrote: On Tue, Aug 23, 2016 at 2:26 PM, pinker wrote: I am just surprised by the order of magnitude in the difference though. 2 and 27 minutes that&#

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

> On Sep 15, 2016, at 1:20 AM, Yogesh Sharma > wrote: > > Dear John and all, > > >8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was > >released in 2009, the final 8.1.23 release was in 2010, after which it was > >>dropped. > Yes, we understood your point. > But we r

Re: [GENERAL] Chante domain type - Postgres 9.2

On 09/26/2016 08:14 AM, Adrian Klaver wrote: On 09/26/2016 06:54 AM, Thomas Kellerer wrote: Rakesh Kumar schrieb am 26.09.2016 um 15:08: You sound like you think that varchar(50) is somehow cheaper than text. The biggest impediment to text cols in other RDBMS is no index allowed. If PG ha

Re: [GENERAL] Incrementally refreshed materialized view

Of course 9.5 is the current release so the answer is Yes, since 9.5 On 09/26/2016 12:29 PM, Rakesh Kumar wrote: *Does PG support INCREMENTAL MV ? Looks like not (until 9.5)*

Re: [GENERAL] Query help

> On Oct 4, 2016, at 9:31 PM, Bret Stern > wrote: > > Good evening, > I'm curious about a way to ask the following question of my vendors > table. > > psuedo1 "select all vendors which exist in BUR and EBC and SNJ" > > and > psuedo2 "select all vendors which DO NOT exist in all three show roo

Re: [GENERAL] high transaction rate

2. Accumulation of dead tuples leading to what should be very short operations taking longer. No idea of that is helpful but where I would probably start Please tell me that in this case, updating 2 (big)integer columns does not generate dead tuples (i.e. does not involve a insert/delete pa

Re: [GENERAL] FreeBSD 10 => 11: Dump and reload your PostgreSQL database unless you like it broken

On 12/07/2016 02:06 PM, Kevin Grittner wrote: On Wed, Dec 7, 2016 at 7:33 AM, Michael Sheaver wrote: I would like to echo the sentiment on collation and expand it to character sets in general. When issues with them come up, they do take an incredible amount of time and effort to resolve, and

Re: [GENERAL] high transaction rate

On 12/07/2016 09:58 AM, John R Pierce wrote: On 12/7/2016 8:47 AM, Rob Sargent wrote: Please tell me that in this case, updating 2 (big)integer columns does not generate dead tuples (i.e. does not involve a insert/delete pair). if the fields being updated aren't indexed, and there&#

Re: [GENERAL] high transaction rate

On 12/07/2016 03:32 PM, John R Pierce wrote: On 12/7/2016 2:23 PM, Rob Sargent wrote: How does your reply change, if at all, if: - Fields not index - 5000 hot records per 100K records (millions of records total) - A dozen machines writing 1 update per 10 seconds (one machine writing every

Re: [GENERAL] huge table occupation after updates

> On Dec 10, 2016, at 6:25 AM, Tom DalPozzo wrote: > > Hi, > you're right, VACUUM FULL recovered the space, completely. > So, at this point I'm worried about my needs. > I cannot issue vacuum full as I read it locks the table. > In my DB, I (would) need to have a table with one bigint id field+

Re: [GENERAL] huge table occupation after updates

> On Dec 10, 2016, at 7:27 AM, Tom DalPozzo wrote: > > Hi, > I'd like to do that! But my DB must be crash proof! Very high reliability is > a must. > I also use sycn replication. > Regards > Pupillo > > > > > Are each of the updates visible to a user or read/analyzed by another > activity?

Re: [GENERAL] huge table occupation after updates

> On Dec 10, 2016, at 10:01 AM, Tom DalPozzo wrote: > > 2016-12-10 16:36 GMT+01:00 Rob Sargent : > > > On Dec 10, 2016, at 7:27 AM, Tom DalPozzo wrote: > > > > Hi, > > I'd like to do that! But my DB must be crash proof! Very high reliability >

Re: [GENERAL] About the MONEY type

> On Dec 18, 2016, at 5:23 PM, Gavin Flower > 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 timezone... >>> rather, it converts it to an internal representat

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while. > On Dec 29, 2016, at 5:32 PM, Tim Uckun wrote: > > Honestly I don't even like JS. Having said that I am not too crazy

Re: [GENERAL] COPY: row is too big

> On Jan 2, 2017, at 10:13 AM, Adrian Klaver wrote: > >> On 01/02/2017 09:03 AM, vod vos wrote: >> You know, the csv file was exported from other database of a machine, so >> I really dont want to break it for it is a hard work. Every csv file >> contains headers and values. If I redesign the t

Re: [GENERAL] Index impact on update?

On 01/04/2017 09:59 AM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? Details: I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and Longitu

Re: [GENERAL] Index impact on update?

On 01/04/2017 09:59 AM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? Details: I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and Longitu

Re: [GENERAL] COPY: row is too big

On 01/05/2017 05:44 AM, vod vos wrote: I finally figured it out as follows: 1. modified the corresponding data type of the columns to the csv file 2. if null values existed, defined the data type to varchar. The null values cause problem too. so 1100 culumns work well now. This problem wa

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/05/2017 10:18 AM, Job wrote: Hello guys, a very strange thing: after upgrading from 8.4.22 to 9.6.1 i noticed, under heavy beanchmarks, a really slowdown of Postgresql 9.6.1, with the machine really "without breath". By replacing Postgresql 8.4.22 evberything returns working fine. With

Re: [GENERAL] COPY: row is too big

On 01/05/2017 11:46 AM, Adrian Klaver wrote: On 01/05/2017 08:31 AM, Rob Sargent wrote: On 01/05/2017 05:44 AM, vod vos wrote: I finally figured it out as follows: 1. modified the corresponding data type of the columns to the csv file 2. if null values existed, defined the data type to

Re: [GENERAL] Materialized view vs. view

> > > "A rose by any other name would still smell as sweet”. Actually there’s no “still” in that line, if you’re quoting Shakespeare. And the full “That which we call a rose …” is truly appropriate here. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] update error with serializable

On 01/20/2017 10:05 AM, Kevin Grittner wrote: On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same row (my bug apart but I d

Re: [GENERAL] Why is table not found?

You must quite as "Raum" > On Jan 31, 2017, at 6:45 AM, Egon Frerich wrote: > > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I created a db 'Hausrat' with a table "Raum". If I look in pgadmin I > find the the table. In sql field is shown: > >> -- Table: "Raum" >> >> -- DROP TABLE "R

Re: [GENERAL] Generating an XSD file from an existing database

> On Dec 6, 2015, at 2:30 PM, Blake McBride wrote: > > Greetings, > > I need an XML specification for my exiting schema - an XSD file. Reading the > docs I see schema_to_xml but I do not understand: > > A. Will that produce the XSD file I seek? > > B. I do not understand the documentation

Re: [GENERAL] Convert 2 foreign key values within the same SELECT

On 01/04/2016 12:36 PM, gvim wrote: I have a query which successfully retrieves id values "me" and "you" when 2 planetary values are supplied: SELECT l.me_id AS me, l.you_id AS you, a.l AS left, a.aspect, a.r AS right, l.id AS link_id, c.comment FROM aspects a, links_aspects

Re: [GENERAL] Support for BDR in 9.5?

On 01/08/2016 10:39 AM, Andrew Biggs (adb) wrote: Can anyone tell me if PostgreSQL 9.5 supports (either natively or by extension) the BDR functionality? I tried it out and ran into issues, but it could well have been I was doing something wrong. Thanks! Andrew I'm sure those who might be

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

On 01/22/2016 03:53 PM, Andrew Sullivan wrote: This is why I posted all that stuff about what the IETF does some while ago. There is definitely more than one way to do this. Best regards, A Just a gut feeling, but I think this thread had driven the rest of the regulars to drink at a bar with

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 12:55 PM, Melvin Davidson wrote: Your problem seems strange as it has never been previously reported for anyone else that has _successfully_ set up partioning. Perhaps is you provide just a little bit more detail we might be able to help you. Useful and needed information would

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 03:00 PM, Joshua D. Drake wrote: On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Application instead of the master/base table insert Insert into the child table directly ba

Re: [GENERAL] Table size for partitioned setup

On 03/28/2016 02:41 PM, Mat Arye wrote: Hi All, I am writing a program that needs time-series-based insert mostly workload. I need to make the system scaleable with many thousand of inserts/s. One of the techniques I plan to use is time-based table partitioning and I am trying to figure out

Re: [GENERAL] Table size for partitioned setup

On 03/28/2016 02:55 PM, Mat Arye wrote: This will run on EC2 (or other cloud service) machines and on ssds. Right now runs on m4.4xlarge with 64GiB of ram. Willing to pay for beefy instances if it means better performance. On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent <mailto:robjs

Re: [GENERAL] Non-default postgresql.conf values to log

> On Apr 7, 2016, at 8:26 AM, Alex Ignatov wrote: > > > >> On 07.04.2016 16:53, Tom Lane wrote: >> Alex Ignatov writes: >>> My question is: is there any option(s) to log non-default >>> postgresql.conf values to log file? >> No, but you can easily find all the non-default settings by queryin

Re: [GENERAL] 20160417105248.d20dcefed39b5d9031c6b...@potentialtech.com

> On Apr 17, 2016, at 12:41 PM, Sergei Agalakov > wrote: > > I know about DBSteward. I don't like to bring PHP infrastructure only to be > able to compare two dumps, > and to deal with potential bugs in the third party tools. The pg_dump in > other hand is always here, and is always trusted.

Re: [GENERAL] migration from PointBase to PostgreSQL

1. get an ascii dump of the source database. 2. check if for consistency if you're ambitious or suspicion by nature (or you've been through this a number of times before and discovered that this is _always_ necessary) 3. create the target database/schema 4. convert source data to target as per

Re: [GENERAL] Understanding PostgreSQL Storage Engines

On 10/08/2010 03:39 PM, Adrian Klaver wrote: > On Friday 08 October 2010 2:30:40 pm Carlos Mennens wrote: >> I know that MySQL uses MyISAM storage engine by default and was just >> trying to look on Google to try and see if I could understand what >> storage engine does PostgreSQL use by default

Re: [GENERAL] Postgres won't start after setting ssl=on

Does postgres run as root or as the postgres user. I suspect you want postgres to own that file. Mike Christensen wrote: Hi, I'm trying to require SSL for Postgres connections from certain IPs.. This is on Postgres 9.0. First, I've followed the directions at: http://www.postgresql.org/docs/9

Re: [GENERAL] Adding a New Column Specifically In a Table

On 10/14/2010 04:32 AM, Thom Brown wrote: > On 13 October 2010 23:19, Raymond O'Donnell wrote: >> On 13/10/2010 19:04, Carlos Mennens wrote: >>> >>> OK so I have read the docs and Google to try and find a way to add a >>> new column to an existing table. My problem is I need this new column >>>

Re: [GENERAL] how to get current sql execution time?

\timing On 10/14/2010 04:47 PM, sunpeng wrote: > when I use the psql to send a sql, how to get current sql execution time? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to write an optimized sql with two same subsql?

On 10/14/2010 05:34 PM, sunpeng wrote: > We have a table A: > CREATE TABLE A( >uid integer, >groupid integer > ) > Now we use this subsql to get each group's count: > SELECT count(*) as count > FROM A > GROUP BY groupid > ORDER BY groupid > > Then we try to find the group pair with follo

Re: [GENERAL] installing from source in Windows

No at all familiar with MinGW, but I've done some "./configure"s in my time. ./configure scripts 1) check to see what the local build environment contains and 2) which special feature you wish to toggle. But the pretty much assume a unix-like env. Could you put cygwin on rather than just ming

Re: [GENERAL] Constraint: string length must be 32 chars

Merlin Moncure wrote: On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber wrote: Hello, I'm trying to create a table, where md5 strings will serve as primary keys. So I'd like to add a constraing that the key length should be 32 chars long (and contain [a-fA-F0-9] only): why don't y

Re: [GENERAL] Constraint: string length must be 32 chars

te: Thank you for your advices. I actually would like to store GPS coordinates, but anonymously, so I was going to save md5(my_secret+IMEI) coming from a mobile... I have to lookup if uuid is supported there Regards Alex On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent wrote: Merlin Mon

Re: [GENERAL] Constraint: string length must be 32 chars

Alexander Farber wrote: Hello, really good advices here! But - On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent wrote: I just read the "anonymously" part, so I take it you have ruled out recording the given coordinate components directly, in multiple columns presumably? Otherwis

Re: [GENERAL] Composite Index question

Hm. Run some queries; drop the second version of the index definition; re-run the same queries; report to the group. The redundant index isn't helping, that much is certain. On 10/20/2010 05:43 PM, DM wrote: > Composite Index question: > > I have composite index on 3 columns on a table, by mista

Re: [GENERAL] Composite Index question

any risk. > > I can simulate and test this but i was to checking to see If any one > knows off hand about this. > > > > I can simulate it but > On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > > Hm. Run some que

Re: [GENERAL] Custom cache implemented in a postgresql C function

Are you sure you cache needs to grow endlessly? Otherwise you could use RequestAddinShmemSpace and manage you're map within that space, perhaps "overwriting" chunks on an LRU basis or a rollover. i.e. Grab it all and do your own management within that single block of shmem. Caches are best for thi

Re: [GENERAL] Composite Index question

ovals > and stuff. > > I wnat to know if there is any major performance drawback for having > duplicate composite index, so that i can push hard for the change. Let > me know. > > thanks for your looking into this. > > > On Wed, Oct 20, 2010 at 5:10 PM, Rob Sarge

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

On 10/21/2010 10:27 AM, Scott Marlowe wrote: > On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt wrote: >>> >>> There are only two tables in the query. >>> >> >> Tim, >> >> No, your query is written incorrectly. I don't understand why you come on >> to this list all hostile and confrontational. Reg

  1   2   3   4   >