Re: How to write such a query?

Hi, Armul, On Thu, Jan 6, 2022 at 12:46 AM Amul Sul wrote: > > See prepare statement : > https://www.postgresql.org/docs/current/sql-prepare.html The documentation is talking about a way to do it like: SELECT a, b, c FROM foo WHERE id = $1, which is equivalent to the SELECT a, b, c FROM foo

Re: How to write such a query?

On 1/6/22 1:06 AM, Igor Korot wrote: Hi, Ron, On Thu, Jan 6, 2022 at 1:01 AM Ron wrote: On 1/6/22 12:39 AM, Igor Korot wrote: Hi, ALL, In SQLite you can write: SELECT a, b, c FROM foo WHERE id = :id; where ":id" is the named parameter. The query above is similar to SELECT a,b,c FROM foo W

Re: How to write such a query?

On 1/6/22 1:07 AM, Igor Korot wrote: Hi, David, On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston wrote: On Wednesday, January 5, 2022, Igor Korot wrote: Is there a way to write such a SELECT statement with the named parameter in PostgreSQL? The server, and its prepared SQL capability, doe

Re: How to write such a query?

Hi, David, On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston wrote: > > On Wednesday, January 5, 2022, Igor Korot wrote: >> >> >> Is there a way to write such a SELECT statement with the >> named parameter in PostgreSQL? > > > The server, and its prepared SQL capability, doesn’t understand named

Re: How to write such a query?

Hi, Ron, On Thu, Jan 6, 2022 at 1:01 AM Ron wrote: > > On 1/6/22 12:39 AM, Igor Korot wrote: > > Hi, ALL, > > In SQLite you can write: > > > > SELECT a, b, c FROM foo WHERE id = :id; > > > > where ":id" is the named parameter. > > > > The query above is similar to > > > > SELECT a,b,c FROM foo WH

Re: How to write such a query?

On 1/6/22 12:39 AM, Igor Korot wrote: Hi, ALL, In SQLite you can write: SELECT a, b, c FROM foo WHERE id = :id; where ":id" is the named parameter. The query above is similar to SELECT a,b,c FROM foo WHERE id = ?; except that the parameter has a name. Is there a way to write such a SELECT s

Re: How to write such a query?

On Wednesday, January 5, 2022, Igor Korot wrote: > > > Is there a way to write such a SELECT statement with the > named parameter in PostgreSQL? > The server, and its prepared SQL capability, doesn’t understand named parameters. Only numbered ones. That said, there are a number of different way

Re: How to write such a query?

See prepare statement : https://www.postgresql.org/docs/current/sql-prepare.html On Thu, Jan 6, 2022 at 12:10 PM Igor Korot wrote: > > Hi, ALL, > In SQLite you can write: > > SELECT a, b, c FROM foo WHERE id = :id; > > where ":id" is the named parameter. > > The query above is similar to > > SELE

How to write such a query?

Hi, ALL, In SQLite you can write: SELECT a, b, c FROM foo WHERE id = :id; where ":id" is the named parameter. The query above is similar to SELECT a,b,c FROM foo WHERE id = ?; except that the parameter has a name. Is there a way to write such a SELECT statement with the named parameter in Pos

Re: How best to create and use associative array type in Postgres?

On 1/5/22 12:26, Shaozhong SHI wrote: Have a look at this one. GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL There is no documentation on how to use it. Why use it when you have: https://www.postgresql.org/docs/current/datatype-json.

Re: How best to create and use associative array type in Postgres?

Have a look at this one. GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL There is no documentation on how to use it. Regards, David On Wed, 5 Jan 2022 at 16:24, Tom Lane wrote: > Shaozhong SHI writes: > > What do you think this attempt

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

[ please keep the list cc'd ] Dominique Devienne writes: > On Wed, Jan 5, 2022 at 7:26 PM Tom Lane wrote: >> That will only revoke privileges that were granted by the role >> doing the REVOKE, so I surmise that you did this as some other role. > Hmmm, that's interesting... But it brings up some

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

Dominique Devienne writes: > Final step is to DROP the "owner" ROLE itself, but before doing that, > I run REVOKE ALL ON DATABASE "ddevienne" FROM "owner". That will only revoke privileges that were granted by the role doing the REVOKE, so I surmise that you did this as some other role. But actua

Re: Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

> > Any help would be appreciated in diagnosing this vexing issue. Thanks, --DD Have you captured all the DDL generated by your C++ code?

Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges

At least I think I've revoked the privileges... Hi. I'm writing a unit test that creates a set of schemas and roles, then drops all those roles and schemas. First I create a NOINHERIT NOLOGIN CREATEROLE "owner" ROLE, which I GRANT on the current user (and other LOGIN users later), then SET ROLE "

Re: Can we use sql language to create a function in Postgres?

On Wed, Jan 5, 2022 at 5:27 AM Shaozhong SHI wrote: > > Any online documentation or examples for using Oracle SQL in Postgres? FYI there are commercial offerings (in particular, EDB, which I am not affiliated with) that minimize compatibility concerns with oracle. If doing this by hand, utilizin

Re: Create and access a dictionary type

>> >> Well, yes - you can always decompose the dict and store it in an EAV, but >> sometimes it's more convenient to just use "nested" dictionary at the row >> level. Which is what a JSONB column does. > > Edgar Codd is rolling in his grave. > > Contributing mightily to the global angular m

Re: Create and access a dictionary type

On 1/5/22 10:35 AM, Tomas Vondra wrote: On 1/5/22 17:24, Ron wrote: On 1/5/22 7:22 AM, Tomas Vondra wrote: On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and

Re: Create and access a dictionary type

On 1/5/22 14:36, Shaozhong SHI wrote: I just checked operators.  I could not find any operator to set a new value given a key. I'd say jsonb_set is what you're looking for. Postgres does not have a thing that'd match the Oracle feature exactly, you'll have to adjust your application to use

Re: Create and access a dictionary type

On 1/5/22 17:24, Ron wrote: On 1/5/22 7:22 AM, Tomas Vondra wrote: On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and e.g. in Python can map to dict data type

Re: Create and access a dictionary type

On 1/5/22 7:22 AM, Tomas Vondra wrote: On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and e.g. in Python can map to dict data type. The question confuses me.

Re: How best to create and use associative array type in Postgres?

Shaozhong SHI writes: > What do you think this attempt by using create type and create a function? > Managing Key/Value Pairs in PostgreSQL (justatheory.com) > The date alone should suggest to you that there might be better ways to do it

Re: How best to create and use associative array type in Postgres?

What do you think this attempt by using create type and create a function? Managing Key/Value Pairs in PostgreSQL (justatheory.com) Regards, David On Wed, 5 Jan 2022 at 14:54, Tom Lane wrote: > Shaozhong SHI writes: > > How best to

Re: How best to create and use associative array type in Postgres?

Shaozhong SHI writes: > How best to create and use associative array type in Postgres? I think the closest thing you'd find to that is jsonb, or contrib/hstore if you'd like something with a bit less complexity. The notation is unlikely to look much like Oracle, but they both have the ability to

Replicate consistent snapshot to a second PG instance using FDWs

We have a setup where we run 2 locations with both locations containing a full HA setup (using Patroni, etcd, Consul). Each location has 2 PG servers + 1 witness node. At certain time intervals or on demand, our customer would want to send the data from one location to the other over the wir

Re: Create and access a dictionary type

I just checked operators. I could not find any operator to set a new value given a key. Regards, David On Wed, 5 Jan 2022 at 13:22, Tomas Vondra wrote: > On 1/5/22 14:17, Shaozhong SHI wrote: > > Any examples in Postgres to create a dictionary type to store and access > > key value pairs? > >

Re: Create and access a dictionary type

On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and e.g. in Python can map to dict data type. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.c

Re: Postgis - geography function

On 1/5/22 09:48, Sameer TWAYANA wrote: Hi Sir, I need some help. I have been using the postgis extension for many years. Now in postgis version 3.2 using geom data in geography with ST_Length and ST_Distance function returns some weird results. Before I used 3.1.4, all was good. But I got dif

Create and access a dictionary type

Any examples in Postgres to create a dictionary type to store and access key value pairs? Regards, David

Postgis - geography function

Hi Sir, I need some help. I have been using the postgis extension for many years. Now in postgis version 3.2 using geom data in geography with ST_Length and ST_Distance function returns some weird results. Before I used 3.1.4, all was good. But I got different results in version 3.2. So can you su

How best to create and use associative array type in Postgres?

In Oracle, one can create and use associative array. For instance, TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38); NODES_WAITING FID_SET; How best to create and use associative array type in Postgres? Or, what is the best/most efficient equivalent in Postgres? Regards, David

Re: Can we use sql language to create a function in Postgres?

On Wed, Jan 05, 2022 at 11:22:34AM +, Shaozhong SHI wrote: > If so, can we use Oracle SQL script in DO block? > > Does this mean that all Oracle SQL can be used in Postgres? I highly doubt that. But - I used oracle last time like 2 years ago, so the only thing I can tell you: TRY. instead of

Re: Can we use sql language to create a function in Postgres?

Any online documentation or examples for using Oracle SQL in Postgres? Regards, David On Wed, 5 Jan 2022 at 11:22, Shaozhong SHI wrote: > If so, can we use Oracle SQL script in DO block? > > Does this mean that all Oracle SQL can be used in Postgres? > > Regards, > > David > > On Wed, 5 Jan 20

Re: Can we use sql language to create a function in Postgres?

If so, can we use Oracle SQL script in DO block? Does this mean that all Oracle SQL can be used in Postgres? Regards, David On Wed, 5 Jan 2022 at 11:12, hubert depesz lubaczewski wrote: > On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote: > > I was given an Oracle script. Can we

Re: Can we use sql language to create a function in Postgres?

On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote: > I was given an Oracle script. Can we use sql language to create a function > in Postgres? Sure: create function z() returns int4 language sql as $$ select 123; $$; depesz

Re: Can we use sql language to create a function in Postgres?

On 05/01/2022 11:08, Ray O'Donnell wrote: On 05/01/2022 11:04, Shaozhong SHI wrote: I was given an Oracle script.  Can we use sql language to create a function in Postgres? Yes, absolutely: create function my_function(...) as $$   select. $$ language sql; Should have added the link to

Re: Can we use sql language to create a function in Postgres?

On 05/01/2022 11:04, Shaozhong SHI wrote: I was given an Oracle script.  Can we use sql language to create a function in Postgres? Yes, absolutely: create function my_function(...) as $$ select. $$ language sql; Is this what you mean? Ray. -- Raymond O'Donnell // Galway // Ireland

Can we use sql language to create a function in Postgres?

I was given an Oracle script. Can we use sql language to create a function in Postgres? Regards, David