Re: Different memory allocation strategy in Postgres 11?

Thomas Munro schrieb am 26.10.2018 um 22:13: >>> I typically configure "shared_buffers = 4096MB" on my 16GB system as >>> sometimes when testing, it pays off to have a bigger cache. >>> >>> With Postgres 10 and earlier, the Postgres process(es) would only >>> allocate that memory from the

Re: why select count(*) consumes wal logs

Ravi Krishna schrieb am 07.11.2018 um 15:10: > >> select data_page_checksum_version from pg_control_init() > > returned 1. So we have page_checksum turned on, and wal_log_hints off. If page_checksum is enabled, then wal_log_hints is ignored (or actually always assumed "on")

Re: libwinpthread-1.dll missing in PostgreSQL 11.0-1 for Windows

Marko Krajnc schrieb am 12.11.2018 um 11:19: > I just downloaded the letest version of PostgreSQL for Windows - > v11.0-1 (as a ZIP archive here: > https://www.enterprisedb.com/download-postgresql-binaries) and > started the initdb.exe to initialize the database, but I get the > message that libwin

Re: BDR and PostgreSQL 12 and Windows support

Andrew Smith schrieb am 16.11.2018 um 11:01: > Are there any core features at the > moment that are Linux only? JIT, introduced in Postgres 11, comes to mind

Re: Package-support plans?

Nicklas Karlsson schrieb am 20.11.2018 um 07:32: > Are there any plans for including Oracle-style packages with > namespace/visibility support at some point or is it just "use > schemas"? I could even live without package-state but I find it > frustrating with the namespace pollution when the appli

Re: Regarding Tds_fdw

Durgamahesh Manne schrieb am 28.11.2018 um 11:28: > I have configured tds_fdw on postgres server.. I have created > multiple foreign tables related to sql server as of now i could run > select queries with out any issues > >   i got  this error ERROR: cannot insert into foreign table "pgsql"  when

Re: simple division

Martin Mueller schrieb am 04.12.2018 um 21:29: I have asked this question before and apologize for not remembering it. How do you do simple division in postgres and get 10/4 with decimals? In the expression 10/4 both numbers are integers. And an integer divsion does not yield decimals (that's

Re: simple division

Martin Mueller schrieb am 04.12.2018 um 21:57: I didn't formulate my question properly, because the query went like "select alldefects /wordcount" where alldefects and wordcount are integers. But none of the different ways of putting the double colon seemed to work. One way is to make one

Re: surprising query optimisation

Stephen Frost schrieb am 30.11.2018 um 14:05: > PG doesn’t know, with complete certainty, that there’s only 3 > values. Would the optimizer consult a check constraint ensuring that?

Re: surprising query optimisation

Chris Withers schrieb am 05.12.2018 um 12:42: > So, interestingly, this box has 250GB memory in it, and even though > I've set effective_cache_size to 200GB, I only see 9G of memory being > used. How can I persuade postgres to keep more in memory? effective_cache_size is a hint to the optimizer on

Re: order of reading the conf files

Stephen Frost schrieb am 06.12.2018 um 15:52: > The regular postgresql.conf file is read first, then > postgresql.auto.conf and then pg_hba.conf and pg_ident.conf. We can't > read pg_hba.conf/pg_ident.conf before reading postgresql.conf and > postgresql.auto.conf because their location is specifie

Re: != ANY(array) does not behave as expected

Chris Wilson schrieb am 07.12.2018 um 13:39: > However, if we try to invert it by using the != operator, then we get > unexpected results: > > select * from foo where id NOT IN (1, 2); /* returns row 3 only, as expected > */ > select * from foo where id != ANY (ARRAY[1, 2]); /* returns all rows,

Re: != ANY(array) does not behave as expected

Thomas Kellerer schrieb am 07.12.2018 um 13:48: > Chris Wilson schrieb am 07.12.2018 um 13:39: >> However, if we try to invert it by using the != operator, then we get >> unexpected results: >> >> select * from foo where id NOT IN (1, 2); /* returns row 3 only, as expec

Re: loading jdbc Driver in servlet

Rob Sargent schrieb am 14.12.2018 um 19:28: > Using java 1.8, postgresql-42.1.4.jar, embedded tomcat 9 > > It appears to me that I need to make the call > "Class.forName("org.postgresql.Driver)" when the entry is in a > servlet. Is this expected, within a servlet, or is this just /post > hoc ergo

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 a

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 Activi

Re: Casting Integer to Boolean in assignment

Geoff Winkless schrieb am 24.01.2019 um 12:45: > The reason for that at least is that '1' and '0' are valid boolean values. > > https://www.postgresql.org/docs/9.5/datatype-boolean.html > > There's additional text describing why casts are chosen to be defined > as implicit or not here > > https:

Overloaded && operator from intarray module prevents index usage.

While testing a query on an integer array with a GIN index, I stumbled over a behaviour which surprised me and which I would consider a bug - but maybe I am wrong. Consider the following table: create table idlist (ids int[], ... other columns ...); create index on idlist using gin (id

Re: Overloaded && operator from intarray module prevents index usage.

Andrew Gierth schrieb am 28.02.2019 um 10:29: > [intarray woes] > > Thomas> Is this expected behaviour? Is this caused by the Postgres core > Thomas> (e.g. the optimizer to taking the opclass into account) or is > Thomas> it a "problem" in the way the intarray module defines its > Thomas> oper

Re: Overloaded && operator from intarray module prevents index usage.

Michael Lewis schrieb am 28.02.2019 um 21:23: Yeah, because it's an exact datatype match while the core operator is anyarray && anyarray which is not. Can you dumb down how to change the index or column type such that an index will be used for the && operator while intarray extension is

Re: Where **not** to use PostgreSQL?

Thomas Güttler schrieb am 28.02.2019 um 12:47: > where would you suggest someone to **not** use PostgreSQL? > > Why would you do this? > > What alternative would you suggest instead? Due to the MVCC implementation, I would hesitate to use Postgres in environments that have an extremely high and

Re: Future Non-server Windows support???

Bill Haught schrieb am 07.03.2019 um 20:41: "...most Windows 95 applications still run fine in Windows 10 - that's 20 years of binary compatibility" See Major Linux Problems on the Desktop, 2018 edition by Artem S. Tashkinov https://itvision.altervista.org/why.linux.is.not.ready.for.the.desktop

Re: POSTGRES/MYSQL

Sonam Sharma schrieb am 11.03.2019 um 12:31: > We are planning to migrate our database into any open source DB. > Can someone please help me in knowing which one will be better among > POSTGRESQL and MYSQL. > > In what terms postgres is better than MYSQL. you might want to have a look at this:

Re: POSTGRES/MYSQL

Laurenz Albe schrieb am 12.03.2019 um 10:05: >> Also MySQL has a query cache that allows the results of very common queries >> to be much faster. > > I have used that feature, and it has bitten me: > https://stackoverflow.com/q/44244482/6464308 Note that the query cache was removed in MySQL 8.0

Re: Camel case identifiers and folding

Steve Haresnape schrieb am 15.03.2019 um 00:06: > I'm porting a sql server database to postgresql 9.6. My camelCase > identifiers are having their humps removed. This is disconcerting and > sad. > > Is there a cure for this? > > I don't want to quote my identifiers unless I have to. You don't ne

Re: How to parse XML in Postgres newer versions also

Andrus schrieb am 17.03.2019 um 08:36: In Postgres 9.1.2 script below produces proper results: 1.34 5.56 create temp table t(x xml, nsa text[][]) on commit drop; insert into t values(    ' http://www.w3.org/2001/XMLSchema-instance"; xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">    

Re: Camel case identifiers and folding

Chris Travers schrieb am 18.03.2019 um 13:16: > MySQL also ignores the standard and does not fold cases. Unless you configure it to fold to lowercase. Which in turn is a cure for the fact, that case-sensitivity of table names depends on the case-sensitivity of the underlying file system (not th

Re: Postgres Enhancement Request

Zwettler Markus (OIZ) schrieb am 20.03.2019 um 11:10: > CREATEROLE allows users to create new roles also having the CREATEDB > privilege (at least in version 9.6). > > We want special users to be able to CREATEROLE without being able to CREATEDB > (eg. when usermanagement is done by the applicat

Re: Postgres Enhancement Request

Tom Lane schrieb am 20.03.2019 um 14:59: >>> Please prevent users with CREATEROLE to create roles having CREATEDB >>> (analogous SUPERUSER and REPLICATION). > >> I agree that would be a welcome enhancement. > > No, it wouldn't. The point of CREATEROLE is to allow user creation > and deletion t

Re: Forks of pgadmin3?

kpi6...@gmail.com schrieb am 22.03.2019 um 17:25: > 95% of my time I use pgadminIII just to type select and update > statements and review the output rows. > > I know that I can do this in psql but it’s not handy with many > columns. An alternative you might want to try is SQL Workbench/J: https

Re: Forks of pgadmin3?

Dave Cramer schrieb am 25.03.2019 um 22:33: > Thomas, > > Any chance it would run under graalvm getting rid of the need for the JVM ? > > Dave Cramer It's hard to tell, but I'd say about 70-80% of my users use Windows, so GraalVM is not an option. I also can't bundle it for non-Windows users a

Re: Upgrading PostgreSQL under Windows

Arnaud L. schrieb am 27.03.2019 um 14:00: > Le 27/03/2019 à 07:02, Kumar Prince NCS a écrit : >> We are  Upgrading PostgreSQL under Windows from v9.3 to 9.5. Following steps >> from https://www.postgresql.org/docs/10/pgupgrade.html >> >> We are using PostgreSQL database for confluence application.

Re: PostgreSQL equivalent of Oracle "member of"

Vadi schrieb am 29.03.2019 um 10:44: > I would like to know if there is any equivalent in PostgreSQL for the Oracle > "member of" syntax. > > The usage is as shown below: > > I have used the Oracle sample HR schema for the below example: > > CREATE OR REPLACE TYPE params as table of varchar2 (1

Postgres comparison bugfixes between arbitrary versions

Hello, some time ago someone published a website where it was possible to select two arbitrary Postgres version and then see a list of Bugfixes (and features) that are missing in the older version of the two. It was intended to help discussions with admins that are afraid of upgrading. But I

Re: Postgres comparison bugfixes between arbitrary versions

Andrew Gierth schrieb am 05.04.2019 um 13:15: > Thomas> some time ago someone published a website where it was possible > Thomas> to select two arbitrary Postgres version and then see a list of > Thomas> Bugfixes (and features) that are missing in the older version > Thomas> of the two. > > wh

pg_indexes doesn't show indexes for partitioned tables - bug or intended?

In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the partitions however do show up). E.g.: CREATE TABLE base_table ( column1 varchar(50) NOT NULL, column2 integer NOT NULL, column3 integer not null, part

Re: pg_indexes doesn't show indexes for partitioned tables - bug or intended?

David Rowley schrieb am 10.04.2019 um 17:57: In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the partitions however do show up). Is leaving out the indexes defined on the partitioned table intended or a bug? Overlooked for PG11. Y

Re: Notification for Minor Release and Security Update

Kumar, Virendra schrieb am 11.04.2019 um 22:16: Is there a subscription URL we have to subscribe for to know about Minor Version Release and Security Updates when they are released for public usage. They are sent to the "announce" mailing list, if I'm not mistaken: https://www.postgresql.org

Re: Is it possible to store the output of EXPLAIN into a table

Souvik Bhattacherjee schrieb am 22.04.2019 um 17:27: Hi, I was just wondering if it is possible to store the output of EXPLAIN into a table. create temp table mytab as (select * from (explain select * from table1 where attr = 5) t); Unfortunately, the query above does not work. You can't

Re: Is it possible to store the output of EXPLAIN into a table

Souvik Bhattacherjee schrieb am 22.04.2019 um 17:27: Hi, I was just wondering if it is possible to store the output of EXPLAIN into a table. create temp table mytab as (select * from (explain select * from table1 where attr = 5) t); Unfortunately, the query above does not work. You can't

Re: Resetting identity columns

Ray O'Donnell schrieb am 22.04.2019 um 17:30: I'm probably doing something silly I'm migrating data from one database table to another, where the old table used a SERIAL primary key and the new one uses GENERATED BY DEFAULT AS IDENTITY. Having loaded the data into the new table, I need to res

Re: how to add more than 1600 columns in a table?

pabloa98 schrieb am 24.04.2019 um 22:17: Sadly today we hit the 1600 columns limit of Postgresql 11. How could we add more columns? Note: Tables are OK. We truly have 2400 columns now. Each column represents a value in a matrix. We have millions of rows so I would prefer not to transpose each

Re: ERROR: operator does not exist: timestamp without time zone + integer

Daulat Ram schrieb am 30.04.2019 um 05:46: > We are getting an ERROR: “operator does not exist: timestamp without > time zone + integer “ while creating table in postgres. The same > script is working fine in Oracle, I know there are some changes in > postgres but I am unable to identify . Please

Re: Postgres for SQL Server users

Ravi Krishna schrieb am 06.05.2019 um 23:56: I recently had to write an equivalent of UNPIVOT. UNPIVOT is actually quite easy with Postgres: https://blog.sql-workbench.eu/post/unpivot-with-postgres/ Thomas

Re: Postgres for SQL Server users

Igal Sapir schrieb am 07.05.2019 um 07:58: > GIS is a good feature but it's a niche feature, so while I'll mention > it with extensions I am looking for more general-purpose comparisons > and areas where Postgres is as-good or better than SQL Server. I have a comparison of various DBMS products on

Re: PG version recommendation

David Gauthier schrieb am 07.05.2019 um 20:52: I need to pick a PG version in my request. I want something that will be stable and reliable while, of course, being able to perform well. What would be a good choice for PG version? There is no reason to not choose the latest version. So I would

Re: Questions about btree_gin vs btree_gist for low cardinality columns

Will Hartung schrieb am 31.05.2019 um 00:11: > If you have 10M rows with a “STATUS” column of 1 or 2, and an index > on that column, then you have a 2 node index with a bazillion row > pointers. Some systems (I can’t speak to PG in this regard) > degenerate in this kind of use case since the index

Re: General question about OS

Drexl Spivey schrieb am 10.06.2019 um 04:45: > It seems in my little database development experience that this is > one area where windows might actually offer the best, most > mature/developed choices. If this is the case, I should acclimate > myself to it more. > > I have found many applications

Re: Featured Big Name Users of Postgres

Igal Sapir schrieb am 12.06.2019 um 07:58: > Andreas - unfortunately I do not recognize any of the names so it's probably > European entities that are not very popular here. But Lucee is a Swiss company, so why aren't European companies interesting? I know that Zalando (European online clothing

Re: how to concat/concat_ws all fields without braces

Jean Louis schrieb am 15.06.2019 um 13:19: > I have tried doing something like: > > SELECT concat_ws(' ', table.*) FROM table; > > and if I do that way, it is essentially same as > > SELECT concat(table.*) FROM table; > > and I get the items in braces like (1,something). > > Why do I get it i

Re: Use ctid in where clause in update from statement

> I come from the Oracle world and am trying to convert some queries to > PostgreSQL syntax. One of these queries is a MERGE statement, which I > converted into an UPDATE SET FROM WHERE construct. In the original > query I use the pseudo column ROWID to match a source row with a > target row. > >

Re: Use ctid in where clause in update from statement

Dirk Mika schrieb am 01.07.2019 um 12:02: > I know I can join using the pk, but in oracle using the rowid is > faster so I wanted to know, if this is possible in PostgreSQL as > well. Well, in Postgres ctid is not necessarily faster.

Re: Use ctid in where clause in update from statement

Dirk Mika schrieb am 01.07.2019 um 13:18: > The problem with the INSERT ON CONFLICT is that an insert is tried here > first, which may fire triggers. > > In my case there is a fairly expensive INSERT Trigger ON EACH ROW, which I > would like to avoid. The insert trigger will only be fired if an

Re: Use ctid in where clause in update from statement

> As you can see the trigger function is called for the row I try to > insert, but you can also see that there's no tuple inserted but one > conflicting. Ah, right. Thinking about it, it _has_ to call any BEFORE trigger function as that might change values of the row to be inserted that could

Re: Sequences part 2

Karl Martin Skoldebrand schrieb am 02.07.2019 um 12:44: > Looking more at sequences, I have some sequences that start with ID 1, > incrementing each record by 1. > > So I have e.g. 1 Spain 2. Germany 3. France 4. Ireland 5. Norway > > Now I want to insert more countries, between France and Irela

Re: Sequences part 2

Karl Martin Skoldebrand schrieb am 02.07.2019 um 13:20: >>> Now I want to insert more countries, between France and Ireland. >> >> That is a wrong assumption - there is no "between" for rows in a >> relational database. >> > > Yes, I'm fairly aware of this. However the application the > databas

Re: Too short field

Karl Martin Skoldebrand schrieb am 03.07.2019 um 13:30: > Now, we have a bug in another application that prevents an automatic > tool to enter certain users in the database. The organisational field > is varchar(60) while the actual Organisation “abbreviation” may be as > long as 70 characters (don

Why does jsonb_set() remove non-mentioned keys?

Why does select jsonb_set('{"foo": 1}'::jsonb, '{bar}', to_jsonb(null::int), true) return NULL when all it should do is to add a second key? I would expect {"foo": 1, "bar": null} or no change at all to the original JSON value, but not that the whole JSON is set to null. In the original c

Re: Why does jsonb_set() remove non-mentioned keys?

David G. Johnston schrieb am 04.07.2019 um 18:20: On Thu, Jul 4, 2019 at 6:18 AM Thomas Kellerer mailto:spam_ea...@gmx.net>> wrote: Why does    select jsonb_set('{"foo": 1}'::jsonb, '{bar}', to_jsonb(null::int), true) return NULL when all

Re: Why does jsonb_set() remove non-mentioned keys?

Gianni Ceccarelli schrieb am 05.07.2019 um 10:00: >> strict functions with sql null inputs yield sql null output without >> even executing the function > > So when the SQL-level executor sees a call to any function declared > strict with some NULL parameters, it doesn't call the function at > all.

Re: Adding SHOW CREATE TABLE

Nathaniel Sabanski schrieb am 12.05.2023 um 13:29: HN had a thread regarding the challenges faced by new users during the adoption of Postgres in 2023. One particular issue that garnered significant votes was the lack of a "SHOW CREATE TABLE" command, and seems like it would be an easy one to i

Re: function signature allow "default" keyword

jian he schrieb am 15.05.2023 um 10:33: > > function idea. > allow function calling using the default keyword for any of the input > arguments. > > example: https://dbfiddle.uk/FQwnfdmm > So something like this "SELECT * FROM customer_orders(2579927, > 'order_placed_on DESC', default, 2);" > shou

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

Marc Millas schrieb am 21.05.2023 um 13:13: Oracle have One (1) DB per instance (in Oracle its not named a cluster as...there is only one !). So ... Oracle can have multiple (pluggable) databases per instance since Oracle 12

Re: Reproducing incorrect order with order by in a subquery

Ruslan Zakirov schrieb am 13.06.2023 um 09:49: > For example I have a query: > > SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) > main; > > So the `ORDER BY` clause ended up in a subquery. Most of the time > ordering works until it doesn't. > > Can you help me create a set of

Re: Using vars in jsonbpath operator ?

Markur Sens schrieb am 16.06.2023 um 13:54: > I understand that on a where clause a gin index can be used for the following > predicate > > a.data @? '$.results.docs[*].accs[*] ? (@.id == “123") > > I have a join query however on the following condition > > jsonb_path_exists(a.data, '$.results.

Re: Fwd: TSQL To Postgres - Unpivot/Union All

Anthony Apollis schrieb am 20.07.2023 um 14:17: Does not give me outputs for columns: Metric and Pricing Channel: image.png What am i doing wrong? Please assist? UNPIVOT can be done using a LATERAL sub-query with a VALUES clause: https://blog.sql-workbench.eu/post/unpivot-with-postgres/

Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

Dionisis Kontominas schrieb am 26.07.2023 um 11:00: > Hello all, > > In the Subject I mention what I am intending to do. Letme put some context; > this is my table: > > portal_user_role > ( >     f_id INTEGER NOT NULL, >     f_portal_user_id INTEGER NOT NULL, >     f_portal_role_id INTEGER NOT NUL

Re: How to check for Overlapping Date Ranges with extra fields in table EXCLUDE constraint?

Dominique Devienne schrieb am 26.07.2023 um 11:39: > On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas > wrote: > > Dionisis Kontominas schrieb am 26.07.2023 um 11:00: > > do not want two records to overlap, for the same user, the same role >

Re: Dropping all tables in a database

H schrieb am 07.08.2023 um 03:17: > I am running PostgreSQL 13.11 and tried to drop all tables in a > database without dropping the database or schema. After logging in as > the correct user, the following SQL statement does not work: > > SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE

Re: Role for just read the data + avoid CREATE / ALTER / DROP

Durumdara schrieb am 25.08.2023 um 14:38: > Normally we use the "db owner" role for the connection, but this can do > everything (DDL-DML). > Somewhere they want to access a DB through a Read Only connection. > > In MS-SQL Server it is simple, but in PG it seems to be some kind of "hell". > Former

Re: Right version of jdbc

Raivo Rebane schrieb am 25.09.2023 um 10:36: > I use : >   PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit > and > PostGIS  3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 > > What versions of jdbc driver I have to use for proper work ? Use the latest, i.e. 42.6.0

Re: Inheritance in PostgreSQL

Merlin Moncure schrieb am 18.10.2023 um 03:20: > The only thing you can't really do in SQL easily without writing > nasty triggers are things like, 'this table must be linked from one > and only one of these candidate tables'. I think the language > probably ought to support this, but I don't thin

Re: Correct way of using complex expressions as partitioning key

Alexander Rumyantsev schrieb am 27.10.2023 um 06:27: > Hello! > > Is there some correct way to use complex expressions as a key for partitioned > table? > Inserting works as expected, but select runs over all partitions until use > complete partition key expression as predicate > > test=# crea

Re: Add support for data change delta tables

PavelTurk schrieb am 15.01.2024 um 11:00: > Currently PostgreSQL doesn't support data change delta tables. For example,  > it doesn't support this type of query: > > SELECT * FROM NEW TABLE ( > INSERT INTO phone_book > VALUES ( 'Peter Doe', '555-2323' ) > ) AS t > > PostgreSQL has RETURNI

Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`

Ilya Basin schrieb am 09.03.2024 um 20:08: Hi List. I have a list of bigint keys and I need to retrieve rows by these keys. Normally, I would split this list into pages of size 900 and perform several `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary ORM we use can only produce t

Re: (When) can a single SQL statement return multiple result sets?

Tom Lane schrieb am 11.04.2024 um 01:02: > Jan Behrens writes: >> While writing a PostgreSQL client library for Lua supporting >> Pipelining (using PQsendQueryParams), I have been wondering if there >> are any single SQL commands that return multiple result sets. > > Right now, I don't think so.

Re: Question about when PostgreSQL 11.0 was released

Bysani, Ram schrieb am 08.03.2021 um 19:22: Hello community: I am not finding the release cadence for the PostgreSQL databases. Please let me know how I can find the base and dot version release dates for: PostgreSQL 11 PostgreSQL 12 PostgreSQL 13 Check the release notes for the respective

Re: Log files polluted with permission denied error messages after every 10 seconds

Andrus schrieb am 05.03.2021 um 18:36: > Hi! > >>Windows? Don't let anti-virus software mess with the data directory. > > Windows default Windows Defender is active. I excluded data, pg_wal folders > and postgres process: > > > Then turned real-time protection off: > > Problem persists. New ent

jsonpath with @@ and iterating over arrays

I have a question regarding the processing of arrays when using the "short jsonpath" with the @@ operator. E.g. when trying to "translate" this jsonpath where jsonb_path_exists(the_column, '$[*] ? (@.id == 42 && @.type == "foo")') to be used with the @@ operator, then the "? (...)" cannot be

Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 09:19: > This may be a non-issue, and I don't want to waste your time. But perhaps > someone can have a look to see if there is anything obvious I have missed. > > I am writing a cross-platform accounting app, and I test using Sql > Server on Windows 10 and P

Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 09:52: >>> I am writing a cross-platform accounting app, and I test using Sql >>> Server on Windows 10 and PostgreSql on Fedora 31. Performance is >>> usually very similar, with a slight edge to PostgreSql. Now I have a >>> SELECT which runs over twice as fast

Re: SELECT is faster on SQL Server

Frank Millman schrieb am 19.03.2021 um 10:16: Very often "distinct on ()" is faster in Postgres compared to the equivalent solution using window functions The two derived tables (cl_bal, op_bal) seem to be doing exactly the same thing - at least I can't spot a difference. If that is correct,

Re: Query processing in postgresql

Rama Krishnan schrieb am 15.04.2021 um 19:04: > How the select statement has been processed in the postgresql http://www.interdb.jp/pg/pgsql03.html

Re: Size of PostgreSQL backup ./. Sybase DUMP

Matthias Apitz schrieb am 17.04.2021 um 08:59: As I said, the Sybase DUMP command dumps a single database, i.e. works more like pg_dump of PostgreSQL. Ofc, they're like apples and oranges, but the netto data of the tables must be written to disk, and as I said above in both cases compressed with

PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray

Hello, this aggregate can be created without problems on PG 13 and before: CREATE AGGREGATE array_accum(anyelement) ( SFUNC = array_append, STYPE = anyarray, INITCOND = '{}' ); However, that fails with PG 14beta1 because array_append's parameter are now (anycompatiblearra

Re: PG 14 - can't upgrade from a database using an aggregate with anyelement and anyarray

Tom Lane schrieb am 22.05.2021 um 15:25:>> this aggregate can be created without problems on PG 13 and before: CREATE AGGREGATE array_accum(anyelement) ( SFUNC = array_append, STYPE = anyarray, INITCOND = '{}' ); However, that fails with PG 14beta1 because ar

Re: index unique

Marc Millas schrieb am 03.06.2021 um 22:51: on a table we need a primary key and to get a unique combinaison, we need 3 columns of that table: 1 of type integer, 1 of type text, 1 of type geometry creating the PK constraint doesn work: (even with our current small data set) ERROR:  index row si

Re: returning setof from insert ?

Laura Smith schrieb am 14.07.2021 um 13:22: > A bit of pl/pgsql writer's block going on here ... > > Postgres complains "RETURN cannot have a parameter in function returning set" > in relation to the below. I don't really want to have to "RETURNS TABLE" > because that means I have to enumerate al

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

Beat Hoedl schrieb am 22.07.2021 um 11:52: > BTW: There is no virus scanner on the postgres folder and it's a productive > system, I cant just update. Quote from the Postgres homepage https://www.postgresql.org/support/versioning/ > For minor releases, the community considers not upgrading to

Re: How to restore roles into new Database server?

Sridhar Parepalli schrieb am 15.09.2021 um 15:53: Hello Pros, Is there a quick way to create roles from database server to another db server? You can use pg_dumpall with the --globals-only parameter to create a SQL script that contains all roles from the source server

Re: PostgreSQL - Ordering Table based of Foreign Key

FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48: I want to order tables based on the foreign key so that I can delete tables one by one without facing "ERROR: update or delete on table "table" violates foreign key constraint. DETAIL: Key is still referenced from table" You can create the foreign

Re: streaming replication different versions

Marc Millas schrieb am 06.10.2021 um 13:43: > on release 10,  I remember reading something like: streaming replication is > NOW upward compatible. > which could be understood as: its possible to have a master in rel 10 and a > slave in rel 11. No, that's not possible. For streaming replication

Misplaced double quotes in error message

Hello, consider the following table, query and error message: create table t ( "someColumn" int ); select t.someColumn from t; ERROR: column t.somecolumn does not exist Hint: Perhaps you meant to reference the column "t.someColumn". For someone proficient

Re: Determining if a table really changed in a trigger

Mitar schrieb am 26.10.2021 um 09:05: > I would like to test inside trigger_function if the table really > changed. I have tried to do: > > PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL > (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1; > IF FOUND THEN > ...

Re: database designs ERDs

Zahid Rahman schrieb am 28.10.2021 um 12:58: > I am looking for some database designs (ERD) on current and up to date > business scenarios for a project. > > By update to date I am referring to the DVD rental business ERD, > https://www.postgresqltutorial.com/wp-content/uploads/2018/03/printable-po

Re: Model clause and

Michael Lewis schrieb am 28.10.2021 um 22:44: On Thu, Oct 28, 2021 at 1:57 PM SQL Padawan mailto:sql_pada...@protonmail.com>> wrote: I presume that VIRTUAL GENERATED columns are on the to-do list? https://www.postgresql.org/docs/current/ddl-generated-columns.html Is this not what you want

Re: Incremental backup

Peter J. Holzer schrieb am 29.10.2021 um 15:43: >> Peter, Oracle instance manages collection of the databases and is ensuring >> recoverabilty using redo logs, which are completely analogous to WAL logs, >> if managed a bit differently. Let's not be nitpicking here. Oracle instance >> is completely

Re: General Performance Question

DAVID ROTH schrieb am 18.11.2021 um 15:15: > I am working on a large Oracle to Postgres migration. > The existing code frequently constructs a string and then uses Oracle's > "EXECUTE IMMEDIATE" to run it. > "EXECUTE" has the same functionality in Postgres. > > For example: > CREATE or REPLACE FUN

Re: SELECT fails to present result rows depending on the columns to show

Ilya Anfimov schrieb am 23.11.2021 um 09:31: >> but: >> >> sisis=# select * from titel_worte where desk = '2' and feldnr = 257; >> desknr | feldnr | desk | deskorg | gesanz | aufanz | katkey1 | katkey2 >> ++--+-+++-+- >> (0 row) >> >> sis

Re: Getting json-value as varchar

Andreas Joseph Krogh schrieb am 06.01.2022 um 13:28: Hi, in PG-14 this query returns "value" (with double-quotes): SELECT ('{"key":"value"}'::jsonb)['key']; ┌─┐ │  jsonb  │ ├─┤ │ "value" │ └─┘ (1 row) and this returns 'value' (without the quotes): SELECT ('{"key":"value"}

<    1   2   3   4   >