Questions tagged [table]
A data structure that organizes information into rows and columns.
461
questions
0
votes
0answers
20 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
31 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
23 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
23 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
40 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
26 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
...
1
vote
1answer
76 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
25 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
15 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
39 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
23 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
16 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
40 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
20 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 ...
4
votes
2answers
412 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
18 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
46 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
34 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
86 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
18 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
33 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
302 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
26 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
22 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
0answers
10 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
31 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
52 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
31 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
51 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
42 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
25 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
0answers
19 views
Can view tables from command line but not from pgadmin
I restored a cluster from base backups. The version of postgresql is 12.4. After starting the postgresql service, and logging into the cluster (from putty), I can see the dbs, and even the tables in ...
0
votes
2answers
138 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
23 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
101 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 ...
0
votes
1answer
23 views
Default value for CHARSET when creating a table
I came across this query in a book i'm learning from.
CREATE TABLE `articles` (
`title` varchar(1000),
`content` text,
`links` text,
`image` varchar(500),
`url` ...
0
votes
1answer
190 views
Schema pg_dump failed due to a Lock on a table
I'm running backup restore on a schema every day and get this every now and then:
pg_dump: Error message from server: ERROR: relation not found (OID
86157003) DETAIL: This can be validly caused by ...
-2
votes
1answer
46 views
deleted Database data comming back overnight
I am a developer i am facing a situation where Web Application deletes data, on the next morning the deleted data is back on the Database. Yesterday some of our users spent the whole day deleting ...
2
votes
1answer
27 views
compare the variance of sales from one day to another on the same table
I got this table on a SQL Server named "sales" that shows the sales per day for each vendor, this is the current table:
+-------+-----------+-----------+--------------+
| id | vendor | ...
0
votes
1answer
42 views
Left Join table with first match historically
What I want to accomplish is to join TableB to TableA where TableA.date <= TableB.date, but only the the value with the nearest max date to Table.B.date. Currently, what I get is all the previous ...
1
vote
1answer
42 views
SQL Trigger that creates new table when a new record is added to current table
It's been quite a few years since I have done DB work. I am working through mysql workbench (using MS Access as a frontend).
I have a base table that I am multilinking (basically a name index type ...
1
vote
1answer
90 views
Break down calendar table based on custom dates in SQL Server 2017
I have a table that contains the following fiscal year definitions:
FiscalYear | Period | PeriodStartDate | PeriodEndDate
==================================================================...
0
votes
1answer
198 views
How to set current sequence while converting primary key serial(integer) to bigserial(bigint)
The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for biginteger?
--Create new temp sequence (sequence-name1) ...
0
votes
0answers
49 views
Create column referencing multiple PK's from same table
I am trying to create an table, menu items, to use in my front end.
Menu items can have 1 or many submenu items.
So one column must be able to contain 1 or more ID's from other menu item records.
...
1
vote
1answer
43 views
Delete more than 50K records in SQL table
We have 500k records in a SQL table. Below is table structure
UserID varchar
FirstName varchar
LastName varchar
Address varchar
We need to delete 50K records in this table. We tried below approach
...
0
votes
1answer
14 views
copy a sqlite3 table preserving custom _id
I'm trying to copy a table whose _id field is given by integers with some gaps, e.g. 1,2,5,7, presumably due to deleted messages.
(This is a toy-model for a Whatsapp database I'm manipulating.)
I'd ...
0
votes
1answer
25 views
Same server, same query, different response time
I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:
* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* ...
-1
votes
1answer
29 views
Better Database Destructing [closed]
what is the best way to destruct database tables,
suppose that we have two tables TRADER and PRODUCTS, what is the best way to destruct the tables:
create TRADER_ID Column in PRODUCT_TABLE
make a ...
0
votes
0answers
71 views
BFILE datatype in mysql
My backend is writing The Soap request within a table using a BLOB column so that I have very big tables in mysql with more of 100GB.
I need store this information out of dabase in file for example.
...
1
vote
1answer
22 views
How to design two tables: A hasMany B, and one B per A has state 'active'?
Figure two tables: environments and branches.
Their relationship is: one environment has many branches. This is easy with an environment foreign key in the branches table.
The problem is adding this ...
