Questions tagged [table]

A data structure that organizes information into rows and columns.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
10