All Questions

Filter by
Sorted by
Tagged with
0
votes
0answers
7 views

Postgres tracking count of recent items in count table or by selecting count of recent records from history table

Which would generally be more efficient, or does it not matter? Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has ...
3
votes
1answer
39 views

VERY slow lateral join on relatively small database

My database consists of apps and their reviews (schema below). I'm trying to answer the following question: Given a series of dates from the earliest reviews.review_date to the latest reviews....
0
votes
1answer
14 views

Stopping cascading logical replication

Given two primary PG12 servers, A and B, let's setup logical replication from A to B. A --> B Both servers can and do receive writes, and don't use sequences or unique constraints so there is no ...
0
votes
0answers
25 views

Mysql randomly restarts with different args?

I'm facing a rather strange problem and I'm not sure how to make sense of it. It looks like the mysqld process is being started with different args by something other than systemd after the mysql ...
0
votes
0answers
13 views

Oracle - How to find out ADVM file system type?

Is there some built-in command from Oracle to find out ADVM file system type? I was looking for it in advmutil command or acfsutil command. But I don't find anything. In Linux usually I use file -s [...
0
votes
0answers
10 views

Windows and Linux from a DBA prospective

I'm considering moving into the DBA field and have begun reading in areas of SQL and Database design theory however I'm not sure how to approach Windows and Linux. I've passed the CompTIA A+ in 2006 ...
-1
votes
0answers
34 views

Help with homework about normalization

I have a table Service (customerId, waiterId, shift, meal). CustomerId and waiterId are key. Shift can be morning, afternoon or evening. A customer can only be served twice. I know that given a ...
0
votes
1answer
73 views

Inserting into table: either invalid datatype or duplicate column name error. (But the datatype is valid and there's no duplicate)

Spoiler: solution to this is here. the solution was really simple. I'm trying to insert data from an external table. Here's the code I'm trying to use: INSERT /*+ ignore_row_on_dupkey_index ( consruct ...
2
votes
2answers
60 views

SQL Server Unique Constraint on two columns with an exception

Hi all and thanks for your advice. Expense(SupplierID(Foreign Key), DocumentID(vchar)) I understand how to add a simple unique constraint on two columns. However, if DocumentID = 'NA', I would like ...
3
votes
1answer
108 views

Do I need to ANALYZE table after DROP INDEX?

In our postgres db we historically have many similar indexes (e.g. is_deleted boolean) that have quite bad selectivity value. Worse than that, sometimes query planner breaks and postgres starts to use ...
0
votes
1answer
18 views

Update table with sum from other table for the same id

Given two tables: Table src user_id | counter | text ------------------------ 5 | 1 | aa 7 | 7 | bb Table dst user_id | counter | text ------------------|----- 3 | 5 ...
2
votes
1answer
16 views

Performing SQL string manipulations on a single column

I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals. For example: Input table: ...
0
votes
1answer
43 views

Is it a fundamentally flawed design concept to put everything in the same database (with different schemas) and only ever connect to that database?

I've long wrestled with an annoying problem: If I put different "projects" in different databases, it immediately becomes a huge pain to connect to both, or to exchange information between ...
-6
votes
0answers
27 views

Is there yet an “auto index” feature in PostgreSQL? [closed]

I've struggled all my database life with indexes. Am I supposed to apply it on the ORDER BY column(s), on the ones being compared, a combination of them (and if so, what combination/order?), which ...
0
votes
1answer
31 views

Creating an Index based on Execution Plan

I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
1
vote
0answers
51 views

How can I write procedures that I can loop over to insert a series of files into my database?

My goal: Insert a series of csv files into my database by creating procedures for each individual table and then looping over them. My csv files will all be named very similar to this: ...
0
votes
0answers
45 views

Accelerated log growth

The log file for my SQL Server 2014 database is growing rapidly (currently in 32 GB in COMPLETE RECOVERY mode). This is creating storage issues on my server. The database has a set of job's with ...
0
votes
1answer
29 views

Why am i getting “missing right parenthesis” error when i try to LOG ERRORS when loading from an external table?

I've successfully created an error logging table BEGIN DBMS_ERRLOG.create_error_log( dml_table_name => 'enzyme', skip_unsupported => TRUE); END; / desc ERR$_ENZYME; Name ...
0
votes
0answers
17 views

Postgres “CREATE TABLE AS (SELECT …)” stuck

I am using Python with psycopg2 2.8.6 against Postgres 11.6 When I am running a query CREATE TABLE tbl AS (SELECT (row_number() over())::integer "id", "col" FROM tbl2) Code is ...
0
votes
1answer
15 views

String index for a list of values using IN($1,$2): will a btree index work?

We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for ...
0
votes
1answer
36 views

Actual Execution plan does not have memory grant info

I manually ran query in SSMS and captured actual execution plan of the query by enabling its icon in SSMS. I am not getting "Memory Grant" when I keep my mouse on SELECT OPERATOR. I didn't ...
0
votes
0answers
17 views

PostgreSQL 9.2 CDC implementation

I need to implement Change Data Capture(CDC) for PostgreSQL 9.2. I planned to use Debezium for this purpose but looks like it is only supports PostgreSQL 9.4+ https://debezium.io/documentation/...
0
votes
0answers
7 views

PostgreSQL rule to delete record before insertion, if the record has changed

I'm not sure it's possible to do this with a rule, but what I want to do is, when you insert a record: If it doesn't exist, just insert it as usual. If it exists, but each field is equal to what you'...
6
votes
1answer
164 views

Is there any side effect if select statements are wrapped in BEGIN TRAN … COMMIT?

I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN .....
2
votes
2answers
29 views

Failed to start mongo.service on ubuntu 16.04 after update from 4.0 to 4.2

I'm trying to update my MongoDB installation from 3.6 to the latest version [4.4] on Ubuntu16.04 using apt-get. Going from 4.0 to 4.2 I faced a problem during the startup process after updating the ...
-3
votes
0answers
33 views

Correct SQL query to compute customer support response time [closed]

Consider the following table: TABLE TicketMessage COLUMN TicketId INT COLUMN Sender VARCHAR (either 'customer' or 'staff') COLUMN SentAt DATETIME The goal of the query is to compute the average ...
0
votes
1answer
17 views

Why are my postgres logical replication workers crashing after syncing a certain amount of data?

I am trying to setup a logical replication between two postgresql 12 DBs run in Docker containers on AWS ECS. I gave the task 2 vCPU and 6 GB of ram so quite luxurious for a DB with ~1e7 rows in two ...
-1
votes
1answer
32 views

Complex SQL Agent Job

I would like to tun the Stored Procedures on the following dates. 1st sept 2020 1st Nov 2020 1st Dec 2020 1st Feb 2021 1st March 2021 1st May 2021 1st june 2021 1st august ...
0
votes
0answers
12 views

How to create a MongoDB index with isodate?

I've create and index for a query and its running is less then 1 second to retrieve millions of rows, but as soon as I add greater than ISODATE, it doesnt use indexes anymore. the query is this one: ...
0
votes
1answer
10 views

postgresql 12.3 convert existing DB to use case-insensitive collation?

postgresql 12.3 in a container with Alpine Linux 3.10 I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch. ...
3
votes
1answer
56 views

How to capture runtime parameters with extended event?

I use rpc_completed event in order to capture executed sprocs. I am also to view the statement and passed parameters by using the following way: right-click extended event -> Watch Live Data and ...
0
votes
1answer
18 views

How to validate negative values in query

Hi I am using postgresql in my project. I want to make a validation that if query returns negative then i want to change output to null. (to_char (NULLIF(coalesce(0::numeric,0) - (coalesce(26776.94::...
0
votes
0answers
16 views

T-SQL SYNONYM equivalent in PostgreSQL?

The title pretty much asks the question, so I'll begin with an example. I like to include a unique prefix in my table names, e.g. "con_contract". In T-SQL I can then set an synonym like ...
0
votes
0answers
8 views

postgresql data warehouse structure - hierarchy (coming from sql server)

I'm building a data warehouse in postgresql and I'm struggling with choosing a "right" structure for my datasets. Coming from a similar product in SQL Server, things are a lot more ...
1
vote
1answer
24 views

My Mariadb fails and shuts down after running with a timeout

My Mariadb version is $ mysql -V mysql Ver 15.1 Distrib 10.3.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 My machine is a ubuntu server: $ uname -a Linux mypc 5.4.0-42-generic #46-...
0
votes
1answer
14 views

INSERT .. ON CONFLICT DO UPDATE SET error

When I use the following query : cur.execute("""INSERT INTO TABLE (SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp FROM tmp_table) ON CONFLICT (ID) DO UPDATE SET Last_seen=...
0
votes
1answer
18 views

Delete rows on a table with cascading foreign keys

I want to delete rows on Table A but it has foreign keys on Table B and Table D. And Table B has foreign keys in Table E and Table D has foreign key in table F. Can I delete all the cascading records ...
0
votes
1answer
30 views

PL/SQL Statment ignored

I am really new in PL/SQL and I stuck over here. I want to write function which return allProject create or replace FUNCTION GETALLPROJECTS RETURN VARCHAR2 AS currentUserID varchar2(10); BEGIN ...
2
votes
1answer
43 views

Space Calculation for SQL Server Table: What's wrong with my query?

I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database: SELECT t.name AS TableName, p.rows, (sum(a....
2
votes
0answers
19 views

Validating SQL Server Change Tracking Synchronization

I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
-1
votes
0answers
12 views

Disadvantages of SQL Availability Group [closed]

What are the disadvantages or Cons of using Availability Group by you experience or not?
-1
votes
1answer
28 views

SQL alwayson AG Disaster Recovery

Need advise on below: I have 2 node alwayson AG on Primary Sync mode in the current configuration and fsw is in Primary DC only Actually during Switchover the entire DC 2 node and fsw will be down ...
-3
votes
1answer
45 views

Explain Like I'm Five : SQL (relational) and NOSQL (non-relational) databases [closed]

If I had to explain SQL & NOSQL to a college student within 1 minute : You're familiar with an Excel spreadsheet, right ? That is SQL: id, name, age, weight 1, Adam, 20, 150 2, Bob, 30, 160 ... ...
-2
votes
1answer
20 views

What are some good SQL Server Performance Metrics from System Views?

I am working on a project where I need to collect as many SQL performance metrics as possible, all from system views. I currently have collected, and in parenthesis the system view from which I have ...
0
votes
0answers
20 views

SQL to display one row as many based on one column

Is it possible to display one row as multiple rows based on the values in one column ON THE FLY? Example... Data: column1 (text) | column 2 (text) ----------------------------------- one ...
0
votes
1answer
59 views

Optimization for branched WHERE

I have a query that executes against a table that can grow to millions of rows. The query comes out of a QA tool that we use that is outside the standard functionality of the DB (as far as what is ...
0
votes
1answer
99 views

Query tuning update with hash flow distinct operator

Need some help in understanding the slowness in one of the UPDATE statements like below:- UPDATE TOP (100) xyz SET xyz.flag = 1 OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
1
vote
1answer
22 views

How to write concat query for multiple 'select into' statement in mysql stored procedures?

I am trying to assign values to variables in mysql stored procedures. Here is my code declare count int; declare ParamOrgId int; declare isPremium int; SET @sql = CONCAT(' select count(id) , orgid , (...
0
votes
1answer
17 views

MariaDB subqueries to same table and column resulting in several columns

I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB Tested and tested for hours and hours different approaches without success, one example is below. Some ...
-2
votes
0answers
23 views

¿Como puedo depurar los archivos temporales de una base de datos postgresql? [closed]

SELECT datname, temp_files AS "Temporary files" , temp_bytes AS "Size of temporary files" FROM pg_stat_database db

15 30 50 per page