IS it possible that a not nullable column can be setup to either a) add value freely with INSERT/UPDATE query b) if value not provided on insert, to avoid null entry added default the value to that of another column? I know that with a computed column I can set the default to be another columns value, but I cannot then override this.

I am building a MySQL database and think using implicit DEFAULTS seems very promising. I am successfully using DEFAULT CURRENT_TIMESTAMP and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for on insert timestamps and on update timestamps. I would like to add a default for a public key. A 32 character unique and random alphanumeric string. Is this possible using the DEFAULT field specification? If so, how? Or must I handle this by creating a random value and checking it's uniqueness, *for the 1 in 1.5^50 chance it's a duplicate*, on INSERT? This article seemed promising, but didn't give me an definitive answers: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/ Thank you in advance

I have two tables with same structure, first column gid as serial, and many other columns after that. I want to insert selected rows from one table to the other. Without serial it is really easy: insert into all_roads select * from new_roads where add_road = 1; But with serial as first column in both I do get error: ERROR: duplicate key value violates unique constraint I really do not want to copy serial number from one table to the other. I rather use default serial value for all new inserted records. Is there a way without writing insert which manually lists all the columns except the first - serial?

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT [¿what goes here?] as missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))

### What is the default column name that should be in [¿what goes here?] in the above example?

In Postgres 9.x, for a column of type UUID , how do I specify a UUID to be generated automatically as a default value for any row insert?

When I use show create table Posts I see this column:

Slug varchar(400) NOT NULL DEFAULT 'uuid()'

This means that the default value is a string. I want the default string to be a real uuid() function. I want it to become:

Slug varchar(400) NOT NULL DEFAULT uuid()

How can I do that? What query should I write for this? No matter what I do inside the phpMyAdmin or Adminer it does not become what I want. --- I have a column that has 'uuid()' as its default value. I want to change to so that it becomes uuid(). I don't want to create a new column. I don't want to drop the column and recreate it. I want to modify it.

In Snowflake, what are the implications of having a default value of null on a non-nullable column? Also, when I describe a particular table in Snowflake, I see a default value of NULL in all columns. When my colleague describes the same table in Snowflake, in the same environment (PROD), they are seeing the default value as null (lower-case) but with all those lower case nulls greyed-out. Anyone out there run into this? What could it mean?

I have a couple of audit columns in my table: - modified_by - modified_date I have an AFTER trigger set up to automatically update these columns whenever data is inserted or modified, with current_user and current_timestamp respectively. Would there be any benefit in also adding these as default column values (I assume not given that inserts are already handled by the trigger)?

I am a complete novice, I hope my terminology is correct. I have a database created long ago. It helps keep track of visitors. The visitor’s data is entered manually, but some fields are calculated and most cannot be empty. There is a field (“group2”) which is unused. I want to start using it. I want it to be pre-filled in by default with “Y”,but be able to change it to “N” (and no other characters and not empty). In my research, it looks like the ENUM function is usable to limit input; I wonder if I am on the right track, and if so, how do you set the pre-filled default to Y?

ALTER TABLE test_table ADD COLUMN a DEFAULT NULL; vs. ALTER TABLE test_table ADD COLUMN a; Both columns will set NULL if column a is not specified. As far as I know, if I add a column into a table with a default value in production database, it could lead to trouble rewriting all rows with default value. Is DEFAULT NULL the same?

Cassandra has added guardrails feature some time ago, bust none of the guardrails have any default value set in the configuration file (see cassandra.yaml ). I generally wonder how can a db admin know what value to set for a specific guardrail, and I'm mainly interested in the values for fields_per_udt_warn_threshold and fields_per_udt_fail_threshold. Is there maybe any document with suggested values, are there actually any data that'd support choosing one value over the other? Or should we rather set these basing on a gut feeling/experience, and guardrails lack defaults, because they're very case-specific? I'm also afraid that if guardrails are disabled by default, no user/db admin will want to set them, not even mentioning a possibility of people being generally unaware of these options (there're so many in the config file already...). Even more so, if you imagine guardrails of only the warn level/soft limits breaking stuff in the long run, e.g. via clogging logs.

I have a database with a table whose primary key is a serial column, or a column with a locally-computed default value that prevents conflicts, for instance: CREATE TABLE foo ( foo_id serial PRIMARY KEY, foo_name text ); I also have a second database, where I use the postgres_fdw foreign data wrapper to access the table in the first database. I’d like to insert a row in the foreign table in the second database, without specifying a value for the primary key, letting the remote server “choose” a value in a conflict-free way. Unfortunately, whenever I try to insert data in the foreign table, without selecting the primary key column, postgres_fdw tries to insert rows with NULLs for the columns that weren’t selected, without using the server-defined default values. Hence it fails as the primary key is defined NOT NULL. As far as I can see, there is not way to use a foreign sequence. I’ve seen that I can define a default value when I create a foreign table but, as I understand it, it is implemented on the local side. Is there a way I can insert a row in a foreign table and let the foreign server use its default values?

I have an account table which is laid out in 3NF:

CREATE TABLE account (
    account_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    account_type_id VARCHAR(15) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    opening_balance DECIMAL(10,2) NOT NULL DEFAULT 51,
    account_number INT(8) NOT NULL,
    sort_code VARCHAR(8) NOT NULL,
    created_date DATE DEFAULT now(),
    branch_id INT NOT NULL,
    PRIMARY KEY (account_id),
    UNIQUE(sort_code, account_number)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

However, I want to know if there is a way to create a trigger that upon inserting into this table a unique sort_code and account_number can be generated. The account number should just be a unique 8 digit, however, the first 3 digits will contain the id of branch_id from a separate branch table, e.g an account belonging to branch_id 700 should have an account number starting with 700. And sort code should have this format "XX-XX-XX" where the X's numbers and this should also be checked to be unique. Is it possible to make a trigger to do this in MySQL/mariadb?

Before all, sorry my very bad english level. Here's my problem. If i have two tables: 'Products' |id | product | |---|---------| |1 | "Fork" | |2 | "Spoon" | |3 | "Knife" | and 'taxes' |id | id_prod | tax | |---|---------|---------| |1 | 1 | 21 | |2 | 2 | 11,5 | If i execute the following command: SELECT product.*, taxes.tax FROM products LEFT JOIN taxes ON taxes.id_prod = products.id I will obtain this result. |id | product | tax | |---|---------|---------| |1 | "Fork" | 21 | |2 | "Spoon" | 11,5 | |3 | "Knife" | NULL | My question is: How i can give a default value when the product is not included in tax? I want to receive a result like this: If the default value is "21" |id | product | tax | |---|---------|---------| |1 | "Fork" | 21 | |2 | "Spoon" | 11,5 | |3 | "Knife" | 21 | How i can do that? Thanks in advance...

I have a table definition in Postgres that use now() for timestamp and current_user() for auditing. date_created date NULL DEFAULT now(), edited_by varchar NULL DEFAULT "current_user"(), The problem is that the server produces queries with explicit null values if not given other instructions. I have no way of changing the query being sent from the server to pg. INSERT INTO test_table (notes, date, edited_by) VALUES('test', null, null); Is there a way to still use these default values somehow? Or is the solution to write a trigger function to solve it?

I can insert multiple rows into a table with default values for all columns the [RBAR](https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/) way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into course default values; end loop; end;$$; Is there a way of doing the same with a single SQL statement?

I'm trying to generate dummy tables with dummy data for testing performance with some SQL tasks. Based on this old thread: https://dba.stackexchange.com/questions/89538/is-there-a-way-to-insert-multiple-rows-into-a-table-with-default-values-for-all I've noticed that with an IDENTITY type, it's no more possible to populate a table with dummy data using the suggested generate_series(1, N) solution. So, how could I insert 1'000 dummy data into, for example, the following table (PG 14), taking benefit from the default value for the field name without overriding the system values for the field id:

CREATE TABLE foo (
	id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	name TEXT DEFAULT md5(random()::text)
);

Trying the following fails:

INSERT INTO foo (id) 
  SELECT generate_series(1, 1000);

-- which results in:
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
SQL state: 428C9

As a workaround, I'm now creating the table with an extra field i as a simple INTEGER to feed this one instead of the id and then drop it after data insertion, but it's not 100% clean to me. I was hoping for something simple and clear as this for example:

INSERT INTO foo DEFAULT VALUES (1000);

but this is obviously an invalid syntax.

The table T has this field +---------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+-------------------+-----------------------------+ | updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---------------+--------------+------+-----+-------------------+-----------------------------+ In MySQL 5.7, doing an update of a record and setting updated=null was automatically setting updated to the current date-time UPDATE T SET ...,updated=null WHERE k=123 ==> +---------------------+ | updated | +---------------------+ | 2022-01-05 22:52:05 | +---------------------+ But doing the same in MySQL 8.0, gives +---------------------+ | updated | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ How can I fix that, is this a MySQL bug, or a new setting (or a change in behavior)?

We have a Rails app powered by Postgresql *v11.4* where I want to add a new column with a default value and a not null constraint like below: ALTER TABLE "blogs" ADD "published" boolean DEFAULT FALSE NOT NULL I know adding a new column with a default value is safe. However, is it still safe when combined with NOT NULL? Or will it lock the database? Thanks!

When creating a new login via the GUI: In the *User Mapping* section, certain databases will have some roles pre-ticked in the Database Role Membership section. What controls this behavior? It's different for each database. Oddly, this does not occur when creating a database user via the GUI. **Edit:** Adding *Script* code generated after filling in username and selecting *Map* on ADCData DB.

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'', DEFAULT_DATABASE=[ADCData], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [ADCData];
GO
CREATE USER [testLogin] FOR LOGIN [testLogin]
GO
USE [ADCData]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testLogin]
GO
USE [ADCData]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [testLogin]
GO

Again, I'm not ticking these role membership boxes - they are pre-ticked once I select *Map* on the database. **Edit II:** I'm not sure of the history of this DB - I'd estimate there's a 60% chance it's been restored from a backup. It also happens when adding **any** login - in the code example *testLogin* is not a login previously used. **Edit III:** Above code was generated by clicking the *Script* button at the *Login - New* dialog. My issue is that the *db_datareader* and *db_datawriter* role options are ticked by default - I want to know what is determining that. Also, I double-checked and there are no triggers at the server or db level. **Edit IV:** My sincere appreciation to everyone working on this question. Unmodified *Guest* db user screenshot is below. No permissions are given, and CONNECT is revoked. I tried briefly granting CONNECT and seeing if that changed the UI, but it was still the same.