Re: Oracke BLOB to Postgres BYTEA using ora2pg

út 15. 1. 2019 v 12:56 odesílatel Daniel Verite napsal: > Mihalidesová Jana wrote: > > > nipjd=> select distinct encode(serializable_value, 'escape') from > > alf_node_properties_zaloha where serializable_value is not null; > > >encode > > >

Re: Read consistency when using synchronous_commit=off

Sorry I misunderstood. The term "read consistency" is generally used either in the context of isolation level or in the context of slaves. > We don't have standby instance, as I have mentioned we are using just > one instance of postgres serving local clients running on the same > machine, do you

Re: Read consistency when using synchronous_commit=off

> After better thinking, I have to reply to myself since I m not entirely sure of my previous question. (I m digging into the docs, but i do not want to mislead you in the meanwhile) Yes, I was not able to get some statement regarding this in the docs, please let me know if you find something.

Re: aggregate functions are not allowed in UPDATE

Thank you, the following seems to have worked - On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > UPDATE users > SET avg_time = diffs.average_time_for_the_grouped_by_user > FROM diffs > WHERE users.uid = diffs.uid --< the missing "where" I commented about

RE: Can anyone please provide me list of customers using postgreSQL

From: Ramamoorthi, Meenakshi [mailto:meenakshi.ramamoor...@cpuc.ca.gov] Sent: Tuesday, January 15, 2019 2:42 PM To: pgsql-general Subject: Can anyone please provide me list of customers using postgreSQL Dear folks: 1) Can someone please send me a link of all companies using PostgreSQL ?

Re: Can anyone please provide me list of customers using postgreSQL

On Tue, Jan 15, 2019 at 12:42 PM Ramamoorthi, Meenakshi wrote: > > Dear folks: > 1) Can someone please send me a link of all companies using PostgreSQL ? > > 2) Both government and private companies using PostgreSQL > > 3) Any security issues found earlier and the steps taken for

Re: aggregate functions are not allowed in UPDATE

On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber wrote: > > Last question please - how to run the query for all users? > > I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL > way? > > How to refer to the outside "uid" from inside the CTE in the query below? > > WITH

Re: Can anyone please provide me list of customers using postgreSQL

On 1/15/19 12:42 PM, Ramamoorthi, Meenakshi wrote: Dear folks: 1)Can someone please send me a link of all companies using PostgreSQL ? Postgres is free.  There is no list of licensed sites 2)Both government and private companies using PostgreSQL 3)Any security issues found earlier and

Re: aggregate functions are not allowed in UPDATE

Last question please - how to run the query for all users? I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL way? How to refer to the outside "uid" from inside the CTE in the query below? WITH diffs AS ( SELECT gid, uid, played - LAG(played)

Can anyone please provide me list of customers using postgreSQL

Dear folks: 1) Can someone please send me a link of all companies using PostgreSQL ? 2) Both government and private companies using PostgreSQL 3) Any security issues found earlier and the steps taken for resolution or how it was mitigated. 4) Advantages of PostgreSQL compared

Re: aggregate functions are not allowed in UPDATE

And I should better change the avg_time column from TEXT to TIMESTAMPTZ (and use TO_CHAR on it later down the road) so that I can compare my players Regards Alex >

Re: aggregate functions are not allowed in UPDATE

Ahh, the subqueries - On Tue, Jan 15, 2019 at 5:59 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber > wrote: > >> So calculate the average somewhere else, put the result in a column, > >> and then reference that column in the SET

Re: Refining query statement

On 1/15/19 9:02 AM, Ron wrote: select distinct on (C.contact_id) C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact from Contacts as C    join Organizations as O on C.org_id = O.org_id    join Activities as A on C.contact_id = A.contact_id where

Re: Refining query statement

Adrian Klaver schrieb am 15.01.2019 um 17:44: So we end up with something like this: select distinct on (C.contact_id) C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact from Contacts as C    join Organizations as O on C.org_id = O.org_id    join

Re: Refining query statement

On Tue, 15 Jan 2019, Adrian Klaver wrote: Use BETWEEN?: https://www.postgresql.org/docs/10/functions-comparison.html a BETWEEN x AND y between So: next_contact BETWEEN '01/01/2019'::date AND '01/15/2019'::date Or a range function:

Re: aggregate functions are not allowed in UPDATE

On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber wrote: >> So calculate the average somewhere else, put the result in a column, >> and then reference that column in the SET clause. >> > > do you suggest to add a second CTE? That would qualify as "somewhere else" - as would a simple subquery in

Re: aggregate functions are not allowed in UPDATE

Unfortunately, I don't understand your advice, David - On Tue, Jan 15, 2019 at 5:46 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber > wrote: > > When I am trying > > > > WITH diffs AS ( > > SELECT > > gid, > > uid, > >

Re: aggregate functions are not allowed in UPDATE

On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber wrote: > When I am trying > > WITH diffs AS ( > SELECT > gid, > uid, > played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff > FROM moves > WHERE uid = 1 > ) > UPDATE users SET > avg_time = TO_CHAR(AVG(diff),

Re: Refining query statement

On 1/15/19 8:02 AM, Ron wrote: the best way to do it: So we end up with something like this: select distinct on (C.contact_id) C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact from Contacts as C    join Organizations as O on C.org_id = O.org_id   

aggregate functions are not allowed in UPDATE

Good evening, I have prepared a simple test case for my question: https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/0 There I create 3 tables: CREATE TABLE users ( uid SERIAL PRIMARY KEY, avg_time TEXT ); CREATE TABLE games ( gid SERIAL PRIMARY KEY, player1 INTEGER NOT NULL REFERENCES

Re: Refining query statement

On 1/15/19 8:26 AM, Rich Shepard wrote: On Tue, 15 Jan 2019, Adrian Klaver wrote: For the above I could see using a datepicker widget that allows for multidate select. The specifics would depend on the software you are using to write the UI. Adrian,   I'm using wxPython4, and I will use

Re: Refining query statement

On Tue, 15 Jan 2019, Ron wrote: And I've never liked this method (though I'm old and crotchety) Ron, I'm older but not crotchety (most days), and I'm taking my SQL knowledge beyone what I've used in the past. I would appreciate you're explaining why you don't like the explicit JOINs

Re: Refining query statement

On Tue, 15 Jan 2019, Thomas Kellerer wrote: With regards to "cleaner": the first thing to do is to remove the parentheses around the column list. In Postgres "(a,b,c)" creates a single column with an anonymous record type (that contains three fields), rather than selecting three columns. In

Re: Refining query statement

On Tue, 15 Jan 2019, Adrian Klaver wrote: For the above I could see using a datepicker widget that allows for multidate select. The specifics would depend on the software you are using to write the UI. Adrian, I'm using wxPython4, and I will use a calendar with that. But, is there a way to

Re: Refining query statement

On 1/15/19 9:47 AM, Thomas Kellerer wrote: Rich Shepard schrieb am 15.01.2019 um 16:39:   Working with my sales/client management system using psql I have a select statement to identify contacts to be made. This statement works: select (C.contact_id, C.lname, C.fname, C.direct_phone,

Sv: Using psql variables in DO-blocks

På tirsdag 15. januar 2019 kl. 16:51:09, skrev Andreas Joseph Krogh < andr...@visena.com >: Hi all.   I'm trying to use a psql variable in a DO-block, but it fails:   [snip]   Seems I was a bit lazy, here's what works:   \set resource_group 'Ressurser' \set

Re: Using psql variables in DO-blocks

On Tue, Jan 15, 2019 at 8:48 AM Andreas Joseph Krogh wrote: > Hi all. > > I'm trying to use a psql variable in a DO-block, but it fails: > [...] > Any hints? > Don't do that. The body of a DO block is a string literal and psql won't and shouldn't mess with its contents. You'll need to use

Re: Refining query statement

On 1/15/19 7:39 AM, Rich Shepard wrote:   Working with my sales/client management system using psql I have a select statement to identify contacts to be made. This statement works: select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact) from Contacts as C,

Re: Refining query statement

Rich Shepard schrieb am 15.01.2019 um 16:39: >   Working with my sales/client management system using psql I have a select > statement to identify contacts to be made. This statement works: > > select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, > A.next_contact) > from Contacts

Using psql variables in DO-blocks

Hi all.   I'm trying to use a psql variable in a DO-block, but it fails:   \set resource_group 'Ressurser' \set quoted_resource_group '\'' :resource_group '\'' DO $$ begin if not exists(SELECT * FROM tbl_group WHERE groupname = :quoted_resource_group)then raise notice 'Group % not found,

Refining query statement

Working with my sales/client management system using psql I have a select statement to identify contacts to be made. This statement works: select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact) from Contacts as C, Organizations as O, Activities as A where

Re: pg_restore restores privileges differently from psql

Yeah, this is a known issue --- the various GRANTs for a specific object are stored in a single "TOC entry" in the archive, which pg_restore will send to the server in a single PQexec call, causing them to be effectively one transaction. The easiest way to deal with it is to not send pg_restore's

RE: repmgr and SSH

Hi Ian If we setup SSH between all the nodes (master, standby and witness) there is a big security problem when repmgr monitors several configurations : For instance this architecture : - configuration A : primary A, standby A - configuration B : primary B, standby

RE: Oracke BLOB to Postgres BYTEA using ora2pg

Mihalidesová Jana wrote: > nipjd=> select distinct encode(serializable_value, 'escape') from > alf_node_properties_zaloha where serializable_value is not null; > > encode >

Re: Read consistency when using synchronous_commit=off

> I m not sure other clients are able to read from WAL buffer, therefore > i m not sure the data is available to other clients at that specific > point in time. No. On the standby the buffer cache has to be populated with the updates before other client sessions can read it. AFAIK other client

Re: repmgr and SSH

On 1/14/19 6:29 PM, ROS Didier wrote: Hi I would like to setup a repmgr configuration with one primary node, one standby node and one witness node.    Regarding SSH configuration, the documentation is not clear, I think. Do we need to setup SSH between the three nodes or only

Re: repmgr and STONIT - SPLIT BAIN

Hi On 1/15/19 5:54 PM, ROS Didier wrote: Hi We are going to implement repmgr according to the following configuration : -Primary node on the site A -Standby node on the site B -Witness node on the same site A Do we need to use LOCATION repmgr parameter in this case to avoid Split Brain

Re: Read consistency when using synchronous_commit=off

Hi, After better thinking, I have to reply to myself since I m not entirely sure of my previous question. (I m digging into the docs, but i do not want to mislead you in the meanwhile) If i recall correctly, written data is parked in WAL buffer before being synced to disk (to the transaction

Re: Read consistency when using synchronous_commit=off

Hi, all clients will get the latest version of the row (from RAM, that is). The only thing is that in case of server crash, not-yet-written-to-disk commits will be lost. detailed explanation can be found here: https://www.postgresql.org/docs/current/wal-async-commit.html regards, fabio

Read consistency when using synchronous_commit=off

To improve commit performance we are planning to use "synchronous_commit=off", with this if multiple clients are reading the same data/row will they always get the latest updated data/row ? (clients are using committed read and we are not using clustered environment, we have just one instance of

Re: Oracke BLOB to Postgres BYTEA using ora2pg

út 15. 1. 2019 v 11:37 odesílatel Pavel Stehule napsal: > > > út 15. 1. 2019 v 11:13 odesílatel Mihalidesová Jana < > jana.mihalides...@cetin.cz> napsal: > >> Hi, >> >> >> >> These are original data in blob on oracle >> >> >> >> SYS@CENIPR_1 > select distinct >>

Re: Oracke BLOB to Postgres BYTEA using ora2pg

út 15. 1. 2019 v 11:13 odesílatel Mihalidesová Jana < jana.mihalides...@cetin.cz> napsal: > Hi, > > > > These are original data in blob on oracle > > > > SYS@CENIPR_1 > select distinct > UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(serializable_value, 4000,1)) from > NIP_NE.ALF_NODE_PROPERTIES where

RE: Oracke BLOB to Postgres BYTEA using ora2pg

Hi, These are original data in blob on oracle SYS@CENIPR_1 > select distinct UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(serializable_value, 4000,1)) from NIP_NE.ALF_NODE_PROPERTIES where serializable_value is not null; UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(SERIALIZABLE_VALUE,4000,1))

Re: Oracke BLOB to Postgres BYTEA using ora2pg

Dobro jutro On 15/1/19 10:39 π.μ., Mihalidesová Jana wrote: Hi, We try to migrate from oracle to postgres using ora2pg but we hit some weird behavior of bytea. Or it’s just our ignorance. Table migration were ok, but we are not able to read bytea data. What we did wrong. Assuming your

repmgr and STONIT - SPLIT BAIN

Hi We are going to implement repmgr according to the following configuration : -Primary node on the site A -Standby node on the site B -Witness node on the same site A Do we need to use LOCATION repmgr parameter in this case to avoid Split Brain and

Re: Oracke BLOB to Postgres BYTEA using ora2pg

Hi út 15. 1. 2019 v 9:40 odesílatel Mihalidesová Jana < jana.mihalides...@cetin.cz> napsal: > Hi, > > > > We try to migrate from oracle to postgres using ora2pg but we hit some > weird behavior of bytea. Or it’s just our ignorance. > > Table migration were ok, but we are not able to read bytea

Oracke BLOB to Postgres BYTEA using ora2pg

Hi, We try to migrate from oracle to postgres using ora2pg but we hit some weird behavior of bytea. Or it's just our ignorance. Table migration were ok, but we are not able to read bytea data. What we did wrong. Thank you for your help, JM nipjd=> \d alf_node_properties_zaloha