Questions tagged [table]

A data structure that organizes information into rows and columns.

Filter by
Sorted by
Tagged with
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....
's user avatar
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 ...
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 ...
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 |...
-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 ...
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 ...
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 ...
-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 ...
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 ...
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 ''...
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 ...
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. ...
-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"...
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 ...
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 ...
-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. ...
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 ...
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 ...
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 ...
-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), [...
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 ...
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 ...
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 ...

1
2 3 4 5
11