Questions tagged [table]
A data structure that organizes information into rows and columns.
475
questions
0
votes
0answers
24 views
How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?
Hope you are well
I'm been a bit stuck trying to develop the code to get the inheritance of extended properties.
I will explain the situation:
The thing is have a query to list all extended properties ...
0
votes
1answer
23 views
Get number of deleted pages in PostgreSQL table/index?
How can I get the number of deleted pages in a table or index?
My purpose in asking is to modify an index bloat query that otherwise doesn't take into account which pages are deleted (and therefore ...
0
votes
1answer
48 views
How bad is to prefix table names to order them exactly as I want?
When using an interface like phpmyadmin/heidisql or others, I have this tables:
account
account_post
account_post_status
account_post_type
account_status
account_type
Ideal for me is this:
account
...
1
vote
0answers
29 views
How to design a table schema to optimize insert and updates for a unique numeric column
Suppose there is a table with 3 columns
id, name, score
Name and score are both unique columns.
The use case is that,
The table is pre-populated with 10 rows. Scores are set from 1-9.
A user can ...
0
votes
0answers
56 views
SQL Server table design handling ubiquitous information
I have a doubt about table desgin in SQL Server 2019.
Usually i try to map tables with each entity in my "conceptual model", so as usual i do that and create tables for user, articles, ...
2
votes
3answers
144 views
Count number of occurences per location per hour in PostgreSQL
I have a dataset in Postgres of boat locations on waterways. Here is a sample of the table:
boat_id
ts
waterway_id
Boat_A
2019-01-01 16:29:11
WW_01
Boat_A
2019-01-01 17:03:04
WW_02
Boat_B
2019-01-...
0
votes
2answers
76 views
SQL Server - how to define default value function that require column name?
Column names are not permitted when declaring default column value. Is there a workaround to achieve something like this?
CREATE TABLE [dbo].[Tasks]
(
[TaskId] INT IDENTITY(1,1) NOT NULL,
[...
0
votes
1answer
168 views
Changing InnoDB table format to dynamic
I run a forum, and this new warning pops up in the admin area:
You have one or more InnoDB tables that are using the Compact row
format. This may limit the amount of data that can be stored within
...
-2
votes
1answer
17 views
Difference between these two query implementations of inner joining a table with a lookup table?
I've been learning querying and I've come across the concept of inner joining
Where this query inner joins the columns departmentId from employee and department tables and displays the value of ...
0
votes
1answer
72 views
Return unique records based on shared column in 3 tables
This is the database schema that I'm working with and I've included some sample data as an example of what is currently in the database.
CREATE TABLE Visits ([ADATE] date, [AKEYTIME] varchar(10), [...
0
votes
1answer
39 views
handling primary key with large tree, best way to get root id from child table X
I always used database design using this pattern;
root table with PK id1
child1 table with PK id2 FK id1
child2 table with PK id3 FK id2
child3 table with PK id4 FK id3
etc...
so if I have to get ...
0
votes
2answers
81 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 ...
1
vote
0answers
15 views
In Interactive SQL 12.0.1, How do I declare a temporary table and then run a compound statement in a single SQL Statement?
I am trying to load a csv file into a temporary table and then run a compound SQL Statement on that table. I can get the DECLARE LOCAL TEMPORARY TABLE ... INPUT INTO ... FROM [filename] to work on its ...
0
votes
2answers
21 views
Count results of table by date [closed]
I am trying to query the number of transactions that occur on a given day with the following query
select COUNT(seq_no) from "table"
where start_time >= '2020-04-01 00:00:00'
and ...
0
votes
1answer
35 views
MS SQL Creating Tables and Adding Values by a Trigger
Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By ...
0
votes
1answer
19 views
is it safe to create mysql database by just creating its directory?
context: I want a separate encrypted filesystem per database (to have a separate key) on MySQL 5.7 but if I mount it before I execute CREATE DATABASE foo it fails with error database exists, and ...
0
votes
0answers
44 views
Can a database have an unnamed row?
I came across a question asking whether it is possible to have unnamed rows and columns in a database. I assumed unnamed implied primary key. I'm not sure whether my assumption is correct. When I ...
1
vote
1answer
44 views
Add constraint to table creation in SQL
I have to create a table Product with elements P, PType, PName, Price and Origin.
I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I ...
0
votes
1answer
28 views
Consolidate / sort / combine / merge data for dummies
I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I ...
0
votes
1answer
35 views
In SQLITE, how do you create a new table into its own file?
I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about ...
0
votes
1answer
73 views
Oracle 11g - Decreasing Big Table Size
I want to reduce the size of a table.
I thought if I could do this with export / import, but when I check it
with the ESTIMATE_ONLY option, it doesn't seem to work.
I have no chance of using ...
0
votes
1answer
50 views
Capture row with highest value from each percentile
So far I've tried doing this:
SELECT MAX(buckets.position_id), NTILE
FROM (
SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id)
FROM position
WHERE activity_id = 8
...
2
votes
1answer
240 views
MySQL table with identical name multiple times in same database
I stumbled into a pretty nasty bug into our PROD environment
I was running some alter scripts and it appears it ended up duplicating the table for each column altered.
I've tested with MySQL Workbench,...
0
votes
1answer
112 views
Repair Database using .FRM .IBD .OPT Files ( Hard copy mysql folder )
I had a crash on my server.
The only thing I could do was hard copy the mysql folder
Now I am trying to access my database again through the PHPMyAdmin interface.
Some databases (mostly WordPress) run ...
0
votes
1answer
36 views
Postgresql: Disallow duplicate value to be inserted if already in any of multiple columns?
Suppose I have 3 columns:
Critical, Medium, Low
And I have data UUIDs to be inserted from another tables. And I want to disallow insertion of same UUID in any of the column if it is already present ...
0
votes
1answer
62 views
Create cache table [closed]
I need help with SQL Server. There is a task scheduled to run an .exe file. After the exe finish execution, I can get the logs below:
2021-03-05 22:45:00 : Create cache table
2021-03-05 22:45:00 : ...
0
votes
2answers
24 views
Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014
We are given 5 tables.
Orders
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers
ID ...
1
vote
1answer
20 views
Mysql update table from another table with condition
In mysql i have 2 table.
First
wp_postmeta_import
Second
wp_postmeta_temp
These tables have these columns:
meta_id
post_id
meta_key
meta_value
I need to update only my column meta_value of ...
0
votes
1answer
41 views
Identify table usage in sql server before we stop the load
I tried browsing this across lot of links but cant get much of help so i am posting this question which sound bit similar.
We have quite some tables which are being planned from our app team that they ...
0
votes
1answer
24 views
Subquery with variable in where
I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null.
But why not do ...
5
votes
3answers
1k views
Delete rows older than x days without locking table
we have a quite big MS SQL database with millions of rows.
I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application.
The ...
0
votes
1answer
19 views
Need help identifying how to make a function selectable using table() clause and a custom data type
I would like to be able to run the following code to select records:
Desired Syntax
select * from table(my_new_package.get_data(123))
I have been successful in creating code that compiles, but I am ...
0
votes
1answer
47 views
Is columnstore a good option for UPDATES with WHERE clauses?
I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea.
The project consists of a table (A) that will hold a large number of rows, with many ...
0
votes
2answers
40 views
Table compression on 500GB table using Enterprise edition with online=on
I am using MS SQL2016 enterprise edition and we have several tables that need to be compressed to save memory and backup space. Right now they are each around 500GB and I am trying to get some space ...
1
vote
1answer
261 views
How to model dependent categories (parent-child-relation)?
Idea / Goal
I have two tables: Article (represents a Product) and Category. The relation is like this: An Article can be assigned to only one Category but a Category may have at least one Articles, ...
0
votes
0answers
113 views
Importing xml data to postgres database table
I used the following script to import data from xml to postgres db table-
DO $$
DECLARE xml_string xml;
BEGIN
xml_string := XMLPARSE(DOCUMENT convert_from(pg_read_file('C:\\Users\\pink\\Desktop\\...
0
votes
1answer
456 views
How to show number of tables for each database in Postgres?
Related to my previous question, I use the code below to get all databases:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
AND datallowconn
AND datname <> 'postgres';
How to get ...
4
votes
3answers
306 views
SQLServer - treat table as index
I have table that is more or less as follows:
table Sample (
location bigint,
device bigint,
timestamp datetime,
type bigint,
value bigint,
)
For performance reasons we also ...
0
votes
1answer
262 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 | ...
0
votes
1answer
25 views
SQL insert which updates one table and based on a field value chooses what other table needs updating
DB setup: MSSQL
I have a source record coming in via API call. That call is intended to update a system in the database.
The first table is a common table where all systems have some details
SystemId ...
0
votes
1answer
28 views
Elevate Database - Script out all tables structure
Could some one please help/guide me how I can script out all the tables structure from a single database in Elevate database. (In SQL there is a way to do it but not sure how to do it in Elevate DB)
I ...
0
votes
0answers
88 views
Snowflake: Creating a table with a column name which is ANSI reserved fails, but adding a column(alter table) same ANSI reserved column name succeeds
CREATE TABLE test_table (LOCALTIMESTAMP varchar(10))
This query fails with .invalid column definition name 'LOCALTIMESTAMP' (ANSI reserved)
ALTER TABLE test_table ADD COLUMN LOCALTIMESTAMP varchar(...
0
votes
2answers
57 views
Does SQL Table Column is assigned with column number
In Table, we can see there is number assigned automatically (line number) is the same thing is available for the columns (column number) and how can we get that number?
I need a SQL query to find the ...
0
votes
1answer
32 views
Product_ID in my Invoice table [closed]
I'm doing a uni assignment.
I have
a stock table where stock is assigned an ID (product_ID).
a sales table where the stock is purchased.
an invoice table where sales are processed.
I want the ...
1
vote
1answer
53 views
Need Help Normalizing a Database for Media Website
I've created a media website where visitors can watch trailers, view pictures, etc. Along the way, I've found that I've needed additional images for sharing, etc. I went the "easy" route and ...
0
votes
1answer
93 views
Adding database-specific privs for user gives table 'db' is full, MariaDB, *tiny* toy database
On Windows 10, running 10.4.14-MariaDB - mariadb.org binary distribution
Using phpmyadmin, logged in as root.
I have a rather small WordPress database named PBALL_WP that I loaded from a mysqldump ...
0
votes
0answers
26 views
I have a [Tag] table will associated with multiple entity tables, should I create mapping table for each or only one table?
The [tag] table schema:
___________________________________
|column |type |note |
|-------+------------+------------|
|id |bigint |primary key |
|name |varchar(20) | ...
0
votes
2answers
503 views
Two records in the same table must be related somehow
I've inherited a somewhat strange table whose records must somehow be linked to each other. In the real world, these two "structures" are combined into one larger "structure", so ...
0
votes
1answer
25 views
If I add an additional column to handle special values, how will this impact my table size?
I have a table TABLE1 with 400,000 row (by now) that has 20 columns.
I have a column DETAIL that usually stores 100 to 300 characters, that's why its size is 1024, but I have an special case. I need ...
0
votes
1answer
144 views
Index fillfactor and index pages in PostgreSQL
The postgres documentation says index fillfactor affect on "index pages" and says "for heavily updated tables a smaller fillfactor is better". I'm confused that its about update an ...
