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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: Move from MySQL to PostgreSQL

basti schrieb am 29.03.2023 um 14:57: > https://github.com/volkszaehler/volkszaehler.org/blob/master/lib/Util/Aggregation.php > > There are several problems. > > One of that ist the 'REPLACE INTO'. Most probably INSERT ... ON CONFLICT > An other problem is '@prev_timestamp := timestamp' That's t

Re: PostgreSQL vs MariaDB

Inzamam Shafiq schrieb am 24.03.2023 um 12:07: Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)? As others have pointed out, this is really hard to quantify. For large datab

Re: Oracle to PostgreSQL Migration

Inzamam Shafiq schrieb am 20.03.2023 um 13:57: > We have an Oracle DB which is around 1TB and we want to migrate to > PostgreSQL that have a new table structure, so we want to perform > data transformation and real time CDC from Oracle to PostgreSQL. Do > we have any good open source tool to achiev

Re: nested xml/json to table

Wim Bertels schrieb am 17.03.2023 um 11:05: > what would be the general idea: "easily" convert an hierarchical > structure like json or xml to a table; for example creating columns by > appending the key-names when going doing down the three, using null for > empty values, adding more columns as ne

Re: PostgreSQL

Joseph Kennedy schrieb am 07.02.2023 um 12:02: > I would like restrict access to sensitive or restricted information > for some users (eg. hide data of one or more clients for some > database users). > > PostgreSQL allows to create security policy as Row-Level Security, > policies based on the quer

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

Sebastien Flaesch schrieb am 06.02.2023 um 18:17: Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY| Is there any built-in function that returns the underlying sequence name used for such column? Otherwise, an SQL query to return the sequence name?

Re: moving a database to a new 15.1 server

Matthias Apitz schrieb am 01.02.2023 um 13:24: > $ psql -Ulbs_lbsoclc01_dev_r1_dbo_u lbs_lbsoclc01_dev_r1 > psql (15.1) > Type "help" for help. > > lbs_lbsoclc01_dev_r1=# > lbs_lbsoclc01_dev_r1=# \d > Did not find any relations. > lbs_lbsoclc01_dev_r1=# \d dbo.accession_index >

Re: DBeaver session populating pg_stat_activity.backend_xmin

Dirschel, Steve schrieb am 25.01.2023 um 20:36: When I connect to the database through DBeaver with those 2 default settings changed and find that session in pg_stat_activity column xact_start is populated along with backend_xmin. Those get populated just by logging in. As you found out in the

Re: Get table catalog from pg_indexes

Igor Korot schrieb am 27.11.2022 um 23:13: >> I've never heard of a database referred to as a catalog. (That's always >> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) > > In the ODBC terminology the DB is usually referenced as catalog. JDBC uses the same term and t

Re: Calculating average block write time

Laurenz Albe schrieb am 18.11.2022 um 16:51: I can easily calculate the average block read time using pg_stat_database and divide blk_read_time by blks_read. While there is a column blk_write_time, it seems that there is no cummulative measure for the total number of blocks written. Any ideas

Re: Calculating average block write time

Thomas Kellerer schrieb am 04.11.2022 um 10:19: > I can easily calculate the average block read time using > pg_stat_database and divide blk_read_time by blks_read. > > While there is a column blk_write_time, it seems that there is no > cummulative measure for the total number of

Re: pg_restore remap schema

Tom Lane schrieb am 08.08.2022 um 20:22: > Guillaume Lelarge writes: >> Le lun. 8 août 2022 à 18:28, Fabrice Chapuis a >>> Is a development in progress to add this option > >> Nope, never heard of someone working on this. > > People have asked for such a thing before, but it'd be quite difficult

Calculating average block write time

I can easily calculate the average block read time using pg_stat_database and divide blk_read_time by blks_read. While there is a column blk_write_time, it seems that there is no cummulative measure for the total number of blocks written. Am I missing something, or is this simply not tracked (a

Re: High CPU usage

ertan.kucuko...@1nar.com.tr schrieb am 20.10.2022 um 21:59: Hello, I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very high CPU usage on my Linux system as below PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 2357756 postgres 20 0 244103

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

gogala.mla...@gmail.com schrieb am 19.10.2022 um 01:46: Amazon, lead by Kevin Closson, the guy who has famously designed Oracle Exadata among other things, even came up with the recipe how to migrate it to Postgres: https://aws.amazon.com/blogs/database/migrating-oracle-autonomous-transactions-t

Re: Postgres 15 upgrades and template1 public schema

Bruno Wolff III schrieb am 19.10.2022 um 22:36: I noticed when I did an upgrade from Postgres 14 to 15 that the public schema in template1 was still owned by postgres instead of pg_database_owner. I was expecting it to change because the release notes said that new database clusters would have

Re: pg_upgrade to 15 fails on Windows because of xml_is_well_formed()

Tom Lane schrieb am 13.10.2022 um 21:01: When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails with: pg_restore: error: could not execute query: ERROR: could not find function "xml_is_well_formed" in file "c:/Program Files/PostgreSQL/15/lib/pgxml.dll" I don't understa

pg_upgrade to 15 fails on Windows because of xml_is_well_formed()

When trying pg_upgrade to upgrade Postgres 14 to 15 on Windows 10 this fails with: pg_restore: creating FUNCTION "public.xml_is_well_formed("text")" pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 647; 1255 23216 FUNCTION xml_is_well_formed("text") postgres pg_restore: error: could

Re: Corrupted Postgresql Microsoft Binaries

Hillary Masha schrieb am 23.08.2022 um 20:58: I downloaded the microsoft postgresql binaries for versions 14.5, 13.8, 12.12, 11.17, 10.22 from https://www.enterprisedb.com/download-postgresql-binaries and found that there was an error with opening the zip files. Does anyone else use these files a

Re: Postgres question

ajay venki schrieb am 17.08.2022 um 14:39: > Thanks. I am looking forward to install pgloader tool to migrate my MS SQL > data to postgres.  > I tried searching it online and the instructions were not clear to me. Is > there any article or video which talks about pgloader installation on windows

Re: Copying records from TABLE_A to TABLE_B (in the same database)

Ron schrieb am 02.08.2022 um 20:37: AWS RDS Postgresql 12.10 There are no indices or constraints (except for NOT NULL) on table_a. The two ways that I know are:     INSERT INTO table_a SELECT * FROM table_b; and     \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);     \COPY table_b

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

Sebastien Flaesch schrieb am 19.07.2022 um 18:50: > Tom, > > /If that's the behavior you want, you can build it out of standard SQL > facilities (e.g. update a one-row table). > / > > Can you elaborate please? > > Do you mean the code should use an UPDATE on a one-row table to acquire a >

Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

Bryn Llewellyn schrieb am 04.06.2022 um 03:41: Am I missing a use case where an object with a key-value pair with a JSON null value is meaningfully different from one where the key is simply absent? It seems the JSON specification doesn't actually define equality. But the JSON patch RFC 6902[1]

Re: How to drop a subscription inside a stored procedure?

Adrian Klaver schrieb am 10.06.2022 um 16:58: On 6/10/22 05:57, Thomas Kellerer wrote: I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. However, the "drop subscription" part results in this error: ERROR:  DROP SUBSCRIPT

How to drop a subscription inside a stored procedure?

I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. For that, I essentially want to drop and re-create the subscription. In order to be able to do that, the tables need to be empty. So the approach is: Run a query to get all replicated t

Re: Automatic PK values not added to new rows

Rich Shepard schrieb am 25.05.2022 um 20:15: On Wed, 25 May 2022, Adrian Klaver wrote: Do: select * from people_person_nbr_seq; and report back the results. Adrian, Huh! bustrac=# select * from people_person_nbr_seq;  last_value | log_cnt | is_called +-+---

Re: TO_DATE function between PostgreSQL 8.2 and 9.4

gzh schrieb am 17.05.2022 um 17:55: I have had a Perl Website working for 7 years and have had no problems until at the weekend I replace my database server with a newer one. Database server (old): PostgreSQL 8.2 32bit Database server (new): PostgreSQL 9.4 64bit I run following sql in Postgre

Re: Unexpected zero results

Viliam Ďurina schrieb am 23.03.2022 um 17:56: Hello all, I'm experimenting with JSON-path functions, and stumbled upon this query:   SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)') It returns 0 rows. I expected it to return one row with `null` value. Isn't it the case that `SELECT ` shoul

Re: Strange results when casting string to double

Carsten Klein schrieb am 16.02.2022 um 14:27: > I'm using several (now unsupported) PostgreSQL 9.3.24 servers on > different (ancient) Ubuntu 14.04 LTS machines. On only one of those > servers, I get strange/wrong results when converting a string into a > double value: > > SELECT 1.56::double preci

Re: Turn a json column into a table

Shaozhong SHI schrieb am 15.02.2022 um 07:23: > There is a JSON column in a table. It contains key value pairs, just > like a dictionary. What is the best way to turn this column into a > data table? jsonb_each() is one option: select j.* from the_table t cross join jsonb_each(t.the_c

Re: DELETING then INSERTING record with same PK in the same TRANSACTION

David G. Johnston schrieb am 09.02.2022 um 21:47: You cannot defer uniqueness checks to transaction commit so either it is going to fail on the insert or it will not fail at all. You can defer unique constraints, but not primary key constraints. create table t ( id integer ); alter table t

Re: Why not used standard SQL commands?

Ali Koca schrieb am 08.01.2022 um 19:25: I'm seeing \dt used for "show tables", \l used for "show databases". Why not standart SQL syntax words? Why specified PostgreSQL commands? I can't figure out that. The only thing the SQL standard defines to gain access to information about tables, schema

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"}

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

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

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

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

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

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

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: Script checking to see what database it's connected to

Julien Rouhaud schrieb am 22.02.2021 um 02:19: >> The output: >> $ psql12 -f test_pg.sql >> t >> connected to postgres >> got here >> psql:test_pg.sql:15: ERROR: syntax error at or near "exit" >> LINE 1: exit > > Well, the supported commands did work. You should probably look at > https://www.pos

Re: JSONB_AGG: aggregate function calls cannot be nested

Alexander Farber schrieb am 20.02.2021 um 19:39: So I am trying: # SELECT                 JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day,                 JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int) AS completed,                 JSONB_AGG(SUM(CASE

Re: Slow while inserting and retrieval (compared to SQL Server)

sivapostg...@yahoo.com schrieb am 17.02.2021 um 14:27: > We use datawindows. Datawindows will send the required DML > statements to the database. > And it sent in format 1 . > > IN start of the application, Autocommit set to True. > Before update of any table(s) > Autocommit is set to False > Inse

Re: Slow while inserting and retrieval (compared to SQL Server)

sivapostg...@yahoo.com schrieb am 17.02.2021 um 13:01: > To populate some basic data we try to insert few records (max 4 > records) in few tables (around 6 tables) from one window. We feel > that the insert time taken is longer than the time taken while using > Sql Server. We tested almost a sim

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

Guy Burgess schrieb am 15.02.2021 um 11:52: > The mystery now is that the only process logged as touching the > affected WAL files is postgres.exe (of which there are many separate > processes). Could it be that one of the postgres.exe instances is > holding the affected WAL files in use after anot

Re: Turn jit off for slow subquery in Postgres 12

Andrus schrieb am 16.02.2021 um 07:48: > Last year I posted testcase [1] which shows that using jit makes execution > magnitude slower ( 2.5s vs 0.4 sec) in typical shopping cart application > product search in Postgres 12. > > There are also other reports on this [2,3]. > > I tried to turn jit of

Re: Postgres 9.4 Needed

Taranum Fatima schrieb am 09.02.2021 um 01:59: > I have to install Postgres 9.4 which is compatible with Jira 7.2.2 .  > > We have to migrate the data from older version to New  but before we need to > restore the Data . Hence, Postgres 9.4 is needed. > > I do not find the 9.4 repo or postgresql94

Re: Foreign table performance issue / PostgreSQK vs. ORACLE

Markhof, Ingolf schrieb am 29.01.2021 um 13:56: > The set-up basically is a production database and a reporting > database. As names indicate, the production database is used for > production, the reporting database is for analysis. On the reporting > database, the only way to access product data i

Re: Postgres blog sites centrally

Yambu schrieb am 28.01.2021 um 16:21: > Is there a central place where i can get postgres blogs as they are > written by different blog sites That would be https://planet.postgresql.org/

Re: interval data type

James B. Byrne schrieb am 21.01.2021 um 22:22: What is the difference between interval(3)[] and simply interval(3)? Where in the documentation is the [] syntax discussed? The [] denotes an array of intervals. So in a column defined as interval[] you can store multiple intervals, just like in

Re: Accounting for between table correlation

Atul Kumar schrieb am 15.01.2021 um 16:29: As per Ron, you are not supposed to ask your questions here. As According to him, we should keep on doing research on internet rather than asking for support directly even you have done enough research and until unless “Ron” won’t be satisfied you have

Re: upgrade postgres 9.5 to 9.6

Atul Kumar schrieb am 15.01.2021 um 14:17: > I want to upgrade my server from postgres 9.5 to 9.6, but my DB size > is in TBs and I want to do it in minimum downtime (2-3 hours) so > please help me how should I perform it. > > > Please share the document, if possible, it will be grateful. pg_upgra

Re: Suggestion: provide a "TRUNCATE PARTITION" command

Michael Lewis schrieb am 08.01.2021 um 17:47: > For me, it seems too easily error prone such that a single typo in > the IN clause may result in an entire partition being removed that > wasn't supposed to be targeted. I don't see how this is more dangerous then:      del

Re: Suggestion: provide a "TRUNCATE PARTITION" command

Michael Lewis schrieb am 08.01.2021 um 16:32: On Fri, Jan 8, 2021 at 2:36 AM Thomas Kellerer mailto:sham...@gmx.net>> wrote: Hello, I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres? Especially during bulk loads it's more efficient to

Re: Suggestion: provide a "TRUNCATE PARTITION" command

legrand legrand schrieb am 08.01.2021 um 14:57:> maybe a naïve plpgsql as proposed in https://www.postgresql-archive.org/Partitionning-support-for-Truncate-Table-WHERE-td5933642.html may be an answer Yes I am aware of that (and that's what I have used so far) - I just thought it would make li

Suggestion: provide a "TRUNCATE PARTITION" command

Hello, I wonder if it made sense to add a "TRUNCATE PARTITION" command to Postgres? Especially during bulk loads it's more efficient to TRUNCATE a partition if I know I want to replace all rows, rather than doing a DELETE. Currently this requires dynamic SQL which isn't always feasible (and mi

Re: CROSSTAB( .. only one column has values... )

Adam Tauno Williams schrieb am 05.01.2021 um 16:46: I'm using the crosstab feature and do not understand why I am only getting values in the first column. The query: SELECT date_trunc('month', t2.value_date) AS invoice_date, t1.value_string AS invoice_type COUNT(*) FROM document d LE

Re: How to REMOVE a fillfactor set by accident?

Thorsten Schöning schrieb am 31.12.2020 um 21:13: Is there some way to REMOVE the fillfactor where it is set, so that Postgres applies it's defaults? Would be great to have an output of NULL everywhere where fillfactor is output instead of sometimes e.g. 100 and more often NULL. I've already tri

Re: Improving performance of select query

Karthik Shivashankar schrieb am 14.12.2020 um 12:38: > I have a postgres(v9.5) table named customer holding 1 billion rows. > It is not partitioned but it has an index against the primary key > (integer). I need to keep a very few records (say, about 10k rows) > and remove everything else. > > /ins

Re: PL/java

Rob Sargent schrieb am 07.12.2020 um 23:22: > If I'm tracking correctly I can say the java implementation of > CopyManager is, to me, blindingly fast. So if the E and the T are in > java then certainly the L can be also. I can confirm that using CopyManager in JDBC has pretty much the same perfo

Re: Deferrable FK not behaving as expected.

Ron schrieb am 07.12.2020 um 19:15: Referenced by:     TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"   FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think if you only mention "deferrable" this is the same as "deferrable ini

Re: Is the Halloween problem an issue in Postgres

guy...@icloud.com schrieb am 02.12.2020 um 21:27: The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE clause of the same query. I just saw a presentation from someone about how in SQL Server he recommended writing changes to a tem

Re: Calling Procedure from another procedure in Postgres

Muthukumar.GK schrieb am 02.12.2020 um 11:20: > I need to call the procedure(not function) and insert the records > into a temporary table from another procedure in postgres. When > executing the procedure 'Sampleproc2',I got some below syntax error. > Kindly let me know whether postgres supports

Re: Determine if postgresql cluster running is primary or not

Raul Kaubi schrieb am 20.11.2020 um 09:53: > CentOS 7 > Postgres 9 to 12 > > For monitoring purpose, I would like that certain scripts are only run in > primary server. > So I am looking ways to universally discover if postgresql cluster that is > running is primary or not. As the standby will b

Re: Multiple result set to be returned in procedure/function

Daniel Verite schrieb am 19.11.2020 um 13:06: >>arthur=> select * from get_results(); >>get_results >> >> >> > > Friendlier names may be used by assigning them in the function, > i.e. plpgsql does support: > > declare > c1 refcursor := 'mycursorname'

Re: Multiple result set to be returned in procedure/function

Muthukumar.GK schrieb am 19.11.2020 um 09:27: > is it possible to return Multiple results set from procedure/function > on single execution. Please advise me on this. we are planning to > migrate things from sqlserver to postgresql where my existing sql > stored procs will return multiple result se

Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 17:00: I actually have version 9.3.17 😬 On Wed, 18 Nov 2020 at 15:55, Thomas Kellerer mailto:sham...@gmx.net>> wrote: Snjezana Frketic schrieb am 18.11.2020 um 11:29: > I have a column called |targeting| in a table called |

Re: How to select values in a JSON type of column?

Snjezana Frketic schrieb am 18.11.2020 um 11:29: > I have a column called |targeting| in a table called |campaigns| . > [...] > and I need to select all the |ids| in |includes|. > Currently, I am doing it like this  > > SELECT  > |targeting#>'{targets,0,audienceSegments,0,includes,0,segments,allOf,

  1   2   3   4   >