Re: [GENERAL] Puzzled by UNION with unknown types

Thanks Tom! -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ * *https://agency-software.org/demo/client * ken.tan...@agency-software.org (253) 245-3801 Subscribe to the

[GENERAL] Puzzled by UNION with unknown types

Hi. I've solved my practical problems, and I know unknown types are just bad, but am still curious about why some of these cause errors, and others don't. It seems contingent on whether the value is generated on the fly, but I don't understand the mechanism or distinction. Any help appreciated!

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

> > ...least excruciating version of the relevant text... Ouch, I'm glad you folks take care of reading such stuff! What you put in the documentation was much much clearer--just wish I had found it! Speaking of which, I had looked at the "From" section of the "SELECT" page (

[GENERAL] Fields re-ordered on JOIN with * and USING

Hi. I recently noticed that when doing a SELECT * with USING, that the join field(s) appear first in the output. I'd never noticed that before, and was just curious if that is expected behavior or not. Thanks. Ken CREATE TEMP TABLE t1 ( f1 INTEGER, f2 INTEGER UNIQUE, f3 INTEGER, f4

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

On Thu, Jun 29, 2017 at 9:34 AM, Jeff Janes wrote: > >> Well sure, I can see it increases your chances of getting _something_ >> restored. But there's also a lot to be said for ensuring that _all_ your >> data restored, and did so correctly, no? >> > > Record the errors,

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

Thanks for the responses. For me, using the 9.2 binary was the winner. Shoulda thought of that! On Wed, Jun 28, 2017 at 1:30 PM, Tom Lane wrote: > > Generally speaking, it helps a lot if you don't insist on restoring the > output in a single transaction. In this case, that

[GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

Hi. I've got a CentOS server with 9.2 and 9.6 both running. (Both from PGDG). I've got a cron job that transfers data from one DB to another, that recently stopped working, and I traced it to my installing 9.6. The dump comand is pretty straightforward: pg_dump -c -O -t "${prefix}*"... But

Re: [GENERAL] Ordering of window functions with no order specified?

Thanks for the replies! -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ * *https://agency-software.org/demo/client * ken.tan...@agency-software.org (253) 245-3801 Subscribe

[GENERAL] Ordering of window functions with no order specified?

Hi. If you use a window function and don't specify an order, will the rows be processed in the same order as the query results? In this particular case, I'm wondering about row_number(), and whether I explicitly need to repeat the ordering that's already specified in the query? SELECT

Re: [GENERAL] Limiting DB access by role after initial connection?

On Sun, Jun 11, 2017 at 12:15 PM, Bruno Wolff III <br...@wolff.to> wrote: > On Fri, Jun 09, 2017 at 21:14:15 -0700, > Ken Tanzer <ken.tan...@gmail.com> wrote: > >> On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <br...@wolff.to> wrote: >> >> Seems to

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <br...@wolff.to> wrote: > On Thu, Jun 08, 2017 at 22:37:34 -0700, > Ken Tanzer <ken.tan...@gmail.com> wrote: > >> >> My approach was to have the initial connection made by the owner, and then >> after successf

Re: [GENERAL] Limiting DB access by role after initial connection?

> > As to your very last point (suggestions about other approaches), is it > impossible or impractical to migrate to a scheme in which each user > actually has a data base role and their own password? Postgresql has really > great facility for managing database authorization and access by means of

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 11:22 AM, Joe Conway <m...@joeconway.com> wrote: > On 06/09/2017 08:56 AM, Ken Tanzer wrote: > > The extra logging would be undesirable. Is there any way to skip that > > entirely? I see with block_log_statement I could dial down the logging >

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway <m...@joeconway.com> wrote: > On 06/08/2017 10:37 PM, Ken Tanzer wrote: > > My approach was to have the initial connection made by the owner, and > > then after successfully authenticating the user, to switch to the role > &

Re: [GENERAL] Limiting DB access by role after initial connection?

As a follow up to this, a couple more questions from my wishful thinking list: 1) Any way, whether kosher or hacky, to set an arbitrary yet immutable (for the lifetime of the session) variable within a session? Something akin to DEFINE, which wouldn't allow redefinition? A temp table that

[GENERAL] Limiting DB access by role after initial connection?

Hi. As context, I'm working with an organization with a current production database. Organizations in other locations using the same service delivery model want to share this database, with some but not all of the data restricted so that people at each site can see only that site's data. I've

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > > So I can switch to Custom format for future backups. But regarding the > > existing backups I have in Tar format, is there any way to successfully > > restore them? > > FWIW, the business with making and editing a list file should work just > fine with a tar-format dump, not only with a

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > I do get the "make \d show relevant information" argument and that is one > that seems easier to solve... > Maybe I'm missing something, but I'm not sure how you'd solve this or change what \d shows for a table. Right now I get to see this in my \d: "authorized_approvers_only" CHECK

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > I can't really make this an FK. I can (and probably will) put this into a >> trigger. Although it seems like an extra layer of wrapping just to call a >> function. I'm curious if there's any conceptual reason why constraints >> couldn't (as an option) be restored after all the data is

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

> > Aside from being a bit more verbose there is nothing useful that writing > this as "CHECK function()" provides that you don't also get by writing > "CREATE TRIGGER". > I agree you get the same result. It may be a minor issue, but for me it is convenient to see the logic spelled out when

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

Thanks Adrian and David. That all makes sense, and I gather the answer regarding the existing dumps is "no, they can't be restored." So be it. Here's a couple of follow-on comments:: Ideally figure out how to write an actual FK constraint - otherwise use > triggers. I can't really make this

[GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

On 9.3.17, I tried to restore a tar from pg_dump. It failed thusly: bash-4.1$ pg_restore -d spc_test_1 agency_backup.spc.2017.06.05_10.30.01.tar pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 10608; 0 107743 TABLE DATA tbl_payment spc

Re: [GENERAL] Installing module for 9.6, not 9.2, on Centos?

> > The biggest problem is that I was out in the Sun too long today and was > not paying attention to what you posted. This part: > > PATH=/usr/local/pgsql96/bin:$PATH > > is from how I installed various versions of Postgres from source on my > machine. My guess is it does not match your setup.

Re: [GENERAL] Installing module for 9.6, not 9.2, on Centos?

On Tue, May 23, 2017 at 5:34 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/23/2017 05:27 PM, Ken Tanzer wrote: > >> >> But the install still goes to 9.2: >> >> PATH=/usr/local/pgsql96/bin/:$PATH make USE_PGXS=1 install >>

Re: [GENERAL] Installing module for 9.6, not 9.2, on Centos?

> > >> But the install still goes to 9.2: >> >> PATH=/usr/local/pgsql96/bin/:$PATH make USE_PGXS=1 install >> > > Did you do: > > PATH=/usr/local/pgsql96/bin/:$PATH make USE_PGXS=1 clean all > > I did. > Or is there some environment variable set that could be interfering? > > I didn't see

Re: [GENERAL] Installing module for 9.6, not 9.2, on Centos?

Thanks Adrian, though still no luck for me. The compiling was working OK already, the install still goes to 9.2. > Where did you get the table_log extension > >> I no longer remember (it's been a few years), but my tgz file was the same as what's on pgfoundry > I usually do something

[GENERAL] Installing module for 9.6, not 9.2, on Centos?

Hi. I recently installed 9.6 on my Centos 6.9 server, alongside a still-running 9.2. I need to install a module for 9.6 (table_log) that is installed on 9.2. I took the table_log Makefile and changed it to point to /usr/pgsql-9.6/bin/pg_config instead of 9.2. It is still installing into the 9.2

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

On Mon, May 15, 2017 at 4:45 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 05/15/2017 01:40 PM, Ken Tanzer wrote: > > > >> But let me ask, is there a big warning about this somewhere I missed? >> Can the 9.2 updates do something to fix this

Re: [GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

> > >> Workarounds: >> >> * You can connect to 9.2 using /usr/pgsql-9.2/bin/psql command. It knows >> the >> old socket directory. >> > > That was where I was going until I saw this in the OP: > > bash-4.1$ /usr/pgsql-9.2/bin/psql -p 5432 > psql: could not connect to server: Connection refused >

[GENERAL] Help: Installing 9.6 breaks local connections to 9.2 on Centos 6.9

Hi. On a Centos 6.9 server (in the cloud with Rackspace), I'm wanting to install PGDG 9.6 alongside the already-running 9.2. After installing the 9.6 packages (and even before doing an initdb), I am no longer able to make a local connection to the 9.2 server. Instead I get the message: psql:

Re: [GENERAL] referential integrity between elements of an array and another table?

For me, foreign keys for arrays would be incredibly helpful. For simple checkbox-type options, it's _much_ simpler to just put them in an array, and I work with a UI that can handle the arrays well. I do hope this makes it into Postgresql someday! In the meantime, I've mostly sacrificed the

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

Hi. I've got a recurring problem with character encoding for a Postgres-based web PHP app, and am hoping someone can clue me in or at least point me in the right direction. I'll confess upfront my understanding of encoding issues is extremely limited. Here goes. The app uses a Postgres

Re: [GENERAL] isnull() function in pgAdmin3

On Mon, Oct 3, 2016 at 6:39 AM, dudedoe01 wrote: > What is the most feasible way to emulate the below MySQL function into > postgreSQL. Since the isnull() function is no longer supported in 9.6 > version. I have tried every trick in the hat to get the desired results. >

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 4:31 PM, John R Pierce <pie...@hogranch.com> wrote: > On 9/7/2016 1:36 PM, Ken Tanzer wrote: > >> No transformation is needed, except for padding the fields out to their >> maximum lengths. >> > > without accessing metadata, how would

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 4:39 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 09/07/2016 04:25 PM, Jim Nasby wrote: > >> On 9/7/16 6:07 PM, Ken Tanzer wrote: >> >>> ERROR: PL/Python functions cannot accept type record >>> >> >> Ug

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 3:38 PM, Jim Nasby <jim.na...@bluetreble.com> wrote: > On 9/7/16 5:32 PM, Ken Tanzer wrote: > >> SELECT my_cat(ebh_gain,'ebh_gain') FROM ebh_gain; >> >> I know TCL and probably Python and others can work with a record as a >> tri

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 3:46 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 09/07/2016 03:32 PM, Ken Tanzer wrote: > >> >> >> On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>&

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 09/07/2016 01:36 PM, Ken Tanzer wrote: > >> >> >> On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>&

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 09/07/2016 01:15 PM, Ken Tanzer wrote: > >> Hi. Using version 9.2. I'm trying to create a function that will take >> a record from any view and assemble it into a string, for exp

[GENERAL] How to assemble all fields of (any) view into a string?

Hi. Using version 9.2. I'm trying to create a function that will take a record from any view and assemble it into a string, for export to another system. For example, this view: > \d ebh_gain View "public.ebh_gain" Column | Type | Modifiers

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

On Fri, Jun 10, 2016 at 2:23 PM, Ken Tanzer <ken.tan...@gmail.com> wrote: > On Fri, Jun 10, 2016 at 1:47 PM, Steve Atkins <st...@blighty.com> wrote: > >> > You could look at one of the existing SQL parsers implemented in PHP, and >> use those to parse the co

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

On Fri, Jun 10, 2016 at 1:54 PM, rob stone wrote: > > Hi Ken, > > Would this be static or dynamic? > For example, if you altered a column to become defined as NOT NULL, > say, when you build the form used to maintain that table you'd like to > have a "required" attribute

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

On Fri, Jun 10, 2016 at 1:47 PM, Steve Atkins wrote: > > You could name the check constraints, catch the errors and use a > client-side mapping between constraint name and a friendly error message > for display in the web interface. > > This seems plausible, but not ideal. I

[GENERAL] Converting Postgres SQL constraint logic to PHP?

Hi. I was hoping this list might be able to offer some help/advice/suggestions/opinions about feasibility for something I want to implement, namely converting Postgres constraints into PHP logic. Here's the context and explanation: I work on a PHP web app using Postgres. When possible, we try

Re: [GENERAL] Suppress decimal point like digits in to_char?

> > > Well, this may be a good enhancement request, add something like > d=decimal point, supressed if alone. > > Yeah. Maybe that's all that need to be said. :) > > In particular, one might reasonably choose a format string like > 'FM999,999D99' and not realize it will fail on whole numbers.

Re: [GENERAL] Suppress decimal point like digits in to_char?

avid.g.johns...@gmail.com> wrote: >> > On Sunday, March 13, 2016, Ken Tanzer <ken.tan...@gmail.com> wrote: >> >> > Typically if I'm going to format any currency amount with pennies I >> would >> > format all values, even those with zero pennies,

Re: [GENERAL] Suppress decimal point like digits in to_char?

On Mar 13, 2016 6:29 PM, "David G. Johnston" <david.g.johns...@gmail.com> wrote: > > On Sunday, March 13, 2016, Ken Tanzer <ken.tan...@gmail.com> wrote: >> >> Hi. Is there a way with to_char to suppress a decimal point, like a leading or trailing 0, so t

[GENERAL] Suppress decimal point like digits in to_char?

Hi. Is there a way with to_char to suppress a decimal point, like a leading or trailing 0, so that integers will not have them, but non-ints will? I'm hoping I'm missing something easy. Thanks. Ken SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM ( SELECT 1 AS val UNION SELECT 1.05

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 2:02 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Ken Tanzer wrote: > > > Are there any other potential solutions, pitfalls or considerations that > > come to mind? Any thoughts welcome. And as I said, if there's not a > good >

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 3:03 PM, Andrew Sullivan <a...@crankycanuck.ca> wrote: > On Thu, Sep 10, 2015 at 02:54:31PM -0700, Ken Tanzer wrote: > > Thanks, but I guess I should have been clearer. Thanks to y'all > wonderful > > mailing list folks, I get it now

[GENERAL] Very puzzling sort behavior

Hi. In a table that includes these columns: my_db=> \d tbl_client ... name_last | character varying(40) | not null name_first | character varying(30) | not null ... I am extremely puzzled by the sorting of the "CLARKE"s in this list:

Re: [GENERAL] Very puzzling sort behavior

Alright never mind, I guess I see what's going on. Thanks! Ken On Thu, Sep 10, 2015 at 12:51 PM, Ken Tanzer <ken.tan...@gmail.com> wrote: > > > On Thu, Sep 10, 2015 at 12:47 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Peter Geoghegan <peter.geoghega...@gmail

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 12:47 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Peter Geoghegan <peter.geoghega...@gmail.com> writes: > > On Thu, Sep 10, 2015 at 12:35 PM, Ken Tanzer <ken.tan...@gmail.com> > wrote: > >> Any thoughts about what's going on, what to

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 12:56 PM, Peter Geoghegan < peter.geoghega...@gmail.com> wrote: > On Thu, Sep 10, 2015 at 12:51 PM, Ken Tanzer <ken.tan...@gmail.com> wrote: > > OK, can one of you help me out in understanding this? I would have > thought that given "CLARK,

Re: [GENERAL] Count of non-null values per table column

On Fri, Aug 14, 2015 at 6:35 PM, David Nelson dnelson77...@gmail.com wrote: On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley david.row...@2ndquadrant.com writes: Tthat is the way I would do it for a table with a small number of columns, but these have

Re: [GENERAL] EXCLUDE, Gist and integers

On Wed, Jul 15, 2015 at 10:03 PM, Guillaume Lelarge guilla...@lelarge.info wrote: Hi, Le 16 juil. 2015 2:23 AM, Ken Tanzer ken.tan...@gmail.com a écrit : Hi. I'm looking into adding daterange exclusions to some of my tables. Following the documentation, I can do this no problem

[GENERAL] EXCLUDE, Gist and integers

Hi. I'm looking into adding daterange exclusions to some of my tables. Following the documentation, I can do this no problem to prevent any records from overlapping: CREATE TEMP TABLE foo ( client_id integer, start_date date NOT NULL, end_date date, EXCLUDE using gist

Re: [SQL] Re: [GENERAL] Link Office Word form document with data from PostgreSQL

On Mon, Mar 30, 2015 at 1:51 PM, avpro avpro avprowebe...@gmail.com wrote: thank you all for your responses. I think to easiest option for me would be to use the ODBC connection. Now my problem is where to start with the VBA code; but that's for another list. cheers On 30 March 2015 at

Re: [GENERAL] What is the alternate of FILTER below Postgresql 9.4 ?

I tried, but it is not giving the output exactly like *FILTER*. app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id) ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0 END AS Non_X_loc from people group by lower(location); x_loc | non_x_loc

Re: [GENERAL] Array string casts with SELECT but not SELECT DISTINCT

}'; this works INSERT 0 1 bns=# bns=# in previous mail, sorry for not mentioning varchar*[]* We did type cast implicit method to avoid application code changes for Oracle to PostgreSQL compatible Thanks Sridhar BN On Sat, Feb 21, 2015 at 9:38 AM, Ken Tanzer ken.tan

Re: [GENERAL] Array string casts with SELECT but not SELECT DISTINCT

please try this below, may be this should help CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT; just for info: actually this should be available in default On Fri, Feb 20, 2015 at 9:48 AM, Ken Tanzer ken.tan...@gmail.com wrote: Hi. Here's a boiled down example of something that caught

[GENERAL] Array string casts with SELECT but not SELECT DISTINCT

Hi. Here's a boiled down example of something that caught me by surprise: ag_reach_test= CREATE TEMP TABLE foo (my_array varchar[]); CREATE TABLE ag_reach_test= INSERT INTO foo (my_array) SELECT '{TEST}'; INSERT 0 1 ag_reach_test= SELECT my_array[1],array_length(my_array,1) FROM foo; my_array |

Re: [GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

EXPLAIN ANALYZE SELECT * FROM food WHERE food.post_timestamp = ('now'::date - interval '1 month')::date AND food.post_timestamp = 'now' ORDER BY food.post_timestamp DESC LIMIT 30; I think the problem is that you're using 'now'::date in your first example, which gets frozen. You

[GENERAL] Localhost vs. Unix Domain Sockets?

Hi. I'm working with a couple of machines that have Postgres/Apache on Linux setups. Connections to Postgres are currntly TCP/IP to localhost. (We're also using itk, so that the apache connections are per-user.) We began looking into about encrypting these connections with SSL, but now I'm

Re: [GENERAL] Localhost vs. Unix Domain Sockets?

Thanks all for the input. Sounds like there aren't downsides to sockets, and they are at least as secure. I do have on follow-up question though: * peer auth (OS user == DB user name) is typically the way to go in I used to have my db and linux usernames match, until this issue came along:

Re: [GENERAL] Localhost vs. Unix Domain Sockets?

Thanks. I'm not really worried about this particular vulnerability, just wondering about the more general idea that having db user name = os user could reduce your security, even if only slightly. Is it just as conceivable that a vulnerability could come along that was more exploitable only if

Re: [GENERAL] php password authentication failed for user ...

On Wed, Jul 9, 2014 at 5:37 AM, basti ba...@unix-solution.de wrote: #hostall all 0.0.0.0 0.0.0.0 md5 did not work. If it really starts with a # like you show it above, it's just a comment and pretty much guaranteed not to do anything. Cheers, Ken --

Re: [GENERAL] Questions about daterange() function

So here are my questions: 1) Is there anyway to control this behavior of daterange(), or is it just best to (for example) add 1 to the upper bound argument if I want an inclusive upper bound? See link for question #3; namely use the three-arg version of daterange (type,type,text)

[GENERAL] Questions about daterange() function

Hi. I've got lots of tables with start and end dates in them, and I'm trying to learn how to work with them as date ranges (which seem fantastic!). I've noticed that the daterange() function seems to create ranges with an inclusive lower bound, and an exclusive upper bound. For example: SELECT

Re: [GENERAL] Questions about daterange() function

On Wed, Jun 25, 2014 at 6:12 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 06/25/2014 05:53 PM, Ken Tanzer wrote: Hi. I've got lots of tables with start and end dates in them, and I'm trying to learn how to work with them as date ranges (which seem fantastic!). I've noticed

Re: [GENERAL] encrypting data stored in PostgreSQL

On 04/09/2014 02:52 PM, John R Pierce wrote: On 4/9/2014 1:40 PM, CS_DBA wrote: Hi All; We have a client with this requirement: At rest data must be encrypted with a unique client key Any thoughts on how to pull this off for PostgreSQL stored data? I looked at this a while ago

Re: [GENERAL] encrypting data stored in PostgreSQL

On Wed, Apr 9, 2014 at 2:32 PM, John R Pierce pie...@hogranch.com wrote: On 4/9/2014 2:16 PM, Ken Tanzer wrote: I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem

Re: [GENERAL] pg_config problems on PG9.3/Centos?

On Tue, Jan 21, 2014 at 11:49 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote: Hi, On Tue, 2014-01-21 at 18:00 -0800, Ken Tanzer wrote: It didn't seem like a great idea to me either, but what's the better alternative? Without the symlink I get lots of errors: make USE_PGXS=1 make

[GENERAL] pg_config problems on PG9.3/Centos?

Hi. I'm trying to build the table_log module for Postgres 9.3, and am wondering if there is an issue with pg_config. I installed Postgres on a fresh CentOS 6.5 with the pgdg packages: yum list installed postgres* Installed Packages postgresql93.x86_64

Re: [GENERAL] pg_config problems on PG9.3/Centos?

On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Exactly where is root's path finding pg_config? IIRC, most of the paths shown here are actually computed relative to the location of the pg_config executable, so I could imagine getting this kind of result if you'd done

Re: [GENERAL] pg_config problems on PG9.3/Centos?

On Tue, Jan 21, 2014 at 1:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: IIRC, most of the paths shown here are actually computed relative to the location of the pg_config executable, so

Re: [GENERAL] pg_config problems on PG9.3/Centos?

On Tue, Jan 21, 2014 at 3:16 PM, Devrim GÜNDÜZ dev...@gunduz.org wrote: Hi, On Tue, 2014-01-21 at 13:18 -0800, Ken Tanzer wrote: Oddly, there was a pg_config in /usr/bin that was not a symlink and not owned by any package. I'm really puzzled as to how it got there, but I removed

[GENERAL] Question(s) about crosstab

Hi. I've got a simple table unit_hold, with grant numbers, buildings and counts of unit types, which I need to summarize, along with a table listing unit types: \d unit_hold Table public.unit_hold Column| Type | Modifiers

Re: [GENERAL] Question(s) about crosstab

On Tue, Dec 17, 2013 at 3:47 PM, rob stone floripa...@gmail.com wrote: SELECT UH.grant_number_code, UH.housing_project_code, UH. count, UT.description FROM l_unit_type UT, unit_hold UH WHERE UH.unit_type_code = UT.unit_type_code; Easier to create a view. Thanks Rob, but that doesn't get

Re: [GENERAL] Question(s) about crosstab

On Tue, Dec 17, 2013 at 5:11 PM, David Johnston pol...@yahoo.com wrote: 3) Limitation of SQL - explained below: The function call string that you pass in is just that, a string, the SQL construct within which it resides has no knowledge of its contents. SQL has the hard requirement that at

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

On Sat, Nov 23, 2013 at 9:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing vik.fear...@dalibo.com wrote: Chapter 15 of our documentation handles installing from source. http://www.postgresql.org/docs/current

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing vik.fear...@dalibo.com wrote: On 11/23/2013 07:41 AM, Ken Tanzer wrote: OTOH, if there were a very clear and credible page with good instructions on installing build environment + postgres (for say RHEL, Ubuntu Fedora) that would install side

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: If you just need a work-around-it-right-now solution, I'd suggest introducing an OFFSET 0 optimization fence into one or another of the levels of view below the outer joins. I've not experimented but I think

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

schemes. Just a thought. I know all the information is out there and can be pieced together. Like many computing endeavors, I'm sure the second time would be quick and easy, but likely not so much the first! Cheers, Ken On Fri, Nov 22, 2013 at 8:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer

[GENERAL] Getting non_NULL right-side values on a non-matching join?

Hello. In doing a left join with a particular view as the right table, and non-matching join criteria, I am getting values returned in a few fields. All the rest are NULL. I would expect all the right side values to be NULL. (The view is large and messy, but it doesn't seem like that should

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner kgri...@ymail.com wrote: Ken Tanzer ken.tan...@gmail.com wrote: In doing a left join with a particular view as the right table, and non-matching join criteria, I am getting values returned in a few fields. All the rest are NULL. I would

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: Hello. In doing a left join with a particular view as the right table, and non-matching join criteria, I am getting values returned in a few fields. All the rest are NULL. I

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

,boo.client_id AS ri_id,b_field from (SELECT -1 AS client_id) foo LEFT JOIN boo_top_view boo USING (client_id); On Thu, Nov 21, 2013 at 7:49 PM, Ken Tanzer ken.tan...@gmail.com wrote: On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes

Re: [GENERAL] Help : Sum 2 tables based on key from other table

If the tables aren't huge, you're not concerned about optimization, and you just want to get your numbers, I think something like this would do the trick. I haven't actually tried it 'cause I didn't have easy access to your tables: SELECT a.product_id, a.product_name, b.initial_stock_sum,

[GENERAL] What does this error message mean?

Hi. I got an error message reported to me that I've never seen before, and I'm not quite sure what it means or what would cause it. When I re-run the query now, it runs without complaint, so the problem seems to have gone away. Which of course I don't understand either! Would be nice to know

Re: [GENERAL] What does this error message mean?

On Sun, Nov 17, 2013 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: Hi. I got an error message reported to me that I've never seen before, and I'm not quite sure what it means or what would cause it. ERROR: type of parameter 70 (text) does

Re: [GENERAL] What does this error message mean?

generate_payments(date,text,integer,integer) line 195 at assignment On Sun, Nov 17, 2013 at 7:03 PM, Ken Tanzer ken.tan...@gmail.com wrote: On Sun, Nov 17, 2013 at 6:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: Hi. I got an error message reported to me that I've never

Re: [GENERAL] What does this error message mean?

Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: But thinking about it some more, the function runs one of 5 possible queries. 4 of them select NULL as comment (no cast), while the fifth (and the one that caused this error) selects 'a string'. Ah. Fixing that so all

[GENERAL] pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)

When trying to move a database from 8.3 to 9.2.5, the plain text pg_dump did not restore without errors. (I used pg_dump from 9.2.5) The problem seems to relate to references to other schemas and the schema search paths. First, here's the error message:

Re: [GENERAL] pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)

On Mon, Oct 21, 2013 at 6:55 AM, Adrian Klaver adrian.kla...@gmail.comwrote: What was the pg_dump command you used to dump the database? pg_dump -p -h localhost -F p -U spc_ag spc_test_1005 ~/spc_test_1005_dump_with_pg_9_2_5.sql So to be clear, housing_project_from_unit was not

Re: [GENERAL] pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)

On Mon, Oct 21, 2013 at 7:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ken Tanzer ken.tan...@gmail.com writes: When trying to move a database from 8.3 to 9.2.5, the plain text pg_dump did not restore without errors. (I used pg_dump from 9.2.5) The problem seems to relate to references

Re: [GENERAL] psql swallowed my BEGIN; on reset... user beware?

you can control this with on error stop directive -- add it to your psqlrc if you want the setting to persist. I hear what you're saying, but wonder about the implications: It sounds like you can't trust your BEGIN to actually start a transaction unless on_error_stop is set The default for

[GENERAL] psql swallowed my BEGIN; on reset... user beware?

After restarting the server in another window, I was surprised that my command did not run in a transaction: spc_test_scratch=# BEGIN; DROP VIEW IF EXISTS ptest_mip ; DROP VIEW rent_info; \i create.view.rent_info.sql FATAL: terminating connection due to administrator command server closed the

Re: [GENERAL] Why does this array query fail?

Is this a quarterly report because that is how long it takes to run? It takes about 7 seconds to run. I suppose if I optimized it I could save a minute every couple of years. I usually get concerned about performance issues when they're actually causing problems. I'm generally more

Re: [GENERAL] Why does this array query fail?

Can we please follow list norms (or at least my example since I was the first to respond) and bottom-post. Absolutely. Gmail did it without my realizing, but my bad and I'm all for following the list conventions. Thanks for taking the time to explain this stuff, which I appreciate. Mostly

  1   2   >