Conditional DELETE and INSERT in PostgreSQL (2021-06-18)

SQLServerCentral Newsletter for June 18, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Conditional DELETE and INSERT in PostgreSQL]( - [Azure Databricks Access Controls and Row Level Security]( - [Branching and Merging in Database Development using Flyway]( - [From the SQL Server Central Blogs - Setting up a Full Text Index–#SQLNewBlogger]( - [From the SQL Server Central Blogs - My journey with hybrid technologies. Where it started, how it’s going, and where it ends. : T-SQL Tuesday #139]( Question of the Day - [Default $DebugPreference]( The Voice of the DBA  Daily Coping Tip Take time to notice something beautiful today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Evaluting a DBA Interviewing is difficult for many organizations. Often they don't have a consistent process, and very often the people that do initial screening don't know much about what a DBA does. Many of the somewhat trivial questions I see on Internet lists (what's a clustered index?) are suited for someone getting started in the business, not for hiring an experienced DBA. Sure, an experienced person ought to be able to answer those questions, but the real world often requires more nuanced questions that relate to the situations we find ourselves in. I found [a list from Joey D'antoni]( recently, which has lots of open ended questions, designed to allow someone to talk about what they know and think, giving the interviewer the chance to gauge their level of expertise. I especially like the questions around tuning and configuration. Tell me what you've done in the past and why. The way someone can tell you a story about their experience and then relate this to practice solutions is good. There is no good way to talk about these situations if you haven't lived them. You might read someone's blog, but a few followup questions on details will show whether you really understand the solutions or not. Of course, you need a good interviewer that knows something about these topics to understand whether the answers make sense or not. An HR person or manager isn't likely going to be able to judge how a candidate performs. While a developer or sysadmin might be able to gauge whether the person has spent time with SQL Server, if you need a really experienced person, you need someone equally experienced to interview them. Hiring is difficult, but with more junior or intermediate positions, it might not matter as much. Evaluate whether the person has some knowledge and fits with your team. Can they work with others, which isn't the same as just agreeing with them. For more senior people, references and networking matter a lot. If others recommend them (or don't), that says a lot. It's good to initially decide if this person is a good fit, and then engage someone like Joey or [Glenn Berry]( (or [Tim Mitchell]( in the BI space) that might provide a better evaluation of your short listed candidates. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Conditional DELETE and INSERT in PostgreSQL]( sabyda from SQLServerCentral Introduction We are comfortable with inserting/deleting record(s) into/from a table. But we sometimes get confused when we need to insert or delete records conditionally from a table. Let us look at an example. Example scenario Suppose we have a table, called ALL_EMPLOYEES, which contains all employees' information who are working or had ever worked for […] [Azure Databricks Access Controls and Row Level Security]( Additional Articles from MSSQLTips.com Azure Databricks has a number of Access Controls and Row Level Security options that we will explore in this article. [Branching and Merging in Database Development using Flyway]( Additional Articles from Redgate How to exploit the branching and merging capabilities of Git for scaling up team-based development, when doing Flyway migrations. From the SQL Server Central Blogs - [Setting up a Full Text Index–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I saw a question recently on Full Text... From the SQL Server Central Blogs - [My journey with hybrid technologies. Where it started, how it’s going, and where it ends. : T-SQL Tuesday #139]( Kenneth.Fisher from SQLStudies One of my favorite people is hosting TSQL Tuesday this month. Ben Weissman (blog|twitter). He wants us to talk about ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Default $DebugPreference What is the default value for the $DebugPreference variable in Powershell? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) A Mixed Matrix in R I want to create a matrix in R that looks like this: [,1] [,2] [,3] [1,] "1" "A" "!" [2,] "2" "B" "@" [3,] "3" "C" "#" How do I do this? Answer: matrix(c(1,2,3,'a', 'b', 'c','!','@','#'), nrow = 3, ncol = 3) Explanation: While you cannot mix data types in a matrix, you can have numbers and letters (and symbols) if they are all character values. The matrix() function will allow you to create the matrix and include the characters you want. If you do not include the nrow and ncol values, this is all in one column. Ref: R Matrices - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Index combining question]( - I ran a report and execution plan suggested a 'missing index' with 4 index key columns and 4 included columns. I created the index and ran the report again it gave me 'missing index' suggestion again with now 2 key columns and 2 included columns which were part of the missing index of the first […] SQL Server 2016 - Administration [Reading multiple audit files]( - Is it possible to read multiple audit files from multiple servers all placed under a single folder ? SELECT * FROM sys.fn_get_audit_file('E:\ABC\ServerAudit\*.sqlaudit', DEFAULT, DEFAULT) order by event_time desc I have 10 audit files from 10 different servers all under this folder - E:\ABC\ServerAudit\ Thanks [replicate deleted records]( - hi guys, does any one know how to replicate "only deleted records" using transactional replication ? in other words deleted record needs to be moved to other database using transactional replication [We added 4 files to tempdb, due to Pagelatch_up being major wait]( - Yet they do not appear to be used, and lots of Pagelatch_up: tempdb waits are still showing. all 4 new files show 98% of free/available space, while the original 8 files are being used, at 50 and more percent, showing 20 or 30 % only free in them. Why are the new files not being […] SQL Server 2016 - Development and T-SQL [How to mark consequitive groups with non-unique group key]( - I have a sequence of records with ID and FLAG being a key to identify the group of records, but the FLAG here is a bit, so it is not unique declare @T table ( ID int , TS date , FLAG bit ); insert into @T(ID, TS, FLAG) values (1, '2020-01-01', 0 ) , […] Development - SQL Server 2014 [Get double records returned from SELECT because I failed to save the PK.]( - I have two tables. A lookup table that has a PK and various columns. Looks something like this: tblOperator PK OperatorID, OperatorName, BadgeID  My main table saves the BadgeID value from the tblOperator instead of the PK OperatorID. So when I do an INNER JOIN between tblMain and tblOperator, I get duplicate records returned. […] [Trying to understand percent sign next to field name]( - I have a simple query where I needed to see if a qty had decimal places. I was instructed to use field_name%1 as shown below. It works. I'm just trying to understand the process so I can learn more. What is the %1 doing/calculating? SELECT m.item_id , l.qty_on_hand FROM inv_loc l INNER JOIN inv_mast m […] SQL Server 2019 - Administration [hardening of SA account causes failed login found in error logs]( - Hi Guys, i am hardening the SA but disabling it and renaming it according to CIS hardening guide. But found multiple failed login errors in Error logs. Did a further trace using the sql profiler and discover: Login failed for user 'sa'. Reason:.... Always On Operations Dashboard i could not found any supporting technote from […] [Updating Python packages in Machine Learning Services ( MLS ) on a MI]( - Hi When MLS is set up on an Azure SQL Server Managed Instance ( compatibility level 150 ) , there are are whole batch of python packages that are installed. I know I can add or remove packages via ADS , but these appear to be packages not part of the base install. Is it […] [Always Encrypted and how to use them in queries.]( - Hello Everyone I've been testing Always encrypted on 2 test machines. I'm trying to get a feel on how easy they are to work with. My objective is to encrypt personal info of individual's but still be able to query that info. On server 1, I created Master Encryption Key, Column Encryption Key & Encryption […] SQL Server 2019 - Development [SQL agent failed job report last 24 hours]( - Hi All, Posting it on development group, if anyone can provide query. Basically, I am seeing 100+ failed jobs daily and it is taking more time to check in agent history and 80% of auto success in 20% are failed. Is there any way can only get report of failed non success jobs. -- Failed […] [MAXRECURSION 0 Option]( - Hi Everyone, I am trying to apply a Multi table function that uses a recursive CTE. When I apply it I get the error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion. I've looked online about this error and apparently I need to add: Option ( MAXRECURSION 0 ) Apparently […] [sql server keeps looking for my User Defined Scalar Function in the Master DB]( - I have a Visual Studio project web site that I have been using for years. The move to SQL server 2019, has apparently broken it. When I call the dataadapter.Fill function it tries to find my User Defined Scalar Function in the Master Database. System.Data.SqlClient.SqlException: 'Cannot find either column "master" or the user-defined function or […] [outer apply alternative]( - My goal is to get the results below. Trying to get the nearest date and result from the #Data table where the resultdate<=ProcDate. An outer apply is taking 4 hours for a 400 row table. Another more efficient way of doing this? I have several more '#Data' tables (CTE's) to join to the #patient table […] Amazon AWS and other cloud vendors [Cost effective VPN solution for AWS with MFA]( - Hi, I've recently setup VPN on our AWS account and had my AWS bill go up by $200. It appears that you pay for it even when nobody uses it. You can't stop it like EC2 instance. I found this article: It says you can automate the removal of VPN configuration and re-creation it […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -