Re: [GENERAL] Invoice Table Design

. I have stumbled upon table inheritance <https://www.postgresql.org/docs/9.6/static/ddl-inherit.html> available in 9.6, which I think might be helpful. Thanks again for the feedback! Best, Rob -- View this message in context: http://postgresql.nabble.com/Invoice-Table-

[GENERAL] temp tables and sequences in functions

Postgres v8.2 I'm having a problem with using temporary tables and sequences in a function. It seems that the temp tables are not being removed once the function has completed. I'm also running into OID conflicts when I run the function in another session. I've had to do things like this to be

[GENERAL] libpq block allocated before my malloc handler inits?

he list; I haven't subscribed to the list. Thank you very much for your input, Rob Stoddard -- 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] libpq block allocated before my malloc handler inits?

sslmode=disable in the connection string, to no avail. It still crashes with an invalid free. Perhaps I should try to compile the database with no SSL in it at all? Would that make any difference? Thanks for the input, Rob Stoddard Russell Smith wrote: rob wrote: I am trying to build

[GENERAL] Dynamic datalogging function/scheme

Hello, Looking for advice on creating as dynamic a datalogging scheme as I can using plpgsql. The idea would be to have a trigger function that I could drop on any table in my database. Then on update, the function would iterate through each field, and check to see if NEW. <> OLD.. Then a fu

Re: [GENERAL] Dynamic datalogging function/scheme

Thanks Mike! Much appreciated. -Rob ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] Embedded SQL inherently faster than others?

Dann Corbit snip: -Original Message- From: Kevin [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:18 AM To: [EMAIL PROTECTED] Subject: [GENERAL] Embedded SQL inherently faster than others? Hello, I'm looking for absolute maximum database result speed and if that means writing so

Re: [GENERAL] PGSQL on shared hosting

another instance on the shared server. Something you don't have to worry about if your provider uses the context patch, *ahem*. Cheers, -- Rob Fielding [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

[GENERAL] Re: Re: Why PostgreSQL is not that popular as MySQL?

e code, community and support doesn't follow suit! my 2 cents --rob - Original Message - From: "The Hermit Hacker" <[EMAIL PROTECTED]> To: "Ronald Cole" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, December 04, 2000 3:08 PM Subject

[GENERAL] Re: COPY doesnt work on views

print join("\t",@row); #or whatever } $sth->finish; $dbh->disconnect; I'm sure the other postgres/perl interfaces have similar capabilities. --rob ----- Original Message - From: "Steve Heaven" <[EMAIL PROTECTED]> To: "rob" <[EMAIL PROTECTED]

[GENERAL] Re: Daily Digest V1 #108

Why is pg_dump not correct? You need to elaborate on the parameters of the situation. --rob - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 13, 2000 6:26 PM Subject: Daily Digest V1 #108 > Daily Digest (mime) - Volum

[GENERAL] Re: DBD::Pg

The DBD::Pg module REQUIRES two environment variables to be set and exported. (last time I checked) You might also try "find / -iname libpq-fe.h" in the future to find things like this. Good luck. --rob - Original Message - From: "Vince Vielhaber" <[EMAIL

[GENERAL] Re: trouble with db-restore

I have seen that before on my own system. Same PG version for dump/restore. Wasn't a big deal for me to redo the views since I had so few (i.e. 1) I never reported it, but it happened more than once. Not sure of version, but was 7.0.1 or 7.0.2. --rob - Original Message - From:

[GENERAL] Re: trouble with db-restore

Tried this morning to recreate the problem, but could not (as of v 7.0.2). Perhaps it was 7.0.1? Sorry for not reporting earlier. I'll keep my eye out for this issue in the future. --rob - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "rob"

[GENERAL] Re: Re: [ADMIN] ODBC connect in ERWin

I'm going to ask the obvious, but . . . Can you ping the Unix box from the ER workstation? Did you start the postmaster with -i to allow remote connections? Does the same ODBC DSN work from another program? (i.e. linking a Pg table to an Access DB) --rob - Original Message -

[GENERAL] Re: converting .xls to delimited file

search freshmeat.net there is a tool called something like xls2xml that was derived from the star office package. I haven't tried it, but looks very good. --rob - Original Message - From: "Martin Lillepuu" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent:

[GENERAL] Re: TRIGGER

The docs are not very clear, but I think that trigger functions must all return opaque. --rob - Original Message - From: "HC Mercy T. de Vera" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 17, 2001 10:11 PM Subject: TRIGGER > > I'

[GENERAL] Re: Query question

SELECT * FROM my_table WHERE my_field LIKE 'A%'; --rob - Original Message - From: "Jorch" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, January 16, 2001 12:39 PM Subject: Query question > How can I make query > > S

[GENERAL] Re: Help with query. (*)

Read the docs on "exists" that should be what you are looking for. --rob - Original Message - From: "Diehl, Jeffrey" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: "Diehl, Jeffrey" <[EMAIL PROTECTED]> Sent: Tues

[GENERAL] Re: Re: MS Access data to PostgrSQL data

Search this (and the novice) list for importing from Excel. I recently posted some tips there on using tab delimited text files and the copy command. DBD has a file size limit. --rob - Original Message - From: "Keith G. Murphy" <[EMAIL PROTECTED]> To: <[EMAIL PROTE

[GENERAL] Re: Re: is PG able to handle a >500 GB Database?

. That is not true? --rob - Original Message - From: "Oliver Elphick" <[EMAIL PROTECTED]> To: "Florent Guillaume" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, January 19, 2001 5:27 PM Subject: Re: Re: is PG able to handle a >500 GB D

currval was (Re: [GENERAL] Re: Re: is PG able to handle a >500 GB Database? )

I use for sequences for row versioning not just numbering. I'm glad you pointed this out. I'll need to revisit that code. Is there a correct way to "initialize" currval without incrementing the sequence? --rob - Original Message - From: "Tom Lane"

Re: currval was (Re: [GENERAL] Re: Re: is PG able to handle a >500 GB Database? )

sequence by doing currval(nextval(version)-1) when step one is true. without running into problems across backends? It would increase overhead a lot, but I'll live with that. Am I just fooling myself again? Perhaps I'm just a fool. --rob - Original Message - From: &qu

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

[GENERAL] Update using non-existent fields does not throw an error

Greetings! An update query is apparently succeeding, even though the query refers to fields that do not exist. Here's the query: update inventory set x_coordinate = (select x_coordinate from bases where base = '101'), y_coordinate = (select y_coordinate from bases where base = '101') where char

Re: [GENERAL] Update using non-existent fields does not throw an error

Thanks very much. Now that you've explained it, it should have been obvious. RobR -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, March 16, 2015 12:21 PM To: Rob Richardson; pgsql-general@postgresql.org Subject: RE: Update using non-existent f

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

[GENERAL] Running pg_upgrade under Debian

Hello, I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /home/postgres/data94/userqueries -U pguserqu

Re: [GENERAL] Running pg_upgrade under Debian

On Sat, 2015-04-18 at 00:25 +0200, Karsten Hilbert wrote: > On Sat, Apr 18, 2015 at 08:09:43AM +1000, rob stone wrote: > > > I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this > > error:- > > > > > > postgres@roblaptop:/usr/li

Re: [GENERAL] Running pg_upgrade under Debian

On Fri, 2015-04-17 at 16:16 -0700, Adrian Klaver wrote: > On 04/17/2015 03:09 PM, rob stone wrote: > > Hello, > > > > I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this > > error:- > > > > > > postgres@roblaptop:/usr/li

Re: [GENERAL] Running pg_upgrade under Debian

On Mon, 2015-04-20 at 18:05 -0400, Bruce Momjian wrote: > On Mon, Apr 20, 2015 at 03:02:48PM -0700, Adrian Klaver wrote: > > >But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do > > >as well. > > > > > > > I don't think it is about the underlying programs, it is about > > te

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] Server tries to read a different config file than it is supposed to

On Sat, 2015-05-23 at 04:23 -0700, twoflower wrote: > I thought I understood how specifying a config file path for the > server works, but that's apparently not the case. > > The cluster data is at /storage/postgresql/9.4/data. > > The config files are at /etc/postgresql/9.4/main (this is the

Re: [GENERAL] How to skip duplicate records while copying from CSV to table in Postgresql using "COPY"

> I think I need to ask more specific way. I have a table say `table1`, where I > feed data from different CSV files. Now suppose I have inserted N records to > my table `table1` from csv file `c1`. This is ok, next time when again I am > importing from a different CSV file say `c2` to `table1`

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] Using the database to validate data

m user B refers to that, then user B needs to wait until user A's file has been processed successfully. Without knowing all the details I can envisage a scenario where data being loaded could reference "good" data already in the DB as well as referencing data that exists within that f

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] PostgreSQL Developer Best Practices

more tables. As well as assigning you a frequent flyer number they also assign another integer identifier. A bit of common sense goes a long way when designing an application. Cheers, rob -- 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] 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] BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?

enabled > Active Persistent Links 0 > Active Links 0 > I'm a Linux user. However, libpq version 8.4.1 doesn't jell with version 9.3 of PostgreSql. So, are you trying to obtain the failure "reason" when a "BEGIN", "COM

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] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

t exist LINE 1: SELECT * FROM rhubarb ^ LOCATION: parserOpenTable, parse_relation.c:986 SQLSTATE 42P01 is the error "undefined_table". Note that you have to use pg_send_query to take advantage of pg_get_result, etc. HTH, Rob -- 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] 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

[GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I get errors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its methods already exist. For example, I am trying to run the cod

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

Tim, Thank you, but I think I already did that. The query is a dollar-quoted string, so there should be no need to do anything with the single quote marks within it, so I would have thought the query engine would already know that it's text. But after seeing the first error message, I explici

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

I should have mentioned (twice now) that I'm running under Windows 7. RobR -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, October 15, 2015 10:19 AM To: Rob Richardson Cc: pgsql-general General Subject: Re: [GENERAL] How can I use crosstab functo

Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?

By George, I think I've got it! When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the crosstab methods and my sample query worked. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

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] PSQL Tools

am. It can also do everything else you might want to manipulate a data base. Another handy feature is import/export from/to either a delimited file or XML format. HTH, Rob -- 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] Multiple word synonyms (maybe?)

oking at this from an entirely different perspective, why are you not using ICD codes to identify patient events? It is a one to many relationship between patient and their events identified by the relevant ICD code and date. Given that MI has several applicable ICD codes you can use a select alon

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, Coff

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] Partitioned tables do not return affected row counts to client

ted_rows returns its value from the last SQL statement executed. So, if the last one was a "COMMIT" say, then it returns zero. I guess you already know this. Without knowing the complete circumstances, all I can suggest is running a query using your partitioning rules to verify that the

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] Automate copy - Postgres 9.2

ve to do again in the future, you could download ExecuteQuery which has a function to export a result set as either XML or CSV delimited file output. OTOH, if this is going to be something run on a regular basis, I think you'd be better off writing a little program, or as David suggested embe

Re: [GENERAL] Converting Postgres SQL constraint logic to PHP?

lidation. Also, views are updateable and you'd have to check the tables and columns making up the view. I've never used pg_meta_data but it might be helpful. Another thought would be to write a program that scanned all the tables in your database and wrote out a table in your applicati

Re: [GENERAL] Automate copy - Postgres 9.2

On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote: > > > 2016-06-14 9:47 GMT+12:00 Adrian Klaver : > > On 06/13/2016 02:42 PM, Patrick B wrote: > > > Hi guys, > > > > > > I created this function: > > > > > >             CREATE or REPLACE FUNCTION function(account_id > > > integer) > > > > > >

Re: [GENERAL] Index seems "lost" after consecutive deletes

On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote: > Em 13/06/2016 22:33, Edson Richter escreveu: > > I've a table "A" with 4,000,000 records. > > > > I've decided to delete records from oldest to newest but I can't  > > delete records that have references in tables "B", "C" or "D". > > > >

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] Moving from PHP to Java: A result was returned when none was expected.

   AND player2 = in_uid >     /* and it is second player's turn */ >     AND (played2 IS NULL OR played2 < played1); >     END IF;  > END > $func$ LANGUAGE plpgsql; > Aren't you supposed to use prepareCall? Also 7.4 documentation is a tad out of date but the method is the same. HTH, Rob -- 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] 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] Is it possible to control the location of the lock file when starting postgres?

g port 5435 for 9.6beta testing) psql does not start the database engine. Whichever method you use to start it, that user (usually postgres) has to own the /var/run/postgresql path and have write permissions. E.g.:- -rw-r--r-- 1 postgres postgres 5 Jul 21 03:57 9.6-main.pid A similar set-up

[GENERAL] 9.6beta3

rrect path. Thanks, Rob -- 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] 9.6beta3

On Fri, 2016-07-29 at 12:15 -0400, Tom Lane wrote: > rob stone writes: > > So, could somebody tell me if the 9.6beta1 version of initdb > > contained > > an incorrect version constant? > > It did, see > > https://git.postgresql.org/gitweb/?p

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

  1   2   3   4   5   6   7   8   >