Questions tagged [ddl]

Data Definition Language; defining database structures but not their contents.

Filter by
Sorted by
Tagged with
0 votes
0 answers
28 views

How to get last executed statement on a particular table in Oracle?

Trying to identify what ghost process is inserting records into a particular table. Is there a way to get the exact query?
Dhaval Mohandas's user avatar
0 votes
0 answers
16 views

Rename table taking too long on MySql 8

We recently upgraded from MySql 5.7 to 8 and have run into this issue. We have a rename table operation that runs every minute. It used to take a fraction of a second, but now it takes over 6.0 ...
Faheem's user avatar
  • 101
0 votes
1 answer
101 views

Extraneous Unique Index in Oracle DB DDL

I exported the DDL for the EMPLOYEE table in an Oracle DB. What is this index? What columns is this index for? CREATE UNIQUE INDEX "MY_USER"."SYS_IL0000126968C00005$$" ON "...
Nathan's user avatar
  • 113
1 vote
2 answers
46 views

Can I get the created time of a PostgreSQL DB?

I'm using PostgreSQL-13.0. Is there a way I can get the created time or last modified time of a database? Thanks!
Leon's user avatar
  • 347
0 votes
1 answer
40 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) ...
bk_32's user avatar
  • 103
1 vote
1 answer
462 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 ...
Rodrigo Ce Moretto's user avatar
1 vote
3 answers
356 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 ...
Leon's user avatar
  • 347
0 votes
1 answer
55 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 ...
Nic Can's user avatar
0 votes
1 answer
44 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 ...
Leon's user avatar
  • 347
0 votes
1 answer
143 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....
Leon's user avatar
  • 347
7 votes
1 answer
2k 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 ...
lrkwz's user avatar
  • 181
0 votes
0 answers
73 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 ...
anttik's user avatar
  • 31
1 vote
1 answer
715 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), ...
Bryce's user avatar
  • 35
2 votes
2 answers
1k 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 ...
Hyperion's user avatar
2 votes
3 answers
1k 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 ...
showkey's user avatar
  • 324
0 votes
2 answers
106 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 & ...
OJFord's user avatar
  • 115
0 votes
3 answers
294 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 `...
NARESH KUMAR TELI's user avatar
0 votes
1 answer
190 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 ...
user2323596's user avatar
0 votes
1 answer
998 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, ...
javaTry's user avatar
  • 101
0 votes
0 answers
154 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 ...
adam.g's user avatar
  • 345
1 vote
1 answer
76 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). ...
BogdanSorlea's user avatar
1 vote
2 answers
4k 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 ...
Nir's user avatar
  • 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 ...
Sasha1001's user avatar
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 ...
ITChap's user avatar
  • 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/...
igiannak's user avatar
  • 111
0 votes
0 answers
106 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 ...
Ali EXE's user avatar
  • 205
0 votes
1 answer
953 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 ...
Pugzly's user avatar
  • 105
5 votes
0 answers
661 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 ...
sandinmyjoints's user avatar
1 vote
1 answer
1k 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 "...
Jakub Holý's user avatar
5 votes
1 answer
16k 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 ...
Jakub Holý's user avatar
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 ...
Karthik Ganesan's user avatar
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 ...
channa's user avatar
  • 350
2 votes
2 answers
1k 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 ...
Felipe's user avatar
  • 293
3 votes
1 answer
2k 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 ...
Felipe's user avatar
  • 293
2 votes
1 answer
1k 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 ...
Sebastiaan van den Broek's user avatar
0 votes
1 answer
273 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-...
channa's user avatar
  • 350
2 votes
1 answer
939 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 ...
Avocado's user avatar
  • 223
0 votes
2 answers
417 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. ...
Alfredo Marin's user avatar
0 votes
2 answers
595 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 * ...
T.S.'s user avatar
  • 216
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 ...
leslie.zhang's user avatar
-3 votes
1 answer
13k 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) ...
Huong Truong's user avatar
1 vote
1 answer
433 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 ...
Carl Patenaude Poulin's user avatar
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 ...
Gandolf989's user avatar
  • 1,520
0 votes
1 answer
152 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?
Gary Sharpe's user avatar
2 votes
1 answer
192 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]( ...
David Clarke's user avatar
  • 1,187
0 votes
1 answer
237 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 ...
jrdba's user avatar
  • 55
1 vote
1 answer
109 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 ,...
jamesMandatory's user avatar
-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 ...
jamesMandatory's user avatar
0 votes
1 answer
172 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
Fakipo's user avatar
  • 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 ...
Dmitriy Grankin's user avatar

1
2 3 4 5 6