Skip to main content

Questions tagged [ddl]

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

Filter by
Sorted by
Tagged with
0 votes
2 answers
40 views

Why must I commit after the ALTER TABLE DDL to make changes visible?

If I execute this in DBeaver: alter table classes alter column reference set not null; on my local database which only I have open in DBeaver, it completes successfully. But then I can't open the ...
John Little's user avatar
0 votes
1 answer
34 views

Create table that has a sequence column counting from 1 for every different foreign key

Is there a way to define a table that has a sequence column counting from 1 for every different corresponding foreign key column? I want a "local" serial for every foreign key - sorry for ...
Greenhill's user avatar
  • 101
0 votes
1 answer
51 views

How to update rows in GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP without changing timestamp

On Db2 11.5 on Linux I have (simplified example) the following table: CREATE TABLE ADMIN.TAB1 ( ID INTEGER NOT NULL, COL_TEXT CHAR(10), DATE_TIME TIMESTAMP NOT NULL GENERATED ...
folow's user avatar
  • 357
3 votes
2 answers
269 views

Why does MySQL generate a `key` statement in DDL for composite primary keys?

I am using MySQL version 8.3.0. I can create a “many to many” relationship between persons and teams tables with the following SQL statements: create table persons ( id bigint not null, last_name ...
FloT's user avatar
  • 356
0 votes
0 answers
13 views

How to display the identifier after the execution of a DDL statement?

Postgresql (the command line tool psql) reports this after each DDL statement: DROP SCHEMA CREATE SCHEMA SET CREATE TABLE CREATE FUNCTION CREATE TABLE CREATE TABLE CREATE INDEX CREATE INDEX CREATE ...
ceving's user avatar
  • 339
0 votes
1 answer
56 views

Extra `CASE WHEN` Removes Text from Dynamically Created String

I am trying to generate a CREATE TABLE statement based on a given table in my SQL-SERVER database using the INFORMATION_SCHEMA tables, but when I try and concatenate the DEFAULT portion of a field ...
javery's user avatar
  • 3
0 votes
0 answers
108 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
43 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
107 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
85 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
  • 357
0 votes
1 answer
78 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
794 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
607 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
  • 357
0 votes
1 answer
71 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
48 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
  • 357
0 votes
1 answer
207 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
  • 357
8 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
  • 191
0 votes
0 answers
77 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
1k 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
3 votes
2 answers
2k 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
2k 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
  • 334
0 votes
2 answers
129 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
  • 125
0 votes
3 answers
401 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
228 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
1k 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
172 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
  • 355
1 vote
1 answer
80 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
  • 533
0 votes
1 answer
15 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
3k 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
  • 219
1 vote
1 answer
2k 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
117 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
  • 215
0 votes
1 answer
1k 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
743 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
19k 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 ...
Shiwangini's user avatar
2 votes
2 answers
2k 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
  • 305
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
  • 305
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
315 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-...
Shiwangini's user avatar
2 votes
1 answer
1k 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
506 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
819 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
2k 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
15k 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
573 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
158 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

1
2 3 4 5 6