Questions tagged [table]
A data structure that organizes information into rows and columns.
554 questions
2
votes
3
answers
290
views
Deleting multiples that aren't exact PostgreSQL
I think the issue came when inserting my data, some of the data looks like this:
id
year
1
2000
1
2001
1
2002
2
2000
2
2001
2
2002
3
2000
3
2001
3
2002
Where the intention was for it to ...
1
vote
1
answer
129
views
How to Find Out Which Columns in a Table are Queried by SELECT and UPDATE Statements
We have a table that has 54 columns, it is used by many applications, reports and users.
The table is from legacy system, some columns may be obsolete.
We would like to find out which exactly columns ...
- 5,964
-2
votes
1
answer
17
views
An establishment schema, with a single user owner and many user employees
I have an unchangable user table which stores user data, depending on the role(A 1-M connection with the roles table) the user could be either an establishment worker or an establisment owner. I need ...
- 1
0
votes
1
answer
37
views
Unable to retrieve data from postgres json column
I have a postgres table with json column as dataset_metadata. It stores an epoc time in array looks like:
select
event_type
,technology
,dataset_metadata->> 'intervals_epoch_seconds'
,add_ts ...
- 281
0
votes
0
answers
102
views
Insert operations in MariaDB causing deadlocks
I created the following table in MariaDB:
CREATE TABLE IF NOT EXISTS test_table (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
uuid VARCHAR(1024) NOT NULL DEFAULT '',
CONSTRAINT unique_name UNIQUE (...
0
votes
2
answers
71
views
How do I prevent a DB user to change table contents in SQL Server?
How do I specify that a DB user cannot edit the AspNetUserRoles table in SQL Server? Currently, the user has Membership->db_owner permission, and it can do everything.
I would like to control ...
- 123
1
vote
1
answer
25
views
Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app
I have the following table in MySQL.:
CREATE TABLE `aspnetusers` (
`Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`UserName` varchar(256) CHARACTER SET utf8mb4 ...
- 11
0
votes
1
answer
109
views
Compressing partitioned tables in Oracle 19c
I am compressing partitioned tables.
before partitioned tables, I tried normal tables with the follwoing steps:
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'USER',
orig_table => '...
0
votes
1
answer
41
views
primary key referened by only single foreign key of another table among multiple tables that can reference it
Let's say I have table Account that contains username and password of users to login:-
account (
account_id BIGINT PRIMARY KEY,
username VARCHAR(30),
password VARCHAR(30)
)
-- I have two ...
- 103
1
vote
0
answers
81
views
MySQL InnoDB Stored Procedure locks table?
MySQL 8.0
InnoDB
I have some sql statement like
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO MyTable
SELECT Name
FROM MyTable
...
- 11
1
vote
1
answer
27
views
Using custom table definitions in a multi statment table function
I have a custom type and a table, and I want to reuse this as a return result for several table functions. I want to declare the type rather than recreate the definition. So the start of the function ...
- 11
0
votes
3
answers
110
views
How to restore snapshots to another table?
Someone accidentally deleted several records from Cassandra's table in the cluster.
I want to restore the last snapshot to another copy table with the same structure within the cluster.
What is the ...
- 41
0
votes
0
answers
82
views
Creating table for inventory in existing database
Good afternoon. Let me preface this by saying I am not a DBA, I know just enough to get by and possibly get myself into trouble. I am being tasked with getting equipment inventory into an existing ...
0
votes
0
answers
32
views
How can I attempt to row-compress a table type?
The documentation for row compression states that it has "No effect." on table types. This suggests that it's possible to attempt it, but ineffective. How can it be attempted? I find it ...
- 823
0
votes
1
answer
60
views
Need to create a duplicate table in a separate schema automatically whenever a new table is being created
Whenever a new table is being created in Schema 'A', I need the table to be duplicated in the Schema 'B'. This should happen automatically. I have searched for trigger concept but it wont work on DDL ...
2
votes
2
answers
320
views
Snowflake won't allow an analytic function to be renamed
In the code below, I can do the LAG and PARTITION BY with no problem as a query. But I need it as a table. If I do CTAS, I need an alias on the column. But when I try to put an alias on it, it fails....
- 131
0
votes
1
answer
118
views
Can a postgres autovacuum increase the TOAST size of the table?
A table toast size goes from 7.5Go to 9.2Go in one hour. The normal database activity seems not responsible for that: no more queries than usual. To be the cause, it would need a larger amount of ...
- 281
-1
votes
1
answer
116
views
In Postgres, can I CLUSTER by multiple indexes?
I want to order the records in my tasks table according to completed and then according to workOrderID, just like I can to with the ORDER BY clause of a SELECT query, so that this
taskID
workOrderID
...
- 143
0
votes
1
answer
146
views
How to COPY table privileges dba_sys_privs into different table in Oracle
i want to copy table privileges dba_sys_privs in table A to new table B.
i have create table B. What left now was it's table privileges'. Kindly advice. Thanks
0
votes
0
answers
57
views
SPLIT RANGE ON A TABLE
I'm trying to create a job that executed automatically the rotation of partitions on a table.
I executed the next script but the ranges are not rotating successfully
DECLARE @PARTITION_FUNCATION_NAME ...
- 49
0
votes
1
answer
121
views
Json or separate column?
I'm developing a table structure to store the physical activity data and users can save their progress. The activities belong to multiple programs. like beginner program suppose be like having 1000 ...
- 101
0
votes
0
answers
96
views
how to create a duplicate table with the same constraints as the original in Oracle
I usually create a duplicate table like this:
CREATE TABLE TEST_NEW AS SELECT * FROM TEST;
This does create a replica of the original table, however, the constraints aren't replicated.
is there a way ...
0
votes
1
answer
41
views
Proper way of copying larger data from another Azure database?
Being the 3rd party that is allowed to read from the Azure SQL (in cloud), we need to simulate access to the data as in the older version of the ERP that used plain old MS-SQL server. Only the subset ...
- 143
1
vote
0
answers
77
views
Database Design for Tickets: Single Table with Conditional Fields or Separate Tables
I'm working on designing a database for a ticket management system, and I'm looking for guidance on the best database schema for my use case. The system allows tickets to be created by either a ...
- 11
1
vote
2
answers
907
views
How to optimize a query on a large table, besides using index?
I need suggestions/ideas of optimizing the following query:
SELECT COUNT(*)
FROM (SELECT 1
FROM f
WHERE parent_sha256 = $1
AND parent_sha256 <> sha256
LIMIT 1) AS ...
- 189
0
votes
1
answer
377
views
Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL
I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
- 1
0
votes
0
answers
276
views
Dynamically selecting a table in a query based on result from joined SELECT
I have seen many examples but they don't match with my special case (I know... everyone's case is a special case :D )
I have to match values from different tables, some of which are named after the id ...
- 101
0
votes
1
answer
937
views
Finding when columns are added- SQL Server
In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
0
votes
0
answers
33
views
Help with design using ltree on PostgreSQL
Currently designing a database to register animal species (Sharks and relatives)
1 table will hold species (name, attributes, and relative information)
I will have the taxonomy hierarchy for each ...
- 101
2
votes
1
answer
84
views
Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks
I have the following database schema (MySQL 5.7):
I have assistants that may be linked to either:
one customer (might evolve to multiple customers in the future, hence the association table)
one or ...
- 23
0
votes
1
answer
80
views
Flyway Migrate - External Table Create Throwing ORA-00922: missing or invalid option
We use flyway in our company to make changes to the various regions. The guy who set all this up is unfortunately left the company so I don't have anyone really to run this past.
I have a bit of SQL ...
- 11
1
vote
2
answers
99
views
transparently splitting a single table so the program doesn't notice
I am using homeassistant with mariadb. This is hosted on a Pi 4 with 8gb of RAM
I have been collecting data a long time, and a single table grows to 20gb. The storage is a M.2 NVMe via USB 3.
CPU ...
- 151
1
vote
1
answer
177
views
Column containing keys from columns of another table
I have two csv files, countries and birth_rate_stat with the following data:
ISO
ISO3
ISO_CODE
FIPS
Display Name
Currency Name
Phone_Code
Region Code
AF
AFG
4
AF
Afghanistan
Afghani
93
Asia
AX
ALA
...
- 11
1
vote
1
answer
33
views
Table Schema problem, column with multiple dependencies
I have a product table shown below:
CREATE TABLE(
Id_Product INT PRIMARY KEY,
Product_SKU INT NOT NULL UNIQUE,
Product_Name nvarchar(100) NOT NULL,
Product_Section ...
- 11
3
votes
1
answer
227
views
Selecting second highest value using two SQL queries returns different results
I have the following tables from which I am to select the second highest salary:
Table 1:
id
salary
1
100
2
200
3
300
Table 2:
id
salary
1
100
I wrote two SQL queries to achieve this:
Query 1:
...
- 133
1
vote
0
answers
143
views
Auto Increment ID based on 2 columns
I have table called Logs. it has 4 columns ID, USER_ID, USER_TYPE, USER_ACTION
where
USER_ID is unique
USER_TYPE is unique for every USER_ID and
USER_ACTION can be anything 1,2,3 or A to Z.
USER_ID ...
- 11
0
votes
1
answer
112
views
table inheritance: TPH, TPT and single table vs taxonomies
according to this post and many others web resources, you can do table inheritance with Table Per Hierarchy Inheritance, Table Per Type Inheritance or Single table (single table for all sub classes).
...
- 163
0
votes
0
answers
20
views
GIS database table setup with multiple columns or FK
I am trying to design a environmental GIS table setup in a geospatial database. The workflow process is to create
a Permit To Disturb (PTD Table)
and for each PTD there will be a point, line and ...
0
votes
0
answers
63
views
Move data from existing column to new columns in Sequel pro
I need help with my SQL table, so thank you in advance for any comments. My table in Sequel Pro (example):
Object
Time
Intensity
. A.
. 1.
. 100.
. A.
. 2.
. 150.
. A.
. 3.
. 300.
. B.
....
- 1
0
votes
1
answer
144
views
unable to add data to a table in mysql, Error Code: 1054
##Created table using the following line:
create table sales (
product_id int,
sell_price float,
quantity int,
state varchar(20) );
##Tried to input values using the following line
insert ...
0
votes
2
answers
191
views
Separate tables for logging?
I would like to log new products, updates to products and deletions of products (and the same for other schemata [customers, locations etc]).
Would it be best to have a separate table (e.g. ProductLog,...
- 131
0
votes
1
answer
125
views
create table based on another table
I have a table called " Product " it has: ID_P, Name, expiration date
Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
- 39
0
votes
2
answers
323
views
Exec An SQL query in a table in Database 'B' from database 'A'
i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a ...
- 99
1
vote
2
answers
102
views
Help - Database design
We are implementing the new requirement in our current web application. If we discuss the database design for the new requirement, it appears to be more complex.
Requirement -
We have two webforms; ...
- 165
1
vote
1
answer
73
views
SQL Server find records that have the same data and keep one
I have a table containing Services (service_id) for different Packages(package_id)
I am trying to find Packages with the exact same Services and keep only one Package of them
dummy_id package_id ...
- 521
0
votes
0
answers
64
views
How do I get a big-picture feel for my database?
Background: I was just granted access to my company's SQL Server (MS SQL 2019 via Azure Data Studio). As I have not yet been assigned any specific projects I want start digging.
I've asked my DBA to ...
2
votes
1
answer
47
views
How to combine three tables for a queue in each Store in each town?
Sorry if my question is no appropriate for DA.
I have a State-city table with unique id for every town/city name. (id: town --> city --> State)
I also have a list of Store names associated to ...
- 131
4
votes
1
answer
1k
views
Returning table name and schema name along with column values
I have table in SQL:
public.employee
name
age
abc
20
xyz
30
I want a query which would return the column values along with schema name and table name.
Input:
select * from public.employee
Output:
...
- 41
1
vote
0
answers
61
views
Table for an Appointment App
experts,
I am building out an appointment scheduler application. I am seeking advice on how best to tackle this issue.
I am building out my ERD and I am the point where I will be creating a ...
0
votes
2
answers
357
views
How to use foreign key, to remove subentries?
I try to delete all the subcategories and entries within this subentries, when i delete the main categorie from within one table. If i had multiple tables, i would use a foreign key with ON DELETE ...
- 13