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 mailing

[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 ( https://www.postgre

[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 INT

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, and look through them to

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 would allow the > re

[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 at

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 t

[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 a,b,c,row_

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

On Sun, Jun 11, 2017 at 12:15 PM, Bruno Wolff III wrote: > On Fri, Jun 09, 2017 at 21:14:15 -0700, > Ken Tanzer wrote: > >> On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III wrote: >> >> Seems to me they are separate issues. App currently has access to the >&

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

On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III wrote: > On Thu, Jun 08, 2017 at 22:37:34 -0700, > 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 t

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 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 > > after switching users, b

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

On Fri, Jun 9, 2017 at 6:42 AM, Joe Conway 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 > > of the site they belong to.

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 couldn

[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 be

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 cust

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 (approve

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 loaded

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 using

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 an

[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 pg_

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. Yo

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

On Tue, May 23, 2017 at 5:34 PM, Adrian Klaver 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 >> >> >> Did you do: &

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 anythin

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 like(usi

[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 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, or at least create a warning >> or an R

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: co

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 refe

[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 database

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. > Still 'RPG INV' doesn't sh

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 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 you know what those maximum le

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

On Wed, Sep 7, 2016 at 4:39 PM, Adrian Klaver 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 >>> >> >> Ugh, yeah... that won't work. p

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

On Wed, Sep 7, 2016 at 3:38 PM, Jim Nasby 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 >> trigger function. But T

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

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

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

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

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

On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver 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 export to >> another sy

[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 wrote: > On Fri, Jun 10, 2016 at 1:47 PM, Steve Atkins wrote: > >> > You could look at one of the existing SQL parsers implemented in PHP, and >> use those to parse the constraint to a tree from which you could easily >>

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 against the input field f

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 could get over the a

[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 t

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. Is

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

On Mon, Mar 14, 2016 at 8:22 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Mar 14, 2016 at 3:31 AM, Francisco Olarte > wrote: > >> Hi; >> >> On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston >> wrote: >> &

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

On Mar 13, 2016 6:29 PM, "David G. Johnston" wrote: > > On Sunday, March 13, 2016, Ken Tanzer wrote: >> >> 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

[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 AS

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 3:03 PM, Andrew Sullivan 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 as to why the two sorts are not the

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 2:02 PM, Alvaro Herrera 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 > > way to do this I'll

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 wrote: > > OK, can one of you help me out in understanding this? I would have > thought that given "CLARK," and "CLARKE&q

Re: [GENERAL] Very puzzling sort behavior

On Thu, Sep 10, 2015 at 12:47 PM, Tom Lane wrote: > Peter Geoghegan writes: > > On Thu, Sep 10, 2015 at 12:35 PM, Ken Tanzer > wrote: > >> Any thoughts about what's going on, what to do about it, or what > obvious point I missing? Thanks in advance! > > >

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 wrote: > > > On Thu, Sep 10, 2015 at 12:47 PM, Tom Lane wrote: > >> Peter Geoghegan writes: >> > On Thu, Sep 10, 2015 at 12:35 PM, Ken Tanzer &g

[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: my_db=

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

On Fri, Aug 14, 2015 at 6:35 PM, David Nelson wrote: > On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane wrote: > > > > David Rowley writes: > > Tthat is the way I would do it for a table with a small number of columns, > but these have several dozen so this would get tedious. Although I just > real

Re: [GENERAL] EXCLUDE, Gist and integers

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

[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 (daterange(start_dat

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

On Mon, Mar 30, 2015 at 1:51 PM, avpro avpro 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 19:51, Vincent Veyr

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

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

ntioning 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 wrote: > >> I'm not able to run thi

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

is of type character varying[] but expression > is of type text > > 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, 2

[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 froz

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 th

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: h

[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 thin

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

On Wed, Jul 9, 2014 at 5:37 AM, basti 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 -- AGENCY Software A

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 > (typ

Re: [GENERAL] Questions about daterange() function

On Wed, Jun 25, 2014 at 6:12 PM, Adrian Klaver 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!)

[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] encrypting data stored in PostgreSQL

On Wed, Apr 9, 2014 at 2:32 PM, John R Pierce 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 encrypte

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 whi

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

On Tue, Jan 21, 2014 at 11:49 PM, Devrim GÜNDÜZ 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: > >

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

On Tue, Jan 21, 2014 at 3:16 PM, Devrim GÜNDÜZ 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

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

On Tue, Jan 21, 2014 at 1:45 PM, Tom Lane wrote: > Ken Tanzer writes: > > On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane wrote: > >> IIRC, most of the paths shown here are actually computed relative to the > >> location of the pg_config executable, so I could imagin

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

On Tue, Jan 21, 2014 at 12:32 PM, Tom Lane 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 something like

[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 9.3.2-1P

Re: [GENERAL] Question(s) about crosstab

On Tue, Dec 17, 2013 at 5:11 PM, David Johnston 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 the time yo

Re: [GENERAL] Question(s) about crosstab

On Tue, Dec 17, 2013 at 3:47 PM, rob stone 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 the data in

[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] Getting non_NULL right-side values on a non-matching join?

On Sat, Nov 23, 2013 at 9:21 AM, Tom Lane wrote: > Ken Tanzer writes: > > On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing > wrote: > >> Chapter 15 of our documentation handles installing from source. > >> http://www.postgresql.org/docs/current/static/installation.

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

On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing 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 s

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

rsions more easily than other packaging 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 a

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

On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane 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 > > that ought

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

AS foo_id,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 wrote: > On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane wrote: > > Ken Tanzer writes: >> > Hello. In doing a

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

On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane wrote: > Ken Tanzer 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 would e

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

On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner wrote: > Ken Tanzer 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 expec

[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 mat

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,

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

59 PM, Tom Lane wrote: > Ken Tanzer 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'. > >

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

x27;,'ASSIST','3852',sys_user()); ERROR: type of parameter 70 (unknown) does not match that when preparing the plan (text) CONTEXT: PL/pgSQL function generate_payments(date,text,integer,integer) line 195 at assignment On Sun, Nov 17, 2013 at 7:03 PM, Ken Tanzer wrote: > > On Sun, No

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

On Sun, Nov 17, 2013 at 6:20 PM, Tom Lane wrote: > Ken Tanzer 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) doe

[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 fo

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 wrote: > Ken Tanzer 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 to other s

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 wrote: > 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 restored at all unless >

[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: psql:/tmp/spc_test_1005_dump_with_pg_9_2_

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 fo

[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 con

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 concern

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

ERE service_date BETWEEN '2013-01-01' AND '2013-03-31' ORDER BY client_id,service_date DESC) AS sr USING (client_id) LEFT JOIN (SELECT client_id,export_id FROM client_export_id WHERE export_organization_code='HCH') exp USING (client_id) WHERE client_id IN (SELECT c

  1   2   >