Questions tagged [table]
A data structure that organizes information into rows and columns.
506
questions
0
votes
1
answer
24
views
SQL Summing a column value based conditional on another table's value
I'm working on a SQL query that returns a SUM of a value in a column/table based off a related value in another table's column being met. I'm getting an error:
The multi-part identifier "Charges....
user212215
0
votes
0
answers
11
views
Table Prefix Problem or Not?
I am trying to determine if I actually have a db table prefix problem or not in my wordpress db. I am on mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu)).
Status Quo:
I changed my ...
- 61
0
votes
1
answer
27
views
DEFERRABLE not working as expected in Postgres
My app has the requirement that every table has a "created_by" column that references to the id column of user table. Even the user table. That means the created_by column would be a ...
- 155
0
votes
1
answer
42
views
How do I sync MySQL DB table
I have 9 MySQL servers in different environments. One of them is in the testing server. That one will be the first server if I have to make the change.
Is there any way I can sync the table from the ...
- 111
-3
votes
1
answer
55
views
Retrieving range of values from a lookup table using SQL
I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
1
vote
1
answer
53
views
Flatten a JSONB file - What function/query
In a bit of a pickle at work - I have a Jsonb which I need to flatten a file that has similar structure:
{
"Manufacturers":[
{
"manufacturer":"Tesla",
...
- 23
0
votes
0
answers
28
views
Keeping a table up to date that has been created using a DB Query
I have created a new table view for customers, so that when they log in, they only see the information that they need to see...
I used the Query:
CREATE TABLE IF NOT EXISTS Customer( SELECT ColumnA, ...
- 111
2
votes
1
answer
39
views
Getting all shared values of two or more given values in one table (SQL)
I have a chat database where each user belongs to a chat; this relation is represented in the table "is_member(uid, cid)". Now, when creating a new chat, I want to check if there is not ...
- 23
0
votes
0
answers
28
views
Data modeling for employees with manager's record updated weekly
I would like a best practice or some direction on how to handle employee data, as I just took ownership of the database management
The table employee looks like this
id
employee
manager
week
score
1
...
- 101
0
votes
0
answers
4
views
Trying to input table-format data using Access Data Input form
We're trying to format and input some scientific data into an Access database, and unfortunately the source of the data is in Excel files with very inconsistent formatting, so we can't use a blanket ...
2
votes
1
answer
80
views
Mysql - Table structure to hold history log without any primary key
I have mysql table that holds log data for some products, this data are deleted and inserted by a service every 5 minutes.
This is the table:
| id | product_id | dev_stage_id | production_order | log |...
- 121
-3
votes
1
answer
149
views
How to convert data separated pipe to rows?
I work on sql server 2017 I have table name #countriesData data as below
my issue here How to convert countries column data separated pipe to rows .
meaning every rev and company and year will have ...
- 103
0
votes
1
answer
103
views
Grant All on All tabes in schema?
I have a question about grants...
I ran the following command, assuming it would allow me to make any changes to the tables in the public schema:
grant all on all tables in schema public to User1;
...
- 11
1
vote
1
answer
37
views
Adding a column to a table then updating the table
I have a code that looks something like this
declare @query = '
Alter Table Temp
Add NewColumn int
update Temp
set NewColumn = 100'
exec (@query)
It will give error NewColumn does not ...
0
votes
0
answers
82
views
Large MySQL table has become unresponsive due to size, what can be done to rescue it?
I am currently building a custom project which collects large amounts of data, and I am now in a process of processing that data. One particular table, which holds all of the data that's been ...
- 101
19
votes
6
answers
3k
views
Is there a generic term for tables and views?
I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso.
As far as I understand, 'entity' is ...
0
votes
1
answer
220
views
Table design to store Refund Transaction information
I have a transaction table that contains purchases and refunds. I'd like to create a linkage between purchase and refunds in the same table (I decided not to use a separate table). I'm not sure which ...
- 1,244
1
vote
1
answer
199
views
DB strategies to store data in CSV files?
I'm new to the Postgres community and would love to hear your recommendations.
To give a bit more context, each file is associated with a unique zip code and address and contains 8,760 (365 days x 24 ...
- 13
0
votes
1
answer
62
views
Oracle - Big Table Archiving
Version: Oracle 12cR2 EE
The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2
Table size: 95GB
Table row count: 20M
Table partitioned? No
Indexes on table: there ...
- 29
-1
votes
1
answer
18
views
Table normalization of given table
I've got a table and I'm supposed to figure out why it breaks with 1NF, 2NF and/or 3NF.
I know the differences between these three, but I'm bad at telling what attributes are primary keys and what is ...
- 11
2
votes
1
answer
92
views
How would you design a table to store Configuration or a Setup in the following Questionnaire scenario?
I have a table named Questionnaire which will contain binary YES/NO type of questions with following columns.
Id INT
Question VARCHAR
GroupId INT
1
Are you a female?
1
2
Are you healthy?
1
3
Are ...
- 21
0
votes
1
answer
76
views
Help with Database Design for a CRM
I am creating a CRM for a call-center and I want to store the input field data in a database.
My problem is that I am quite new to this ordeal and have little - to no idea how to structure my database....
- 1
0
votes
0
answers
86
views
How to specify default CREATE TABLE values just once?
Is there any way I can just say once that the default type should be TEXT, and the
default value should be '' for all CREATE TABLEs?
Currently I must do:
CREATE TABLE "t" (dbm TEXT DEFAULT ''...
- 101
0
votes
0
answers
2k
views
Linked server error- The table either does not exist or the current user does not have permissions on that table
There is a view that is being called from Server A (SQL 2016) and this view needs to read data from Server B (SQL 2014). There is a linked server set up between these two servers and the connection ...
- 63
0
votes
1
answer
230
views
Best practice for SQL table names when using sub namespaces that exceed maximum table name length
I have a MySQL database that includes many tables named according to a namespace that uses sub names and underscores (_ as in a_b_c), such as the following:
life_domain_kingdom_phylum
...
- 134
0
votes
1
answer
318
views
Strong vs weak entity types on SQL
I have a 3NF normalized database design and was trying to understand the concept of strong vs weak entity types and my understanding is weak entity types are tables that don't have a primary key. ...
- 21
-2
votes
1
answer
334
views
Count rows from multiple tables with similar names
I want to have 2 columns: Table Name and Row Count
When I run this, I get 12 table names in my database. How do I then count the rows in each one without having to union 12 count queries together:
...
0
votes
1
answer
164
views
Is pg_database a catalog or a table?
I am learning about PostgreSQL and am trying to understand basic concepts like schemas, catalogs and tables. I have been reading PostgreSQL: Up and Running, and to quote the book:
Schemas are ... the ...
- 193
1
vote
0
answers
132
views
How to log changed table fields in SQLite?
I have a table that looks like this
CREATE TABLE IF NOT EXISTS "ebooks"
(
"id" INTEGER PRIMARY KEY,
"name" TEXT NOT NULL,
"author"...
- 25
0
votes
1
answer
3k
views
Export Postgres data without superuser privileges
I'm using Postgres 14 on CentOS 7. I would like to export a single table's worth of data but I don't have superuser privileges. Unsurprisingly, I get this error
myapp=> COPY myapp_currencyprice ...
- 515
0
votes
0
answers
29
views
Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?
Assume A, B, C etc are attribute values in the current rowset
Current function logic is similar to:
Case A = 'Y' then
Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales'
Case B in (99,98,102) OR D ...
- 257
2
votes
2
answers
615
views
Should I split semantically almost identical entities into different tables?
Suppose you have 2 different entities:
Entity 1: PersonalNote - a note you create for yourself
Entity 2: SharedNote - a note you share with someone else
A PersonalNote can also be viewed as a ...
- 129
-1
votes
1
answer
32
views
Command basics, look through three tables [closed]
Hi, I'm starting to learn mysql and am confronted with the commands. I think I understand the basics, but with this example I'm failing to set up the commands correctly.
The underlined keys are ...
-1
votes
1
answer
44
views
Percentage in table group [closed]
I use PostgresSQL. Given table t1, I would like to obtain result. How to? The numbers on table result are the numbers on column value1 divided by their sum. I utilize PostgresSQL server. I thank you.
...
- 117
0
votes
1
answer
216
views
One lookup table per type or one lookup table to handle multiple types
We are creating a lookup table that will handle multiple types of data. For simplicity, I'm only including 2, but there are actually 6. The table will store static data for each type.
We decided to ...
- 101
0
votes
1
answer
98
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 ...
- 640
0
votes
1
answer
145
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
1
vote
0
answers
32
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 ...
- 11
0
votes
0
answers
62
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, ...
- 287
2
votes
3
answers
706
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-...
- 31
1
vote
2
answers
510
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,
[...
- 121
0
votes
1
answer
1k
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
...
- 101
-2
votes
1
answer
37
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 ...
- 1
0
votes
1
answer
74
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), [...
- 3
0
votes
1
answer
80
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 ...
- 159
0
votes
2
answers
515
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
0
answers
25
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 ...
- 11
0
votes
2
answers
23
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 ...
- 75
0
votes
1
answer
45
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 ...
- 1
0
votes
1
answer
51
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 ...
