All Questions

Filter by
Sorted by
Tagged with
1 vote
0 answers
4 views

Postgres 14 and pg_trgm index on primary key column make join operations super slow

I've just upgraded my postgres instance from v11 to v14. There was an interesting problem because we have a trigram index on order_id column. This new feature makes our simple join query on that ...
user avatar
  • 226
0 votes
0 answers
6 views

Delete all data of all tables of all user databases in MariaDB

In our development environment, we want to have a DeleteData.sql query that developers can use to delete all of the data of all tables of all databases of their MariaDB docker instance. For example, ...
user avatar
1 vote
1 answer
16 views

EXCLUDE CURRENT ROW cripples performance of window functions

I'm working with a customer's database where there are rows representing instances of Users, Activities (table named ActivityRecords), and ActivityUsers in correspondingly named tables, where ...
user avatar
  • 11
0 votes
0 answers
19 views

Return Multiple Stored Procedures Outputs into one table

I have a handful of stored procedures which have a SELECT COUNT(*) and then return the number that is that count I want to write a new SP that has all of the values in a table something like: Sp1 | ...
user avatar
1 vote
0 answers
15 views

Why is this `pgbench` benchmark faster on postgresql 11 than on postgresql 14?

To check postgresql performance, I ran the following benchmark with varying postgresql versions and filesystems: pgbench --initialize --scale=50 bench # Run for 3 minutes postgres pgbench --client=10 -...
user avatar
0 votes
1 answer
27 views

Postgres: Delete but keep x amount of rows per input

Let's say I have the following table, with data: +----+------+--------+------+ | Id | User | Item | Time | +----+------+--------+------+ | 1 | 1 | Banana | D+1 | | 2 | 1 | Banana | D+2 | | ...
user avatar
0 votes
1 answer
19 views

any suspicious result in that explain analyze?

I have the following query : SELECT "message"."id" AS "message_id", "message"."conversation_id" AS "message_conversation_id" FROM "...
user avatar
  • 101
0 votes
0 answers
17 views

Oracle: why does this DDL yield a different result in production (reordering of columns)?

Problem description This week we had an issue in production, where an SQL script that was executed ended up with a different result than what we had tested before locally, in Docker and on our ...
user avatar
  • 173
0 votes
1 answer
86 views

SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues

I have a stored procedure that takes too long sometimes even though I have added all suggested indexes. I keep getting the Query had to wait for memory grant The query plan can be found here: Query ...
user avatar
0 votes
0 answers
7 views

Can I mix Galera-3 and Galera-4 nodes?

Say I have three servers node1 192.168.100.201 node2 192.168.100.202 node3 192.168.100.203 on node1 and node2 is running mariadb 10.5 and on node3 still 10.1 and I need that version there still, but ...
user avatar
  • 101
0 votes
0 answers
13 views

Different time sysdate and current_date when connect to database since application server

I have a problem with the time that Oracle gives me when working with applications. I currently connect from an application server (apx1) to a server containing Oracle 19c (bdx1) for database ...
user avatar
  • 131
1 vote
1 answer
15 views

Mysql update json if-else exist

I want to update my raw with if-else condition. Here is my sql code; update posts set like = if(json_contains(like,2,'$'), json_remove(like,replace(json_search(like,'one',2),'&...
user avatar
1 vote
1 answer
30 views

How to disable MYSQL Replication?

I have 2 databases which are replicating each other with no issues. I required a test database server and to avoid installing OS etc, I started a new image of one of the servers (with different ...
user avatar
  • 113
0 votes
1 answer
21 views

Using having after grouping by a rand is not returnig expected results

AFAIK having statement is executed after group by (if present, otherwise it works like a simple where), so it can be used to filter grouped rows, but i'm facing a strange behavior issue I can't ...
user avatar
  • 101
3 votes
1 answer
112 views

Postgres order by with multiple columns

So imagine I have a result set that looks something like this: +----+------------+------------+ | Id | Date_One | Date_Two | +----+------------+------------+ | 1 | 2022-05-12 | null | | 2 |...
user avatar
0 votes
1 answer
25 views

How does Wikipedia store article diffs (or any site which contains lots of text diffs per record)?

Looking at the Obama diffs on Wikipedia article history, there are probably thousands of diffs. Clicking on a specific older diff took about 3-5 seconds to load, as opposed to the more snappy ...
user avatar
  • 261
0 votes
0 answers
14 views

parse JSON keys preserving ordinality

I had heard that in postgresql, the json type preserves declared ordering unlike it's fully-parsed counterpart jsonb. I want to get the ordinal position of each key in a document as supplied by the ...
user avatar
1 vote
1 answer
16 views

Is there a way to get AWS Aurora Mysql 5.7 compatible to cause timestamps to be updated to current timestamp when null is explicitly passed in?

All examples are technically from AWS RDS Aurora Mysql compatible, however I believe the issue is the same for non-aws mysql. In Mysql 5.6 and 5.7 with the exact same values for sql_mode (the 5.6 ...
user avatar
  • 111
0 votes
1 answer
27 views

PostgreSQL DB size estimation

We are doing POC for DB selection. Based on our use case we got three recommendation Postgres Cassandra Azure SQL We are not going with Cassandra since we are not matching minimum requirements and ...
user avatar
0 votes
1 answer
18 views

Can you create SQL database transactions, and run things outside of those transactions while the transaction is opened?

I am sort of a newbie when it comes to transactions in databases, so don't have much experience with them. I am in the situation where I have a jobs table, which is a job queue, and I want to process ...
user avatar
  • 261
0 votes
0 answers
8 views

SingleStore: Sign Up Log In Cannot get source metadata for pipeline. Failed to create consumer: ssl.ca.location failed: No further error info

below is the command that i am using, but when i create the kafka pipeline command i get the below error: ERROR 1933 ER_EXTRACTOR_EXTRACTOR_GET_LATEST_OFFSETS: Cannot get source metadata for pipeline. ...
user avatar
0 votes
0 answers
10 views

SingleStore Execute Immediate a SP/Func inside SP from a query table

I’m having trouble to execute immediate a SP inside a SP from a table. because i have a lot of SP stored in a table (as a data/string). in oracle we can use “select sp_1(:1,:2) from dual” and running ...
user avatar
0 votes
0 answers
17 views

MySql query not using spatial index

We have the below query that we are calling to find all rows within another table's row geometry. When I do a explain, it shows the spatial index spidx_table2 as a potential index, but says it is ...
user avatar
  • 146
0 votes
0 answers
21 views

Filtering timestamp record by range

So i am new at optimizing SQL and here is my query: COUNT (SI_2_STRIPPED.RESOURCE_UUID) AS COMPLETIONS FROM (SELECT SI2.STATISTIC_ID, SS.RESOURCE_UUID FROM ...
user avatar
2 votes
3 answers
61 views

SQL Server Select orders which have all of a set of items

I have the following tables, Order and OrderLine: Order: id | total ---------- 1 | 55.09 2 | 62.42 OrderLine: order_id | line_number | item | qty ---------------------------------------...
user avatar
0 votes
1 answer
21 views

SQL Server collation and its equivalent in Postgres

Legacy system, backed by SQL Server, has unique indexes using collation Latin1_General_CP1_CI_AI. It compares things using charset Latin 1, encoded using CP-1252 (CP1 part), case insensitive, accent ...
user avatar
  • 101
1 vote
0 answers
28 views

MS Access SQL - Left Join (or sub-query?) most recent related record

Hello Database Administrators. Thank you so much for your help with this issue. It blows my mind that something so simple can be so complicated. I have an MS Access database with two tables. The first ...
user avatar
  • 11
0 votes
0 answers
6 views

MySQL InnoDB Cluster config - configure node address

I first asked this question on Stack Overflow but I woke up this morning to the realization that it belongs over here. I'm setting up an InnoDB Cluster using mysqlsh. This is in Kubernetes, but I ...
user avatar
  • 101
0 votes
0 answers
13 views

How to find out the lock details in mysql replica

Currently we are deleting old records from the multiple tables. In the async replication one of the replica is lagging. From the show engine innodb status\G 2 lock struct(s), heap size 1136, 1 row ...
user avatar
  • 99
-2 votes
0 answers
15 views

SQL Server 2017 Standard Licensing Terms

We have a VM server with 32 cores and SQL Server 2017 Standard has been installed. My question is do I need to license all the 32 cores or I can just license the number of cores as per my requirement ...
user avatar
  • 1,003
0 votes
1 answer
19 views

Oracle XE fails on HammerDB benchmark

I have Oracle XE 21c and HammerDB 4.4. The task is to benchmark database. Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 Well, because of that I've done the ...
user avatar
1 vote
1 answer
29 views

join condition order

Given a simple query like this SELECT * FROM table_a a inner join table_b b on b.id_from_a = a.id Does the order of the on clause matters? In the example above I always put the recently joined ...
user avatar
1 vote
2 answers
17 views

2 tables that use the same sequence to generate primary key in postgres

Could I use the same sequence to generate the primary keys on two separate tables for instance like this? CREATE TABLE IF NOT EXISTS public."user_Registered" ( id integer NOT NULL ...
user avatar
  • 227
1 vote
1 answer
24 views

Generating an ID from primary key sequence without adding a row

I have the following two tables where I store users and their addresses. I have a problem relating to the order in which I receive and can save data from the user. At first the user will supply only ...
user avatar
  • 227
0 votes
1 answer
47 views

MySQL Duplicate entry for key unique key

I have a table with a unique key which is a combination of 4 columns. Column A,B,C and D. The values in the columns are like this: Column A Column B Column C Column D 111000 150100_XYZ_XY02 150100-...
user avatar
0 votes
0 answers
24 views

Remove requirement to specify [default] MS SQL port in vb and vbscript connection strings

I need to 'swap' an old database server (Windows Server 2008, SQL Server 2005) with a newer/better one (Windows Server 2012, SQL Server 2008) I tested this swap yesterday and everything worked on the ...
user avatar
  • 619
-1 votes
1 answer
22 views

Partition scan order in Postgres

I have created a natively partitioned table in Postgres and when trying to query this, Postgres is scanning the partitions in ascending order. The partition key is a timestamp column. For my use case, ...
user avatar
5 votes
2 answers
332 views

SQL Return Table with where clause and occurrences of value of total table

I am stumped trying to figure out how to achieve this. I know how I would achieve it in C#, but not SQL. Say I have the following table: ID Name RouteId 1 Bob 1001 2 Bob 1002 3 Ana 1001 4 Jim ...
user avatar
-3 votes
0 answers
12 views

How to create package in postgresql

`CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER DEFAULT 10) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE ...
user avatar
0 votes
1 answer
16 views

Does a timeout on an existing record prove that record exists?

If I attempt to edit a record that may (or may not) exist, but get a timeout after a few seconds, can I take that as evidence that the record exists? Timeouts occur if another transaction hasn't ...
user avatar
0 votes
0 answers
12 views

Count only once the orders_id with a payment status

I have a table where i have order_id with multiple payment status. I want to count only if an order_id has one status not count in double. SO for example order_id 4 has 3 status and I want it to be ...
user avatar
1 vote
0 answers
41 views

Past date shows for CURRENT_TIMESTAMP and GETDATE() and GETUTCDATE()

Why is that that suddenly in SQL Server Management Studio when I query CURRENT_TIMESTAMP and GETDATE() and GETUTCDATE(), it gives me a past date? SELECT SYSDATETIME() ,SYSDATETIMEOFFSET() ,...
user avatar
3 votes
2 answers
215 views

Nest a SQL file into another in SQL Server

I have a simple SQL file I'm using to perform some test: DECLARE @VAR1 AS VARCHAR(100) = '12345' DECLARE @VAR2 AS INTEGER = 54321 WAITFOR DELAY '00:00:02' INSERT Orders SELECT @VAR1 AS COL1, @...
user avatar
  • 365
0 votes
1 answer
19 views

Retrieving Data from current DB and archived DB

With one query,I want to fetch "COLLECTIVE/COMBINED" data from current DB & archived DB with multiple conditions and several joins. SELECT * FROM [Database].[dbo].[Current_DB_TableName] ...
user avatar
0 votes
0 answers
11 views

POSTGRESQL table replication between 2 servers

I want to to replicate A TABLE from one server A to another B so that A and B always in-sync. This table on server A has like 4 - 5 million rows, whereas the table B is empty. What I want to do is ...
user avatar
  • 1
0 votes
1 answer
26 views

Login failed at creating Linked Server

I want to create a linked server on database server B to database server A in a development server where just the SQL Management Studio is installed. All three servers are in the same domain. The ...
user avatar
  • 111
8 votes
1 answer
107 views

What can I add to an Extended Event to return correct row_counts?

I'm running an Extended Event session in SQL Server 2017 that's only recording module_end events for a specific database. The reason for this trace is two-fold: I'm wanting to see the frequency (or ...
user avatar
  • 181
3 votes
2 answers
199 views

How to store and query strings matching prefix or suffix in PostgreSQL?

Say I have 1 million words each in 100 different languages (so 100 million words, each with alphabets ranging from 10 characters to 200 characters, although Chinese has 80k "characters" ...
user avatar
  • 261
0 votes
0 answers
5 views

Are there a logs to harvest on Oracle Active Data Guard instance?

We are trying to see if there is a way for cloud/SaaS-based( like AirByte or Fivetran) CDC/replicate provider to harvest logs on Oracle active data guard instance. Any ideas?
user avatar
  • 1
3 votes
1 answer
57 views

Update all instances of key inside nested JSON array

I'm trying to update a complex JSON document stored in a table in CockroachDB. The CockroachDB query language is mostly Postgres compatible except for the lack of procedural code, so a Postgres answer ...
user avatar
  • 31

15 30 50 per page
1
2 3 4 5
1928