The Basics of PostgreSQL Views (2023-03-17)

SQLServerCentral Newsletter for March 17, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [The Basics of PostgreSQL Views]( - [What are the biggest challenges organizations face in their digital transformation efforts?]( - [SQL Server .WRITE() Function to Update VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) Data Types]( - [From the SQL Server Central Blogs - SQLpassion Live Training about SQL Server Query Tuning Fundamentals]( - [From the SQL Server Central Blogs - #PGSQLPhriday 006: Wrap Up & Summary]( Question of the Day - [SQL Server 2022 Restore and DBCompat]( The Voice of the DBA  Daily Coping Tip Eat mindfully. Appreciate the taste, texture, and smell of your food 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. Annual Security Compliance Training Today we have a guest editorial from Andy Warren as Steve is at SQL Bits. This was originally published on Aug 21, 2015. I just recently finished the annual ritual of required training for security awareness and compliance topics. If you work for a publicly held company, especially one that has PCI, SOX, or HIPAA data, you’ve probably done this, too. It’s a coffee drinking chore for me and that had me thinking – does it do any good or is it just checking the box for the organization? The training covered a variety of topics: phishing, not sharing passwords, clean desk policy, ethics, travel, use of VPN, and more. This time it was a slide type presentation for each topic along with narrated audio, followed by a short quiz at the end of each topic. Most were mercifully short. In the early years it seemed like every topic was twenty minutes! Some of there were of the ‘check the box’ type, asking if you had read and understood various policies, while some were two pages long and one was closer to 100 pages. Does anyone (besides me) actually read a 100 page policy? Do we really expect them to? If we don’t, then what are we doing? Businesses are trying to minimize the non-productive and/or non-billable time while trying to meet compliance requirements and provide useful training. Most do it by outsourcing the training, with a provider customizing it to some degree (links to local policy documents mainly). The end result winds up being a cookie cutter solution that satisfies the auditors and does some good, just not as much as if it really reflected the environment the employee works in, with the biggest internal problems of the previous year called out. This process also assumes that all training needs to be done every year, instead of making a bigger investment up front to make sure the employee really understands all the policies and processes and then using the annual training as a refresher. We can do all the training in the world and still have someone click on a phishing link or let someone tailgate through a door or accidentally send out a list that contains sensitive data. It happens all the time. Does that mean the training is ineffective? That’s worth thinking about for a minute. We can argue that the training reduces the frequency of issues because the users are educated about the threats. Or, we can argue that allowing for any failure is unacceptable and we must prevent those via a managed solution in the background. We could argue that the training isn’t good enough if mistakes are still made – true to a point maybe, but we’ll never get to 100%. Imagine you own a company and you’re required to provide this training in some form. You get to decide: - Everyone signs an electronic form once a year saying they’ve reviewed all the policies and agree to comply with them. - Everyone does two to five hours of review followed by an acknowledgement and/or quiz - Everyone does two full days of refresher training along with multiple tests You can probably think of some other options, but it all comes back to being required to do something. Minimizing cost may increase the risk. Minimizing risk will probably increase the cost. It seems like the logical thing to do is focus on the areas that matter most and figure out how to address those topics most effectively. I still groan when I see the email saying it’s that time again, but I’ve learned to accept it as the most reasonable way to meet the requirements. Andy Warren [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [The Basics of PostgreSQL Views]( Shivayan Mukherjee from SQLServerCentral Learn the basics of views in PostgreSQL. [External Article]( [What are the biggest challenges organizations face in their digital transformation efforts?]( Additional Articles from Redgate As part of a recent Redgate Summit which focused on data modernization, Grant Fritchey interviewed Pramod Sadalage, a Director at Thoughtworks. They discussed data and DevOps, and the typical challenges organizations come across in their digital transformation efforts. Grant Fritchey shares this Q&A. [External Article]( [SQL Server .WRITE() Function to Update VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) Data Types]( Additional Articles from MSSQLTips.com Learn about the SQL Server .WRITE function along with how to use this to update data in VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types. [Blog Post]( From the SQL Server Central Blogs - [SQLpassion Live Training about SQL Server Query Tuning Fundamentals]( Klaus Aschenbrenner from Klaus Aschenbrenner On April 4, 2023 I will run an SQLpassion Live Training about SQL Server Query Tuning Fundamentals. If you have a database driven application which reacts very slowly when... [Blog Post]( From the SQL Server Central Blogs - [#PGSQLPhriday 006: Wrap Up & Summary]( Grant Fritchey from The Scary DBA Your mission, if you chose to accept it, was to share a single tip/hint/fact/something that you wish you’d known about PostgreSQL when you were just getting started learning it....   Question of the Day Today's question (by Steve Jones - SSC Editor):  SQL Server 2022 Restore and DBCompat I have a new SQL Server 2022 instance. What is the earliest version of a SQL Server backup from which I can restore and what is the lowest db compat level? 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) Decoding Set Options The @@options variable stores the options set for a user. If this were set to the value 4, then all options would be off, except for the SET ANSI_WARNINGS option. If I want to use the new SQL Server 2022 function, GET_BIT() to decode this value, what should I run? Answer: SELECT GET_BIT(@@OPTIONS, 3) Explanation: GET_BIT() works with integers and binary values. @@OPTIONS returns an integer, so GET_BIT() reads from the right. It is 0 based, so the option with a 4 value is bit 3. Ref: - @@OPTIONS - - GET_BIT() - - Configure User SET options - [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 - Development [Transaction has no commit error]( - All, I have an unusual scenario. A developer sent me some code that has a BEGIN / COMMIT and a rollback right before the commit. It's all wrapped in a reasonable TRY...CATCH. The script builds some dynamic SQL INSERT statements using OPEN_QUERY, dynamic because the WHERE clause depends on some variables. The INSERT statements they […] SQL Server 2016 - Development and T-SQL [How to remove special chars?]( - Hi everyone, So I have a xml file from which I am trying to import data into my sql tables. I noticed there are some special characters inside the file due to which I am unable to import data properly. Look at the below screenshot: Can anyone tell me what are there square boxes and […] SQL Server 2019 - Administration [How to resolve Login differences between Primary and Secondary cluster nodes]( - I'm a bit rusty with SQL clustering so please bear with me, thanks. I have a 2-server availabilty group. The other day it failed over during a patching reboot, and a Linked Server connection no longer worked while it was failed over to secondary. Initiating a manual failover back to the original primary resolved the […] [Azure SQL - Import Data-tier application]( - Hello! Where can I find log to see how long it took for Import Data-tier application to create new database from the bacpac file? Or is there any script that can be used to get the completion time?  Thanks in advance.  [Cannot bulk load because the file could not be opened error]( - This is a weird one. My user has two NAS shares. QC and Test and two SQL Servers, QC and Test. She has bulk admin permissions on her account and the NAS share (according to the windows group) also has her permissions. She's running the following code: select * FROM OPENROWSET( BULK '//My/nas/Share/QC/MyFile', SINGLE_BLOB) AS […] [SSMS is filling up our C: drive -- installed on E:]( - Good day. I didn't know where else to post this, so forgive my ignorance if this isn't the place. I have a situation that involves a single Windows Server with SSMS installed that has the only permissions to hit another organization for our Data Warehousing team. There may be as many as 4 BIDS plus […] [Trigger on update when changed only desired XML tag.]( - Hello! I have a task to configure for the Skype for business database logging of user status changes (Available/Away/Offline/etc). i found source for this data in XML field with content like: 15500 [To get a list of un-used columns in all tables in a database]( - I am looking to get a list of un-used columns in all tables in a database. I would like the output to look like something like this. DatabaseName, SchemaName, TableName, ColumnName, etc If anyone has a handy script or any link, please share. Thanks. SQL Server 2019 - Development [SELECT ROWS ON TIME CRITERIA]( - Hi, I would like to retrieve only the rows after 23:59:59 but I am getting the output as shown in the picture. Please advise how to write a correct where clause SELECT ID, STARTDATE, STARTTIME, ENDTIME FROM MACHINELOG WHERE (STARTTIME > CAST('00:00:00' as time(0)) AND ENDTIME < CAST('09:00:00' as time)) ORDER BY ID DESC [SELECT ROWS ON TIME CRITERIA]( - Hi, I would like to retrieve only the rows after 23:59:59 but I am getting the output as shown in the picture. Please advise how to write a correct where clause SELECT ID, STARTDATE, STARTTIME, ENDTIME FROM MACHINELOG WHERE (STARTTIME > CAST('00:00:00' as time(0)) AND ENDTIME < CAST('09:00:00' as time)) ORDER BY ID DESC [Iterate and assign Query help]( - Table:SchoolTeacher TeacherName Zip City State ------------ ---- ------ ------ Sam 12345 NJ NewYork Pat 12345 NJ NewYork Peter 12588 Spring MD School: SchoolLocationID Zip City State -------------- ------- ----- ----- 105 12345 NV NewYork 105 12345 NJ NewYork 106 12345 NJ NewYork 107 12588 Spring MD BusinessRule: 1) Match should be done on Zipcodes if […] [SQL Query to display having only values]( - I am having below -mentioned table1 and I would like to display it as shown in the below table Can someone help me with the query Thanks in advance [SELECT with computed values]( - From a huge and ever growing table (tbl_Values) I need to select all records which conform to rules defined in another table (tbl_Rules). Each record in tbl_Values has a time_stamp (datetime) and a fvalue (float); the rules defined in tbl_Rules have a WeekDay (int), a HourOfDay (int), a MinValue (float), a MaxValue (float). Records from […] SQL Server 2022 - Development [Case and When Query]( - Hi All, I want to rewrite the below query using the Case and When statement Query 1 update [SF_Title_Update] set [Title_Proper]='Human Resource Management System Manager' from [SF_Title_Update] where ( title like '%Human%' and title like '%Resource%' and title like '%Management%' or title like '%HRMS%' ) and (job_level='Manager Level' ) and( [Title_Proper]=' ' or [Title_Proper] is […] [Dynamic Query]( - Hi All, I have to generate a dynamic query for the below mentioned keywords to update them in Title_Update column Keywords Recruitment Employee Engagement Performance Management Training and Development Compensation and Benefits Human Resource Diversity and Inclusion Also it should have the below mentioned Job Level Chief Level Director Level Head Level Manager Level Managing […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -