Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
651
questions
0
votes
0
answers
65
views
Why does postgres/citus use the index when querying the table, but not trough the view?
I have a Citus 9.5 database (postgres 11) on Azure and executing a complicated query with CTEs.
The schema has a simple view from another schema as:
create view my_schema.table1
as
select * from ...
- 281
0
votes
2
answers
350
views
Create view with inline function in db<>fiddle
I'm trying to create a view with in inline function in db<>fiddle.
I've removed the CREATE VIEW part for now, and am just trying to run the query, just to get to the root issue.
select /*+ ...
- 1,447
0
votes
0
answers
35
views
Are SQL views anything more than saved text?
I am trying to get a better understanding of an answer by @GarethD in https://stackoverflow.com/questions/24188350/incorrect-syntax-near-keyword-option-in-cte-statement?rq=1#answer-24188586 . In that ...
- 2,759
6
votes
3
answers
1k
views
Views versus a function for getting the top records of a table?
I'm talking with a coworker about a way to approach a problem.
We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns Lot and TestGrade.
...
1
vote
1
answer
106
views
Postgres views for PII
How would I set up a view that is for showing a restricted set of data for masking PII? Can a view be created which is available at lesser role access than the source tables? This is for Postgres ...
- 113
0
votes
0
answers
104
views
fetch Postgres views definitions in correct order
Is there a way I can extract the definitions of all the views in a given Postgres schema in correct order? For example if a view 'A' depends on view 'B', is there a way/script/query which outputs the ...
- 13
1
vote
0
answers
26
views
Granting privileges on a view
I'm reading Database System Concept, in a chapter about authorization, the book first says that:
A user who creates a view does not necessarily receive all privileges
on that view. She receives only ...
- 13
1
vote
2
answers
690
views
Indexing a view with an averaged column
With Sql-Server, I create this view:
IF OBJECT_ID ('dbo.v_table', 'view') IS NOT NULL
DROP VIEW [dbo].[v_table];
GO
CREATE VIEW [dbo].[v_table]
WITH SCHEMABINDING
AS
select toplevelcom_nr, avg(...
- 155
-1
votes
1
answer
434
views
How to assign minimal privileges for create view
In Db2 v11.1 on Linux we have a request to create a new user with access only to the "create view" privilege.
What are minimal permissions I have to set in order for the user to be able ...
- 315
0
votes
2
answers
2k
views
Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?
I have a table as you can see below:
create table table_test
(col_id number(1))
The table has below data:
col_id
------
1
2
3
I create a view based on this table:
create view v_table_test as
...
- 1,412
0
votes
1
answer
633
views
What is the best way to check performance for table vs view?
Could you please advice what is the best solution for compare performance of query for Table vs View.
I mean have have two databases on same server (SQL Server 2016). Both include same data but in ...
- 45
0
votes
0
answers
26
views
View Expansion and Removing "dead variables"
I have a large set of interwoven views in Postgres that sometimes are make inefficient use of other views.
For example,
CREATE VIEW C AS
SELECT A.a, B.b
FROM A
LEFT JOIN B on A.id = B.id
Then,
SELECT ...
0
votes
0
answers
121
views
select which algorithm a view is using
I have a view in a MariaDB 10.4.13 database that feeds into a database documentation process and gathers data on tables and views used in the database.
Amongst other fields it selects which algorithm ...
- 209
0
votes
0
answers
232
views
MSSQL CDC for views and stored procedures
is it possible to enable CDC for Views or Stored procedures?
BR,
Val
- 27
1
vote
0
answers
36
views
Creating the right index/es for querying views in SQL(ite3)
I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
- 111
0
votes
0
answers
370
views
Convert jsonb column into view for Grafana
I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana.
Example for one document insert (partial)
{
"Timestamp": {
"$date": {
...
- 11
0
votes
2
answers
1k
views
How to improve performance to 12million rows table?
I have performance issues on a query in a big table (12 million records) based on Geonames, that's a read-only database so NO DELETE, UPDATE or INSERT only SELECT.
There are queries I make every now ...
- 3
1
vote
1
answer
323
views
Alter a view that refers to a non-readable secondary database
I have a database (DB1) that is not readable because it is secondary in an AG group.
I have another database (DB2) that is not replicated with the AG group. It contains some views that reference the ...
- 4,175
0
votes
1
answer
82
views
Counting failed attempt
Here is my code:
create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(...
- 13
1
vote
1
answer
450
views
Improving indexing of jsonb data when the json structure is well defined
I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which ...
- 111
0
votes
1
answer
5k
views
How can I dynamically create views in Postgres 10 using variables?
I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12. I have about 200 views to create and the only difference in each one of them is the schema location and ...
- 249
0
votes
0
answers
1k
views
MySQL Views breaking mysqldump command
While trying to backup all data from a mysql server with mysqldump I got this error
root@server$ mysqldump -uroot -p --master-data=1 --single-transaction --flush-privileges --routines --triggers --...
0
votes
1
answer
758
views
Return results from SQL JOIN even if criteria not met in all tables
New to SQL. Building my first contact manager project. Current schema can be seen below.
Issue:
I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact....
- 111
0
votes
1
answer
185
views
Anyway to view original query on top of views using postgres
For example, I have a simple query:
SELECT * FROM information_schema.columns where table_schema = 'public';
Which turn out very complex, information_schema.columns is a complex view.
Right now, to ...
- 101
0
votes
1
answer
32
views
Using COUNT() implicitly for making a view over game match data?
I've not used SQL in quite a while and I'm pretty rusty it seems. I've had to do a project recently and needed to use MySQL on Windows and when looking for solutions to this particular query, I get ...
- 111
-1
votes
1
answer
187
views
What is the correct way to organize tables for a SQL view?
I'm new to data modeling. Currently working at creating a personal contact manager but I need some help with modeling the table relationships for a given view.
The final view columns I'm shooting for ...
- 111
1
vote
1
answer
82
views
Different (much worse) query plan when using one view
I am running the following query:
SELECT m.entityid,
m.alias::text AS alias,
m.measurement_ts,
m.volume_m3::double precision AS volume_m3,
m.pressure_mca,
m.battery_volt,
(m....
- 145
0
votes
1
answer
211
views
Query Store System View JOINs
In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any ...
- 119
2
votes
1
answer
283
views
Schema binding seems to not be enforced for view doing temporal query
I am using Microsoft SQL Server 2019.
There are two strange behaviors that I don't see documented (if it is, please point me to the Microsoft docs link), I suspect they're related.
The ...
- 188
0
votes
0
answers
89
views
MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?
TL;DR;
I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
- 103
0
votes
0
answers
167
views
Does MySQL have a built-in way to tell whether a view is insertable (not just updatable)? E.g., something like the IS_UPDATABLE flag, but for INSERT?
The MySQL documentation for the INFORMATION_SCHEMA.VIEWS table mentions that MySQL sets a flag whenever a view is created that records whether that view is updatable, i.e., whether you can UPDATE the ...
- 105
1
vote
0
answers
450
views
How to optimize MySQL VIEW performance?
All my years of development an this is my first post, so I hope I get this right.
I hoping someone can help, my SQL skill is sufficient enough to get me by most of the time, but I'm having poor ...
3
votes
0
answers
448
views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
- 131
1
vote
1
answer
1k
views
Permission denied for view beyond its own schema
There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one.
CREATE VIEW staging_fiat.udz_odx_dtcs AS
SELECT * FROM rohdaten_fiat....
- 113
0
votes
1
answer
614
views
MySQL - cannot examine view definition - Error getting DDL for object - No database selected
I have a view that I can run, it returns a dataset, but whenever I try to alter the view or look at its definition, I get
Error getting DDL for object
No database selected
But I do have a database ...
- 449
1
vote
1
answer
416
views
Getting the "flat" query corresponding to a view using nested views?
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
- 111
0
votes
1
answer
3k
views
Postgres SQL: View with a nested table
I have 2 tables users & groups:
user_id | title group_id | title
---------------- ------------------
U_1 | User 1 G_1 | Group 1
U_2 | User 2 G_2 | ...
1
vote
2
answers
164
views
Why is row-level access needed when views can limit a person's access to rows and/or columns?
So I've always in general been confused about what the difference between the security of a vew and of Row Level Security.
so I guess my general question is
Why is row-level access needed when views ...
- 139
5
votes
1
answer
411
views
SQL Server Indexed View and TOP
I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in ...
- 421
1
vote
2
answers
370
views
list of views that depend on a given table and column
I need a script to check if a given column in a given table is used in any views. Ideally I would like to get the name of the view(s) as well.
I am using T-SQL, and I need to run the script to check ...
- 113
1
vote
1
answer
1k
views
SQL Server Nested views, how deep is too much?
I stumbled on a problem using views. The content management system my program interfaces with, defines a view which calls another view on the system. That is 2 levels to begin with. Before I stumble ...
- 113
1
vote
2
answers
1k
views
Why MySQL doesn't use indexes in view with union select?
I've created a view:
create view dh_hits_new_all_old
as
select * from dh_hits_new_201806
union all
select * from dh_hits_new_201807;
When I select something with a where clause containing a field ...
- 111
-1
votes
1
answer
367
views
Are there any performance downsides in using a view to filter a huge table and use it as the main entrypoint to query the table?
I have a table with a lot of records, and I want to restrict the data access to the last 3 years only, but I still want to keep the data there.
I saw a solution where is it possible to create an ...
- 1
3
votes
1
answer
188
views
T-SQL equivalent to Python Pandas Concatenate
I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does. I do not want to replicate the information for it is rather large. I'd like to have a VIEW like ...
- 173
1
vote
1
answer
2k
views
Using pg_dump on a view always produces an empty file
I want to export a subset of a tables rows using pg_dump (using powershell on windows). The simplest way seems to be to create a view with an appropriate WHERE clause. However, when I run pg_dump on ...
- 1,125
0
votes
1
answer
1k
views
Postgres CREATE OR REPLACE VIEW... can it block?
I'm looking for a way for a table to be swapped while it's being used. Say we have a view
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1;
that is being used, e.g. lots of SELECT * FROM ...
- 171
0
votes
0
answers
624
views
Data mismatch in View2 [SELECT * from View1] with View1, how to fix?
The problem I encounter can be oversimplied as
CREATE View#2 AS SELECT * FROM View#1;
SELECT * FROM View#2;
SELECT * FROM View#1;
Then View#1 and View#2 return different results. This challenges ...
- 159
1
vote
1
answer
730
views
PostgreSQL - trying to speed up query responses from view that has join and group by
I have these two tables:
precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates).
Column | ...
- 111
-2
votes
1
answer
124
views
CREATE VIEW WITH LEFT JOIN - FILTER RIGHT DEPENDING ON LEFT
I am using Oracle 12.1.
I have the following tables, simplified for the question :
(Note that the XXX for entity ID does represent the same ID being repeated)
Events
date_event | entity_id | used
-----...
- 47
0
votes
1
answer
42
views
Collation conflicts between views and functions when using utf8mb4_unicode_ci collation
Currently running MariaDB 10.3.24 and I really want to fully support the utf8mb4 unicode character set in my databases.
The problem I am running into is that when I try and set everything up to use ...
