Questions tagged [ddl]
Data Definition Language; defining database structures but not their contents.
282
questions
-1
votes
0
answers
22
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
29
votes
8
answers
70k
views
Query to compare the structure of two tables in MySQL
To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).
Can you think of a query that can compare two tables?...
- 101
0
votes
1
answer
826
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
57
votes
2
answers
55k
views
MATCH FULL vs MATCH SIMPLE in foreign key constraints
I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs.
The default is MATCH SIMPLE. How do they function?
- 172k
1
vote
2
answers
834
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 ...
CommunityBot
- 1
0
votes
1
answer
20
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)
...
- 3,069
1
vote
1
answer
292
views
How to trigger a function with DDL query?
I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored ...
CommunityBot
- 1
1
vote
1
answer
176
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
...
- 2,774
0
votes
1
answer
261
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-...
CommunityBot
- 1
1
vote
3
answers
215
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 ...
- 82.9k
1
vote
1
answer
723
views
lock-ddl-per-table option not working as expected in Xtrabackup
I've recently been getting the following errors on some of my Percona XtraBackup backups:
InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
CommunityBot
- 1
0
votes
1
answer
49
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 ...
- 758
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 ...
- 4,276
1
vote
1
answer
606
views
Can I run DDL in pretend mode in Oracle
I have a script to drop several objects in the database (including queues) -- when it partially fails I need to recreate the objects that have already been dropped to continue testing the script. For ...
- 101
15
votes
5
answers
23k
views
Is there a means to set the owner of all objects in a PostgreSQL database at the same time?
The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however ...
- 427
0
votes
1
answer
332
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 ...
- 11.7k
8
votes
3
answers
6k
views
Speed up creation of Postgres partial index
I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.
My data is completely static, so I am able to insert all data, then create all indexes.
In this 1.2TB table, ...
- 172k
0
votes
1
answer
100
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....
- 47.8k
6
votes
2
answers
10k
views
Basic transactional DDL script in PostgreSQL
PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
- 172k
1
vote
1
answer
486
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), ...
- 47.8k
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 ...
- 97.3k
0
votes
0
answers
64
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
2
votes
3
answers
789
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 ...
CommunityBot
- 1
1
vote
3
answers
3k
views
How to get the DDL for Oracle object without extra characters
When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script.
For example, if I create this sample ...
- 1
10
votes
3
answers
12k
views
PostgreSQL GRANT ALL on mutliple tables using LIKE
I have more than 100 tables named public.test_*
How can I easily GRANT ALL access to the user test to all that tables at once?
I tried with:
GRANT ALL ON TABLE public.test_* TO test;
But it is not ...
- 111
0
votes
2
answers
87
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 & ...
- 47.8k
0
votes
3
answers
231
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 `...
- 76.9k
10
votes
1
answer
5k
views
Reclaim disk space from dropped column without downtime
I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used.
The docs suggest doing a table rewriting ...
- 172k
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 ...
CommunityBot
- 1
3
votes
2
answers
764
views
How to capture sp_refreshsqlmodule event
My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change ...
- 15.8k
0
votes
1
answer
166
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 ...
- 25.1k
8
votes
1
answer
251
views
Disallow new UDFs that contain a cursor
Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
- 2,201
13
votes
2
answers
97k
views
How to use default value of data type as column default?
How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:
CREATE TABLE test
(
col_1 CHAR(12) NOT NULL,
col_2 INTEGER NOT NULL ...
- 172k
9
votes
2
answers
7k
views
Use composite type to create new table
In ORDBMS databases I have seen that complex types can be used:
create type name as( ...)
And I can references those types when creating a new table, like:
create table example (row_name ref(name))
...
- 172k
0
votes
1
answer
900
views
Migrating Oracle Views to SQL Server
In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table ...
CommunityBot
- 1
0
votes
1
answer
867
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
137
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 ...
- 25.1k
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).
...
- 13.8k
5
votes
1
answer
10k
views
"ERROR: there is no parameter $1" in "EXECUTE .. USING ..;" statement in plpgsql
I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this:
CREATE TABLE parent_table (
value integer,
end_time timestamp without time zone
);
CREATE OR ...
- 172k
31
votes
3
answers
14k
views
Constraint to enforce "at least one" or "exactly one" in a database
Say we have users and each user can have multiple email addresses
CREATE TABLE emails (
user_id integer,
email_address text,
is_active boolean
)
Some sample rows
user_id | email_address | ...
- 805
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 ...
- 35k
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 ...
- 47.8k
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/...
- 47.8k
3
votes
2
answers
9k
views
SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints
I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is:
SQL Error: ORA-12991: column is reference ...
- 24.7k
0
votes
0
answers
100
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
16
votes
5
answers
55k
views
MySQL - fastest way to ALTER TABLE for InnoDB
I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.
I want to change the name of one of the columns and add a few more indices.
What is the fastest way to ...
- 181k
5
votes
0
answers
587
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
957
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 "...
- 172k
4
votes
1
answer
3k
views
CREATE SEQUENCE using expressions with psql variables for parameters
I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression.
The specific goal is to automate create-scripts for ...
- 172k