Re: [GENERAL] pg on Debian servers

On Sat, 2017-11-11 at 14:30 +0100, Magnus Hagander wrote: > > > > The init.d script is not used with systemd. > > > Hello Magnus, Many months ago on a bog standard Debian set-up did a re-boot and ended up with postmasters running for 9.2, 9.4, 9.5 and 9.6 all started one after the other.

Re: [GENERAL] pg on Debian servers

On Sat, 2017-11-11 at 13:03 +, Mark Morgan Lloyd wrote: > Apologies for something which is distro related, but I was bitten by > a > "silly mistake"- one of my own, I hasten to say- earlier. > > Several legacy programs written in Delphi ground to a halt this > morning, > which turned out

Re: [GENERAL] Adding 'serial' to existing column

On Fri, 2017-11-03 at 16:47 -0400, Tom Lane wrote: > Robert Lakes writes: > > I am new to Postgres and I am trying to build this SQL statement in > > my SQL > > script: > > ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT > > nextval('tab_id_seq'); > > That looks

Re: [GENERAL] Postgres 9.6 fails to start on VMWare

On Sun, 2017-10-22 at 15:13 +0100, Martin Moore wrote: > 2017-10-22 14:08:28 UTC [2479-1] LOG: 0: database system > shutdown was interrupted; last known up at 2017-10-22 14:07:20 UTC There is something missing here. Last shutdown at 2017-10-22 14:07:20 UTC on which server? Then attempting

Re: [GENERAL] Problems with the time in data type timestamp without time zone

On Thu, 2017-10-19 at 10:28 -0400, américo bravo astroña wrote: > We are using two different programs within the same computer, a > program saves the data with date and time in the DB and we are doing > another program to retrieve that information with date and time to > generate reports, all

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

On Sat, 2017-09-16 at 19:18 +0200, Rafal Pietrak wrote: > Dear robjsarg...@gmail.com, > > > I do have 17 "process tables" ... they are "class-B" tables, they > DONT > need any hierarchy. One of them contain payment details and has FK do > a > document (in one of the 12 tables of "class-A",

Re: [GENERAL] How to add new Collation language

On Thu, 2017-09-14 at 11:30 +, Rob Northcott wrote: > How can I add a collation language to a Postgres server? > Specifically, I want to create a new database with collation of > English_United Kingdom.1252 but the only options are C, Posix and > United States. > Even if I select United

Re: [GENERAL] pg_ident mapping Kerberos Usernames

> > > > Hi Rob, > > How would that work? I was under the impression the first column was > for socket type and limited to > local, host, hostssl, and hostnossl? > > Thunderbird's config has been fixed, so here is the line from > pg_hba.conf line without the > formatting issues: > > host

Re: [GENERAL] pg_ident mapping Kerberos Usernames

On Sat, 2017-09-09 at 20:44 -0500, techmail+pg...@dangertoaster.com wrote: > Hi, > > I'm trying to get pg_ident to map "user1" and "us...@a.domain.tld" > to > "user1" in postgres, or vice versa. I'm not picky about which way > works. > > Kerberos authentication works. I've gotten "user1" to

Re: [GENERAL] Function not inserting rows

Hello, On Wed, 2017-08-23 at 17:23 +0200, Frank Foerster wrote: > > > > But the created statement looks syntax-wise identical to the pgadmin- > statement (except for the forced error of course): > > select * from api_dev.add_texts_to_item( 444, array['PGADM1', > 'PGADM2'] ); > > > I don't

Re: [GENERAL] sequence used on null value or get the max value for a column whith concurrency

On Thu, 2017-08-10 at 19:05 -0300, marcelo wrote: > In some table, I have a bigint column which at the app level can be  > null. Call it "DocumentNumber", and of course is not the PK. > In most cases, the applications give some value to the column. > > But sometimes, the value remains null,

Re: [GENERAL] invalid byte sequence for encoding

On Wed, 2017-08-09 at 14:21 +0200, basti wrote: > Hello, > i have a webapp convert from ascii to uft8. > > Now I get in postgres > > ERROR:  invalid byte sequence for encoding "UTF8": 0xfc > > Now I try to log all queries with log_statement = 'all'. > All queries are longed expected this one.

Re: [GENERAL] Developer GUI tools for PostgreSQL

On Wed, 2017-07-26 at 18:31 -0700, Joshua D. Drake wrote: > On 07/26/2017 05:58 PM, Tim Uckun wrote: > > I am waiting till pgadmin4 gets a bit faster. It seems kind of slow > > on  > > my mac. > > PostgreSQL Studio > Navicat > EMS PostgreSQL Manager > PgAdmin3 LTS (BigSQL) > > JD > > If

Re: [GENERAL] How to get transaction started always in WRITE mode.

On Tue, 2017-07-25 at 11:32 +0200, Adam Šlachta wrote: > Hello, >   > In short: Is there any way how to setup PostgreSql 9.6 to always > start a transaction in WRITE mode? >   > Our related configuration: > "default_transaction_isolation" --> "read committed" > "default_transaction_read_only"

Re: [GENERAL] Backward compatibility

On Sat, 2017-07-22 at 10:49 -0400, Tom Lane wrote: > Igor Korot writes: > > But it works incorrectly - it should return: > > 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 > > 20161221 > > (Red Hat 6.3.1-1), 64-bit > > i.e. without the word "PosgreSQL", since

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

Hi On Sat, 2017-06-10 at 09:17 -0700, Steven Grimm wrote: > On Sat, Jun 10, 2017 at 1:46 AM, Alban Hertroys > wrote: > > I notice that you're declaring your ResultSet variable inside the > loop, which means that you create and destroy it frequently. I've > been told that this

Re: [GENERAL] SELECT statement with sub-queries

Hello, On Sun, 2017-05-28 at 20:03 +0300, Michelle Konzack wrote: > Hello *, > > I try to get columns from my database with a singel SELECT, but I > stuck. > > I have 3 tables like: > > > 1)  categories (serial,cat) > > 2)  manufacturers (serial,m_name) > > 3)  products

Re: [GENERAL] Unable to connect to Postgresql

Hello John, On Mon, 2017-04-10 at 16:23 -0400, John Iliffe wrote: > On Monday 10 April 2017 11:53:35 Daniel Verite wrote: > >   John Iliffe wrote: > > > So, the problem is resolved, although I have no idea why it was > > > necessary. > >  > Good to hear. > >  > > Yes, I will do that, but

Re: [GENERAL] Unable to connect to Postgresql

On Sun, 2017-04-09 at 20:09 -0400, John Iliffe wrote: > > >  > > You have Apache, PHP, and Postgres all running on your LAN at > > 192.168.1.6. > >  > >  > > You are on 192.168.1.10. > >  > > Your NOT doing "php -f testfcgi.php", so how does Apache "know" to > run > > that script? > >

Re: [GENERAL] Unable to connect to Postgresql

Hello John, Just saw this message. > > Still set to the default: > > #listen_addresses = 'localhost' # what IP address(es) to > listen on; >     # comma-separated list of  > addresses; >     # defaults to

Re: [GENERAL] Unable to connect to Postgresql

Hello John, > >  > > > PHP and Postgresql are both running on same box.  It does have > two > > > interfaces, 192.168.1.6 and 192.168.1.7, and the test programme > is > > > available on either.  The reference to 192.168.1.10 is the client > > > machine, in this case my workstation, which is

Re: [GENERAL] A change in the Debian install

Hello Adrian, On Thu, 2017-04-06 at 21:24 -0700, Adrian Klaver wrote: > On 04/06/2017 08:01 PM, rob stone wrote: > > > > > > > That is the default location and is generally the case in a source  > install. Package maintainers can and do often put them elsewhere. &

Re: [GENERAL] A change in the Debian install

On Thu, 2017-04-06 at 15:58 -0700, Adrian Klaver wrote: > On 04/06/2017 03:16 PM, rob stone wrote: > > > > > > Which is what has me confused. If you are using the postgresql- > common  > system then the *.conf files should be in  > /etc/postgresql/version/clu

Re: [GENERAL] A change in the Debian install

On Wed, 2017-04-05 at 21:14 -0700, Adrian Klaver wrote: > > What repos are you using, the Debian or the PGDG one? > > I guess the question I should really ask is, are you using a repo or  > some other method to upgrade? > > Upgraded from standard Debian repos. Nothing else was changed

[GENERAL] A change in the Debian install

Hello, Postgres is started via pg_ctl and NOT systemd. Below are the log entries when version 9.6.2-1 is started. 2017-04-04 07:15:27 AESTLOG:  database system was shut down at 2017-04- 03 13:08:27 AEST 2017-04-04 07:15:28 AESTLOG:  MultiXact member wraparound protections are now enabled

Re: [GENERAL] How to define the limit length for numeric type?

Hello, On Sat, 2017-03-11 at 22:14 -0800, vod vos wrote: > Hi everyone, > > How to define the exact limit length of numeric type? For example,  > > CREATE TABLE test  (id serial, goose numeric(4,1)); > > 300.2 and 30.2 can be inserted into COLUMN goose, but I want 30.2 or > 3.2 can not be

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

Hi Ken, On Tue, 2017-03-07 at 15:20 -0800, Ken Tanzer wrote: > 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

Re: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

Hi Alex, On Thu, 2017-03-02 at 21:45 +0100, Alexander Farber wrote: > Good evening! > > I am calling this stored function -  > > CREATE OR REPLACE FUNCTION words_get_board( >                 in_gid integer >         ) RETURNS TABLE ( >                 out_bid integer, >                

Re: [GENERAL] Fwd: Query parameter types not recognized

Hello Roberto, On Fri, 2017-02-10 at 16:43 -0500, Roberto Balarezo wrote: > Hi Rob, > > Thanks for your answer. The query is just an example I made to > illustrate the problem. In the database I'm working with, duedate is > a timestamp without timezone column, which can contain null values. > The

Re: [GENERAL] Fwd: Query parameter types not recognized

Hello Roberto, On Fri, 2017-02-10 at 10:17 -0500, Roberto Balarezo wrote: > Hi, I would like to know why this is happening and some advice if > there is a way to solve this problem: > > I have a query like this: > > select COALESCE(duedate, ? + 1) from invoices order by duedate desc > limit 10;

Re: [GENERAL] Recovery Assistance

Hello Brian, On Sun, 2017-01-29 at 07:16 +1100, Brian Mills wrote: > Hi, > > No, it hasn't changed since the first time I looked at it.  > > root@atlassian:/home/tbadmin# ps ax | grep post >  1364 ?        Ss     0:00 /usr/lib/postfix/master >  5198 pts/3    S      0:00 su postgres >  5221 pts/3

Re: [GENERAL] Ajuda com definição

On Tue, 2017-01-24 at 17:35 -0200, Márcio A. Sepp wrote: > Boa tarde, > > > > Tenho um caso onde o campo chave da tabela irá receber dois tipos de > informação: integer de tamanho 5 e integer de tamanho 7. > O problema disso é que se eu criar o campo como sendo integer, lá > pelas > tantas

Re: [GENERAL] COPY: row is too big

Hello, On Wed, 2017-01-04 at 07:11 -0800, Adrian Klaver wrote: > On 01/04/2017 06:54 AM, Pavel Stehule wrote: > > Hi > > > > 2017-01-04 14:00 GMT+01:00 vod vos > >: > > > > __ > > Now I am confused about I can create 1100 columns in a table in >

Re: [GENERAL] [OT] Postgresql and PHP

Hello, On Fri, 2016-12-23 at 16:44 +0100, Alessandro Baggi wrote: > Hi list, > sorry for this OT. > > I have a table on postgresql like this: > > id serial not null, > srcaddr varchar(16) not null > > I use this table to store ip address. I've used also inet type but  > changed to see if this

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

On Wed, 2016-12-14 at 17:00 -0700, David G. Johnston wrote: > On Wed, Dec 14, 2016 at 4:49 PM, Patrick B > wrote: > > ERROR:  function logextract(integer, integer) does not exist > > LINE 1: select logextract(20160901,20161001); > > > > So change the constants you are

Re: [GENERAL] No select privileges when not connecting from login postgres

On Wed, 2016-11-30 at 20:48 -0800, Joseph Brenner wrote: > I'm trying to get a new build of 9.6.1 working on a machine > running Debian stable (jessie) and I'm seeing some odd > behavior where things work correctly if I run psql when > logged in as postgres, but if I run it as user 'doom' (my >

Re: [GENERAL] Using UPDATE ... RETURNING in a custom SQL function, which RETURNS integer

> And finally here is the failing usage of the function : > > words=> SELECT uid FROM words_unban_user(1); > ERROR:  column "uid" does not exist > LINE 1: SELECT uid FROM words_unban_user(1); >    ^ > > Shouldn't you be doing:- SELECT words_unban_user(1); -- Sent via

Re: [GENERAL] About the MONEY type

My two cents . . . On Wed, 2016-11-30 at 13:35 -0800, John R Pierce wrote: > On 11/30/2016 12:16 PM, John McKown wrote: > > Speaking generically, I guess maybe MONEY needs to be somewhat > > like a TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can contain > > a TIMEZONE. I guess a MONEY type

Re: [GENERAL] "Fuzzy" Matches on Nicknames

Hello Michael, On Tue, 2016-11-29 at 19:10 -0500, Michael Sheaver wrote: > Greetings, > > I have two tables that are populated using large datasets from > disparate external systems, and I am trying to match records by > customer name between these two tables. I do not have any > authoritative

Re: [GENERAL] query locks up when run concurrently

On Thu, 2016-11-24 at 22:23 +0100, azhwkd wrote: > It should not be possible because a group does not return to the > update pool before the update hasn't finished. > I watched the queries in a postgres client and there was no overlap I > could see. > I don't really know what to make from this

Re: [GENERAL] help with moving tablespace

> Bonus question: I found an ER diagram of some of the pg_* tables at h > ttp://www.slideshare.net/oddbjorn/Get-to-know-PostgreSQL. Is there an > ERD of all of them so a person can better understand how to use them > when one must? I suppose the same question applies to > information_schema since

Re: [GENERAL] Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16)

On Thu, 2016-11-10 at 05:34 -0500, Juliano wrote: > Hi, >   > I'm trying to import some data from a MS SQL Server 2014 sequential > database to Postgresql using dbi-link. >   > Postgresql 9.6 encoding is utf-8 and does not support utf-16 but, I > need to transfer this data to postgres. >   > I

Re: [GENERAL] Locking question

On Wed, 2016-10-26 at 12:18 +0200, Frank Millman wrote: >   > From: hubert depesz lubaczewski > Sent: Wednesday, October 26, 2016 10:46 AM > To: Frank Millman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Locking question >   > On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman

Re: [GENERAL] Strange? BETWEEN behaviour.

On Thu, 2016-10-20 at 13:51 +0200, Bjørn T Johansen wrote: > I have the following SQL: > > SELECT * from table WHERE date BETWEEN to_timestamp('20.10.2016 > 00:00:00','DD.MM. HH24:MI:SS') AND to_timestamp('20.10.2016 > 23:59:59','DD.MM. > HH24:MI:SS') > > date is of type timestamp. > >

Re: [GENERAL] Question on replace function

On Sun, 2016-09-25 at 10:29 +0200, Charles Clavadetscher wrote: > Hello > > I am using PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by > gcc  > (Debian 4.7.2-5) 4.7.2, 64-bit > > I imported data from a MariaDB table into PostgreSQL and noticed > that  > the content of a field was not

Re: [GENERAL] Passing varchar parameter to INTERVAL

Hello, On Wed, 2016-09-07 at 15:05 +0200, Alexander Farber wrote: > Good afternoon, > > when trying to create a custom function to temporary ban a user: > > CREATE OR REPLACE FUNCTION words_ban_user( >     IN in_uid integer, >     IN in_until varchar,    -- '1 week' OR '1 month' OR '1

Re: [GENERAL] pgadmin4 rc1 query tool performance

Hello, On Tue, 2016-09-06 at 18:40 +0200, Attila Soki wrote: > > > > Am 06.09.2016 um 15:23 schrieb Adrian Klaver > .com>: > > > > On 09/06/2016 02:35 AM, Attila Soki wrote: > > > > > > Hi, > > > > > > i testing the latest release of pgadmin4 (rc1) and noticed that > >

Re: [GENERAL] Restricted access on DataBases

Hello, On Mon, 2016-09-05 at 14:45 +0200, Durumdara wrote: > Dear PG-masters! > > We want to put more databases to one server, to "public" schema: > DB_A, DB_B, DB_C. > And users: > US_A, US_B, US_C, and Main_Admin. > We want to setup the environment. > Every simple user can access his database:

Re: [GENERAL] COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID

On Thu, 2016-09-01 at 12:28 +0300, Michelle Konzack wrote: > Hello to all, > > after a period of silence from Debian, Courier, PHP and PostgreSQL > I  am > half back and running into a problem...  :-/ > > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also

Re: [GENERAL] endash not a graphic character?

Hello Bruno, On Sat, 2016-08-20 at 14:04 -0500, Bruno Wolff III wrote: > I was surprised to find endash and emdash were not graphic characters > in  > en_US. I'm not sure if this is correct behavior, a bug in postgres or > a  > bug in my OS' collation definitions? > > For example: > > Dash: >

Re: [GENERAL] 9.2 to 9.5 pg_upgrade losing data

Hello, On Mon, 2016-08-15 at 08:45 -0700, Adrian Klaver wrote: > On 08/15/2016 08:04 AM, Pete Fuller wrote: > > > > We have not found any obvious differences, other than the size of > > the > > databases (~ 100 gig on the testing site vs 400 Gig in > > production).  In > > fact, the upgrade path

Re: [GENERAL] permissions PostgreSQL 9.5

On Wed, 2016-08-10 at 13:33 +1200, Patrick B wrote: > hi guys, > > > just setting up a new DB using PostgreSQL 9.5. > > > I've created a new username for the code, called codeuser. > > To give the username access to all the tables, views, etc I ran: > > > GRANT INSERT, SELECT, UPDATE,

Re: [GENERAL] 9.6beta3

On Fri, 2016-07-29 at 12:15 -0400, Tom Lane wrote: > rob stone <floripa...@gmail.com> 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

[GENERAL] 9.6beta3

Hi, Some weeks ago I downloaded 9.6beta1 source, compiled it and used initdb to create a cluster, then psql to create roles, schema, etc., etc. I loaded some data into tables using COPY FROM. Today I downloaded the 9.6beta3 source, compiled it and tried to start postgres using pg_ctl but pulled

Re: [GENERAL] Is it possible to control the location of the lock file when starting postgres?

On Thu, 2016-07-21 at 00:12 +, Steve Langlois wrote: > > > On Jul 20, 2016, at 7:48 PM, Steve Langlois > om> wrote: > > > > > you never did answer my previous question, why are you messing > > > about with manually starting postgres from the wrong user > > >

Re: [GENERAL] Moving from PHP to Java: A result was returned when none was expected.

On Wed, 2016-06-15 at 15:56 +0200, Alexander Farber wrote: > Good afternoon, > > at PostgreSQL 9.5.3 I have a stored function (full source code below) > returning void, which I successfully call with PHP: > > function skipGame($dbh, $uid, $gid) { >     $sth = $dbh->prepare('SELECT

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

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

On Fri, 2016-06-10 at 13:01 -0700, Ken Tanzer wrote: > 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: >

Re: [GENERAL] Automate copy - Postgres 9.2

On Wed, 2016-06-08 at 23:50 -0400, David G. Johnston wrote: > > POn Wednesday, June 8, 2016, Patrick B > wrote: > > > ERROR:  syntax error at or near "||" > > > LINE 12:     TO '/var/lib/pgsql/' || crtrow.account_id |... > > > > Which tells me you cannot use an

Re: [GENERAL] Partitioned tables do not return affected row counts to client

On Thu, 2016-06-02 at 11:01 -0700, rverghese wrote: > We are looking to move from one large table to partitioned tables. > Since the > inserts and updates are made to the master table and then inserted > into the > appropriate partitioned table based on the trigger rules, the > affected_rows >

Re: [GENERAL] Debian and Postgres

Hello Adrian,On Thu, 2016-05-05 at 13:47 -0700, Adrian Klaver wrote: >  > Exactly. Showing the list the error you get when you cannot connect > help  > may with solving that problem and save you a great of time. What > have  > you got to lose? > I have nothing to "lose". There is NO error, per

Re: [GENERAL] Debian and Postgres

Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote: > On 5/4/2016 1:55 PM, rob stone wrote: > > I can connect via psql and issue queries without any problems. > > Trying > > to connect via JDBC fails. Trying to connect by an application > > fails. >

Re: [GENERAL] Debian and Postgres

Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote: >  > So Debian does not rotate the logs into history.log..gz? > Yes, it does! Didn't realise it. You learn something every day. 23 packages removed and 31 purged. Going thru the list slowly. Thanks, Rob -- Sent via

Re: [GENERAL] Debian and Postgres

On Wed, 2016-05-04 at 16:50 -0700, Adrian Klaver wrote: > On 05/04/2016 04:38 PM, rob stone wrote: > > > >  > Hmm, it is Ubuntu not Debian, but: > > sudo apt-get remove whois > > vi /var/log/dpkg.log > > 2016-05-04 16:42:39 status installed whois:amd6

Re: [GENERAL] Debian and Postgres

On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote: >  > > I can connect via psql and issue queries without any problems. > > Trying > > to connect via JDBC fails. Trying to connect by an application > > fails. > Are you using the same connection parameters? > > In particular are you using

[GENERAL] Debian and Postgres

Hello, This is a Debian problem that has caused a problem starting Postgres. So, I'm posting it here in the hope that somebody has experienced it previously and can assist me in fixing it. Yesterday, it started up as per normal and first command issued via JDBC driver was run:- 2016-05-04

Re: [GENERAL] Trying to understand page structures in PG

On Wed, 2016-04-06 at 10:33 +, Albe Laurenz wrote: > Every row has two system columns associated with it: xmin and xmax > > xmin is the transaction ID that created the row, while xmax is > the transaction ID that removed the row. > > So when an update takes place, xmax of the original row

Re: [GENERAL] More correlated (?) index woes

On Mon, 2016-03-28 at 20:23 +0100, Geoff Winkless wrote: > So I accept that when using MIN(sc_id) against scdate it makes > statistical sense to use the sc_id index for a reasonable percentage > of the full range of scdate, unless we know in advance that scdate is > closely correlated to sc_id

Re: [GENERAL] spurious /dev/shm related errors on insert

On Fri, 2016-03-18 at 10:45 -0400, Michael Charnoky wrote: > I should have noted: the application is using PostgreSQL 9.5.0, > running on Ubuntu 14.04 > > Mike > > On Fri, Mar 18, 2016 at 10:41 AM, Michael Charnoky > wrote: > > Hi, I'm seeing random errors from an application

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

On Thu, 2016-03-10 at 07:09 -0500, Adam Brusselback wrote: > Rob, > I understand that if I were to replicate the logic in that view for > every use case I had for those totals, this would not be an issue. > But that would very much complicate some of my queries to the point > of absurdity if I

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

On Wed, 2016-03-09 at 12:25 -0500, Adam Brusselback wrote: > I responded yesterday, but it seems to have gotten caught up because > it was too big with the attachments... Here it is again. > > Sorry about not posting correctly, hopefully I did it right this > time. > > So I wanted to see if Sql

Re: [GENERAL] Inserting JSON via Java PreparedStatment

On Mon, 2016-03-07 at 09:32 -0700, David G. Johnston wrote: > > >  > > Hint: You will need to rewrite or cast the expression. > > > Take the hint, literally.  You never did show the SQL but usually the > least complex way to solve this is to indeed transfer the data as a > string/text and then

Re: [GENERAL] Case and accent insensitive

On Tue, 2016-01-26 at 11:06 +, Max wrote: > Is there a collation for case and accent insensitive filter? I just > found the use of operators like ILIKE ou CITEXT module but I can't > use ILIKE in the queries and the CITEXT is a field-by-field solution > for the case insensitive part of the

Re: [GENERAL] A motion

On Sun, 2016-01-24 at 17:27 -0500, Dane Foster wrote: > > On Sun, Jan 24, 2016 at 4:00 PM, bret_stern ment.com> wrote: > > Adrian, > > I hope you reconsider. You have far more value to the list. > > The CoC dictators will flame out, then where will we be. > > Just sit

Re: [GENERAL] PostgreSQL GUI tools

On Sun, 2016-01-03 at 03:15 -0700, subhan alimy wrote: > Hello everyone, > > Is there any PostgreSQL GUI tools to pull the tables structure along > with > the relationships? > > > Much appropriated.   > > If you are a Java shop you can use ExecuteQuery from www.executequery.o rg You need to

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

On Sun, 2015-12-27 at 15:39 -0600, Wells Oliver wrote: > I then have four different views which do largely the same thing, > totaling the days four different ways: > > 1) by last 10 > 2) by the last 30 > 3) by the last 60 > 4) by the entire year > > Each of these views is basically a copy of one

Re: [GENERAL] Permissions, "soft read failure" - wishful thinking?

On Mon, 2015-12-14 at 09:55 -0800, Benjamin Smith wrote: > Is there a way to set PG field-level read permissions so that a deny > doesn't > cause the query to bomb, but the fields for which permission is > denied to be > nullified? > > In our web-based app, we have a request to implement

Re: [GENERAL] JDBC and inet type

On Fri, 2015-12-04 at 09:41 +, Tim Smith wrote: > Hi, > > When I use "preparedStatement.setString(5,ip);" to send values to a > stored function, it obviously gets sent to postgres as "character > varying". > > Postgres obviously complains loudly and says " Hint: No function > matches the

Re: [GENERAL] AccessExclusiveLock on tuple?

On Wed, 2015-12-02 at 10:47 -0800, Christophe Pettus wrote: > On Dec 2, 2015, at 10:29 AM, Tom Lane wrote: > > The short answer is that heavyweight tuple locks can be taken > > internally > > by UPDATE, DELETE, and other row-level operations, and the > > specifics of > > which

Re: [GENERAL] Multiple word synonyms (maybe?)

On Tue, 2015-10-20 at 19:35 +0900, Tim van der Linden wrote: > Hi All > > I have a question regarding PostgreSQL's full text capabilities and > (presumably) the synonym dictionary. > > I'm currently implementing FTS on a medical themed setup which uses > domain specific jargon to denote a bunch

Re: [GENERAL] PSQL Tools

On Sun, 2015-10-18 at 14:48 +, jwienc...@comcast.net wrote: > > Hello > > I am looking for a tool like TOAD for DBA's.   I would like something > that can generate/extract DDL; report manage tablespace, create > explain plans, create/mange users, synonyms, triggers, etc. > > > regards > >

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

> ETO::0::LOG: 0: connection received: host=127.0.0.1 > port=1083 > ETO::0::LOCATION: BackendInitialize, > src\backend\postmaster\postmaster.c:3850 > ETO::0::LOG: 0: connection authorized: user=its-eto_pg36 > database=eto_sql_db > ETO::0::LOCATION:

Re: [GENERAL] BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?

On Sat, 2015-10-03 at 08:18 -0400, Steve Petrie, P.Eng. wrote: > Greetings To : > > I am migrating a PHP web application to use PostgreSQL instead of > MySQL. > > And I have never used PostgreSQL before. > > I have two questions about function

Re: [GENERAL] PostgreSQL Developer Best Practices

On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote: I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understanding the point. So for

Re: [GENERAL] Using the database to validate data

On Thu, 2015-07-23 at 15:34 -0700, JPLapham wrote: Tim Clarke wrote Shouldn't be too difficult to import those new rows into one table, write a procedure that inserts them into the real table one by one and logs the validation failure if any - committing good rows and rolling back

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`, I

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] 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 teaching the

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/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin

[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

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/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib

Re: Fwd: [GENERAL] partial on-delete set null constraint

On Sun, 2015-01-25 at 14:09 +0100, Alban Hertroys wrote: The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says: rule 3: systematic treatment of null values; hmmm this is a little broader then support for null. I would think, that: 1. if a

Re: [GENERAL] How to insert into 2 tables from a view?

On Tue, 2014-12-23 at 15:00 -0500, Chris Hoover wrote: Sorry, in my haste to get the example out, a couple of typo's where in the sql. Correct sql: BEGIN; CREATE TABLE table1 ( table1_id SERIAL PRIMARY KEY, table1_field1 TEXT ); CREATE TABLE table2 ( table1_id

Re: [GENERAL] i386 postgres on i686 debian with multiarch

On Sun, 2014-11-30 at 15:17 +0400, Damian Dimmich wrote: Hi, I'm trying to set up a replica for a postgres 9.3 instance running on an older i386 debian install, replicating to a 64 bit install. Having enabled multi-arch** support on the 64 bit debian by running: dpkg

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

On Thu, 2014-11-27 at 06:49 -0500, Bill Moran wrote: On Thu, 27 Nov 2014 15:07:49 +1100 Sanjaya Vithanagama svithanag...@gmail.com wrote: On Wed, Nov 26, 2014 at 11:47 AM, Bill Moran wmo...@potentialtech.com wrote: On Wed, 26 Nov 2014 10:41:56 +1100 Sanjaya Vithanagama

Re: [GENERAL] Referencing serial col's sequence for insert

On Mon, 2014-07-21 at 10:00 +0800, Anil Menon wrote: Hi, I have a question on the right/correct practice on using the serial col's sequence for insert. Best way of explanation is by an example: create table id01 (col1 serial, col2 varchar(10)); insert into id01(col2) values

Re: [GENERAL] Referencing serial col's sequence for insert

On Tue, 2014-07-22 at 13:32 +, Albe Laurenz wrote: rob stone wrote: I have a question on the right/correct practice on using the serial col's sequence for insert. Best way of explanation is by an example: create table id01 (col1 serial, col2 varchar(10)); insert into id01

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

On Wed, 2014-07-09 at 14:01 +0200, basti wrote: Yes, there is the same problem. postgres logs also: Connection matched pg_hba.conf line 93 „hostall all 127.0.0.1/32md5“ Am 09.07.2014 13:14, schrieb Bill Moran: On Wed, 09 Jul 2014 13:04:19 +0200

Re: [GENERAL] Postgresql 9.2.4 - timezone error

On Thu, 2014-05-29 at 20:22 +0530, Bhushan Pathak wrote: Hello, I have postgresql 9.2.4 running on a CentOS 5 server. A JBOSS AS instance also runs on the same server, which connects to postgresql server [using defined connection pools]. The issue I'm facing is that the JBOSS

  1   2   >