Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
282
questions
-1
votes
0
answers
25
views
Generate DELETE statement for multiple related tables
I am currently using PostgreSQL and I have a database with multiple tables that are related by foreign keys.
I need to remove something from the parent table, but I have a lot of dependent child ...
- 99
0
votes
1
answer
21
views
Error when creating trigger to insert values
I am new to databases and I know this is a simple question but I cannot find an answer anywhere, so I appreciate the help.
I have 2 tables, each with the same structure.
Table1
CaseNum Number(9,0)
...
- 103
1
vote
1
answer
207
views
Postgres GENERATED ALWAYS performance when adding new column
I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:
ALTER TABLE my_table
...
1
vote
3
answers
232
views
Why is NOT there "pg_get_tabledef" that is equivelant of "pg_get_indexdef" for tables in PostgreSQL?
I have searched the docs of PostgreSQL and Google, but I haven't found the function pg_get_tabledef, while pg_get_indexdef exists.
There's no pg_get_tabledef? Maybe it's too difficult to be ...
- 315
0
votes
1
answer
50
views
db2look fails to properly order views dumps
I exported an entire DB2-Luw DB's DDLs with db2look in order to prepare for a Postgres migration, however db2look seems to write the DDLs of the view in an incorrect order, so that some vies might ...
- 1
0
votes
1
answer
41
views
How to rapidly compare definitions of many tables of two PostgreSQL databases?
I have two PostgreSQL databases, the one for producing, and the other for testing.
Both of them have a lot of tables with same table names.
Now I need to upgrade the producing system to the new ...
- 315
0
votes
1
answer
109
views
How to return DDL success in a plpgsql function?
I have hundreds tables in a Postgresql-11 DB, need to be droped.
In order to simplified my work, I coded a function as following:
CREATE OR REPLACE FUNCTION dropTable(
IN tab_name information_schema....
- 315
7
votes
1
answer
1k
views
There is no index in the referenced table where the referenced columns appear as the first column
I have two EXISTING mysql/mariadb tables such as:
CREATE TABLE `ice_product` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`long_description` text DEFAULT NULL,
`long_summary_descr` text DEFAULT ...
- 181
0
votes
0
answers
65
views
what happens on postgres ALTER TABLE when nothing changes?
When doing administration on big production postgres database, it seems that even such ALTER TABLE -ddl queries cannot be safely run, that should be no-operations?
Has anyone looked into Postgres ...
- 31
1
vote
1
answer
530
views
Does adding "NOT NULL DEFAULT X" Column to Postgres cause a table re-write?
Does adding a column with NOT NULL DEFAULT X lock the table and cause a table re-write in Postgres 14?
If it doesn't lock the table and cause a table-rewrite (assuming it's just a metadata change), ...
- 35
1
vote
2
answers
888
views
Is it possible to add a GENERATED COLUMN to a large table without locking/downtime on postgres?
I am trying to add a new column as a generated column to a large table (over millions of rows). This generated column takes in the id column and performs a custom PostgreSQL function and returns it. I ...
- 11
2
votes
3
answers
878
views
How can I add a table column at the first position?
In my PostgreSQL database:
select index from sample;
index
---------
1
2
3
1
2
I want to place the index as below:
select index from sample;
index
---------
1
2
3
4
5
Saddam Khan provided a way to ...
- 284
0
votes
2
answers
88
views
Conditionally execute DDL, short-circuiting syntax error
I'm trying to execute a different create or replace function signature according to version() - since anyarray & anyelement are removed in postgres 14 and replaced by anycompatiblearray & ...
- 115
0
votes
3
answers
240
views
mysql table get lock on adding new field
We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this)
ALTER TABLE `posts_topic` ADD COLUMN `...
0
votes
1
answer
169
views
DB2: Working with concurrent DDL operations
We are working on a data warehouse using IBM DB2 and we wanted to load data by partition exchange. That means we prepare a temporary table with the data we want to load into the target table and then ...
- 101
0
votes
1
answer
894
views
MySQL/Aurora increase column size behavior
I would like to increase the size of a column.
The column currently is varchar(1000) and will be changed to varchar(2000). Null value is already permitted.
The tables has about 2 millions of records, ...
- 101
0
votes
0
answers
140
views
Is it an option to deploy specific schema with objects - procedures, funtions, views to multiples databases?
We have schema utils which we would like to deploy with database objects (procedures, functions, views) to multiple databases on different servers via Azure Devops Pipeline. Is there any option to ...
- 295
1
vote
1
answer
70
views
mysql optimization for intensive schema-altering queries (DDL)
In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now).
...
- 313
1
vote
2
answers
3k
views
Get postgres partitioned table structure by a select
I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres.
Something like \d <table>.
All I find so far is by doing pgdump.
db=> \d ...
- 513
0
votes
1
answer
14
views
What kind of object is made when we make CREATE VIEW, and what sort of commands can be ran on it?
I would like to understand the difference between a table and a View in SQL (Ms Access, if it makes any difference). So I know Create View command can be used to "pull" all fields and ...
- 11
0
votes
2
answers
2k
views
How to drop all indexes not used for primary keys or constraints
I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added.
Is there a way to drop all indexes except the ...
- 209
1
vote
1
answer
1k
views
Postgresql 12 - ddluser and dmluser for same schema dml permission denied on tables [duplicate]
I have created a postgres 12 instance and executed the below command sequence:
psql --host localhost -U postgres -c "CREATE DATABASE mydb"
psql --host localhost -U postgres -d mydb -f /tmp/...
- 111
0
votes
0
answers
101
views
Is it possible to run a query for EVERY DDL change that occurs in a server?
I want to run a query on each and every database that is change in whatsoever DDL change possible.
For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer ...
- 205
0
votes
1
answer
847
views
Oracle generating hash_val from a clob
I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated.
Can this be done perhaps by making the column hash_val a ...
- 105
5
votes
0
answers
607
views
When/how does removing an index cause a table to lock in MySQL?
Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html.
As I understand it, INPLACE means that there is potentially some ...
- 171
1
vote
1
answer
986
views
How to parametrize DDL in a plpgsql procedure?
How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "...
- 154
4
votes
1
answer
14k
views
PostgeSQL: create role/user if not exists?
Is there some way to create a user/role/group in PostgreSQL via SQL only if it does not exist yet? The only solution I have found so far is creating a custom stored procedure, since that supports ...
- 154
5
votes
1
answer
3k
views
Disabling triggers makes DELETE fast, but the table has no triggers?
I am deleting 130k out of 750k rows from a PostgreSQL table.
The first time, it took 8 hours to complete the delete query.
Second time, I added an index to the table, and rebuilt that index. Now it ...
1
vote
1
answer
3k
views
ERROR 1180 (HY000): Got error 5 during COMMIT
We're using Galera cluster and I'm running below statement to make a new table:
create table tab2 select distinct id , report from my_table ;
It runs for sometime and after that it fails/cancel ...
- 348
2
votes
2
answers
943
views
Convert postgres varchar[] to text[] without downtime
As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes.
Is there a way to manage this conversion without the ...
- 283
3
votes
1
answer
1k
views
Postgres varchar(100)[] -> text[] column conversion
I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
- 283
2
votes
1
answer
976
views
Can I make postgres' pg_dump condense the alter table statements in the create table section?
If I use pg_dump to get the schema from my database, I get separate sections for things like keys, constraints etc., like this:
CREATE TABLE public.account (
id uuid NOT NULL,
db_version ...
0
votes
1
answer
262
views
Online schema changes for cloudsql DB
I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.
What I have tried so far:
I tried with pt-online-schema-...
- 348
2
votes
1
answer
878
views
Retrieve Names of Check Constraints Specific to a Column
PostgreSQL 12
When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.
I have a hypothetical table:
CREATE TABLE hypothetical_table (
id SERIAL ...
- 223
0
votes
2
answers
361
views
Add a column with name as a result from a SELECT
I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table.
...
0
votes
2
answers
517
views
Can't rely on INFORMATION_SCHEMA.view_definition?
I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code
select * ...
- 206
2
votes
1
answer
1k
views
Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?
We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for ...
- 123
-3
votes
1
answer
12k
views
What is "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4" for? [closed]
I'm exploring Sakila database. After CREATE TABLE, I saw this syntax. Could any one help to let me know what is this and what it is for? how and when to use it? (I didn't see this in my SQL course)
...
1
vote
1
answer
360
views
rename a column without disrupting users
You can rename a column using the following statement:
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
However, this brings the old column out of commission immediately. Any use ...
1
vote
2
answers
3k
views
get DDL from Mongo database
I haven't done much with MongoDB. But in trying to understand the data that we do have in MongoDB, it makes sense to start with looking at the DDL in the database. What is the simplest way to pull the ...
- 1,520
0
votes
1
answer
146
views
is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra
Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?
- 53
2
votes
1
answer
182
views
Implications of removing unnecessary column from primary key
I have a database in which a number of tables are defined with multiple UUID columns forming the Primary Key when they should instead be defined as Foreign Keys, e.g.
CREATE TABLE [dbo].[SomeTable](
...
- 1,177
0
votes
1
answer
228
views
Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions
I have a view in my production database and I want to create same view in my test database.
I tried this commmand:
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
It ...
- 55
1
vote
1
answer
98
views
Create A Pivot With No SUM() in MySQL
I've seen a lot of examples using MySQL to create a PIVOT but they all use SUM() - using this DDL how can I create a PIVOT in MySQL
Create Table `data`
(
callDate text
,callerName text
,...
-1
votes
3
answers
35
views
Set The Order Query Results Display
I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
0
votes
1
answer
162
views
Mysql : what are the steps for creating a database schema for a big project with many tables?
I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it?
For eg: building the ER model first or something like that
- 93
7
votes
1
answer
2k
views
How to safely replace the old table with its cleaned copy?
I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with
DROP TABLE table;
ALTER TABLE table_cleaned ...
- 239
0
votes
1
answer
152
views
How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?
I created a table in HeidiSQL:
CREATE TABLE `publish_dt` (
`ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
`TS` DATETIME NULL DEFAULT NULL,
`Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci'...
- 115
1
vote
1
answer
810
views
How to get SHOW CREATE TABLE without table borders made of dashes?
When I use SHOW CREATE TABLE in my MariaDb command line client I get this output for my table:
+---------+----------------------------------------------------------------------------------------------...
- 113
0
votes
1
answer
339
views
How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?
We are dealing with very large tables in Sql Azure.
Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends.
Metadata operations are quick ...
- 486