Questions tagged [default-value]
For questions relating to DBMS-supplied values that are generated when none are explicitly provided.
88
questions
1
vote
1
answer
27
views
Can an existing blank field be changed to that it defaults to Y, but can only be changed to N
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 ...
- 11
0
votes
3
answers
994
views
Change the default value of a string column to real uuid()
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() ...
- 101
0
votes
1
answer
39
views
What is the suggested value for guardrail fields_per_udt_warn_threshold?
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 ...
- 103
1
vote
1
answer
267
views
Set a column value using the (remote) default when inserting a row into a foreign table
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 ...
- 193
0
votes
1
answer
56
views
MYSQL: Return a predefined value if result of join is null
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&...
- 23
3
votes
2
answers
4k
views
Inserting dummy data into an empty table having a primary key integer field GENERATED ALWAYS AS IDENTITY
I'm trying to generate dummy tables with dummy data for testing performance with some SQL tasks.
Based on this old thread: Is there a way to insert multiple rows into a table with default values for ...
- 342
6
votes
1
answer
12k
views
Is it safe to use default value with not null when adding a new column?
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" ...
- 265
2
votes
0
answers
76
views
Default roles ticked when creating new login through SSMS GUI
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 ...
- 21
3
votes
2
answers
8k
views
Use default value instead of inserted null
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"(),
...
- 333
0
votes
0
answers
193
views
How to specify default CREATE TABLE values just once?
Is there any way I can just say once that the default type should be TEXT, and the
default value should be '' for all CREATE TABLEs?
Currently I must do:
CREATE TABLE "t" (dbm TEXT DEFAULT ''...
- 101
0
votes
1
answer
3k
views
MySQL 8.0 timestamp CURRENT_TIMESTAMP sets date to '0000-00-00 00:00:00' (was ok in 5.7)
The table T has this field
+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra ...
- 463
0
votes
1
answer
382
views
How to create a trigger that generate a random specific output to insert into a table in MariaDB?
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 ...
- 13
1
vote
2
answers
1k
views
SQL Server - how to define default value function that require column name?
Column names are not permitted when declaring default column value. Is there a workaround to achieve something like this?
CREATE TABLE [dbo].[Tasks]
(
[TaskId] INT IDENTITY(1,1) NOT NULL,
[...
- 127
0
votes
2
answers
61
views
Conditionally use computed column
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 ...
- 153
3
votes
1
answer
2k
views
Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7
I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code ...
- 755
0
votes
1
answer
27k
views
How to set default value as empty string for all rows at once in MySQL while creating the table
In MySQL, while creating a table, I'like to have empty string '' as value for all needed columns instead of 'NULL'.
Is it possible, is it safe and how can I do to do that at once?
I have the following ...
- 13
-1
votes
1
answer
240
views
Nullable integer as part of composite primary key
I want to store phone numbers as integers and I've created this SQL:
DROP TABLE `user_id_phone`;
CREATE TABLE `user_id_phone`
(
`user_id` int unsigned NOT NULL,
`country_code` ...
- 99
0
votes
0
answers
12
views
change db column default from NULL to <null>
When inserting rows in table, a default value is a NULL. Is their a way to make
"<null>"
such that,
+--------------+-------------+------+-----+-------------------+----------------+
| Field ...
- 101
1
vote
2
answers
686
views
Insert to a table using function with default arguments
I'm trying to write a function that makes an insert to the table defined below:
DROP TABLE IF EXISTS pl_table;
CREATE TABLE pl_table
(
id SERIAL PRIMARY KEY,
mandatory VARCHAR NOT ...
- 111
4
votes
1
answer
2k
views
creating new table partition with values already existing in the default partition
I have the following table:
CREATE TABLE orders
(
info_date date,
country_code VARCHAR,
order_total int,
CONSTRAINT orders_pk PRIMARY KEY (info_date, country_code)
) PARTITION BY LIST (...
- 197
6
votes
1
answer
4k
views
Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement?
In mysql 5.7.28 I create a table like this:
create table t1 (
id int not null,
d1 timestamp)
engine=innodb;
which works fine and creates d1 as non-null with default value current_timetamp,on update ...
- 163
0
votes
1
answer
6k
views
What is the default quota on a tablespace for a given user in oracle if you don't set one?
My environment
I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Explanation of the situation
I was creating a user with the following command
create user ...
- 127
0
votes
2
answers
740
views
Insert records from one table to other with default serial
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:
...
- 101
10
votes
2
answers
11k
views
Is DEFAULT NULL and 'nullable value without default' completely the same?
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 ...
- 329
0
votes
1
answer
305
views
What is the default column name given to a TABLE() type with a single column
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 [¿...
user68575
0
votes
1
answer
34
views
Alternative defaults with a different table name
Say the original table has DEFAULT nextval('some_sequence'). I'm looking for a way to specify DEFAULT nextval('another_sequence') on a table view/alias so that I can just insert into the alias as ...
- 699
0
votes
1
answer
154
views
Defaulting a column to the 1st of next month
I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this:
ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL ...
- 103
1
vote
1
answer
3k
views
How can I create a unique and random alphanumeric string using MySQL's Default Value? (v 5.6)
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 ...
- 111
3
votes
1
answer
2k
views
SQLServer add a default value for a column [closed]
I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying ...
- 139
2
votes
1
answer
701
views
Missing right parenthesis after (or before) default 0, Oracle
I am trying to create a table with Oracle SQL where two columns of the table should have a default value and should not be NULL.
I am getting the error you can see below, but i am not able to ...
- 195
0
votes
0
answers
454
views
DROP DEFAULT on psql restore fails because column not existing anymore
A dump creates a command to remove default values like:
ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT;
When on restore the column with the default value do not exist anymore, ...
- 131
0
votes
2
answers
8k
views
Default values in function, arguments dependency, PostgreSQL
I would like to write a function with two arguments, which would have dependency. Something like this:
CREATE OR REPLACE FUNCTION years_compare(
IN year1 integer DEFAULT date_part('year'::...
3
votes
1
answer
149
views
How to replace default on a table
I have a very big table and added a new NOT NULL column with DEFAULT on it. Not an unusual thing in DWH environment. It is an online operation, it works very quickly, and stores default in a table ...
- 345
1
vote
1
answer
867
views
Why is cross db ownership chaining on by default for system DB
I am reading about cross db ownership chaining Server Configuration Option
It talks about how and why to use (or not) this option and says
Setting cross db ownership chaining to 1 is not ...
- 6,178
0
votes
1
answer
458
views
Performance: ALTER TABLE with default value vs. iterating over all records to set new value [closed]
For adding values to a new column, I have the following options:
Using default to add value during ALTER TABLE command.
Doing ALTER TABLE without default, then fetching all the records and iterating ...
- 111
1
vote
1
answer
1k
views
How do I an add a DEFAULT on a column if the table already exists?
I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT
You can see the example in their docs,
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT ...
- 21
3
votes
1
answer
4k
views
Convert varchar column to inet
I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried:
...
- 55
2
votes
1
answer
9k
views
How can I set a default now() timestamp in a timestamp array?
I want to have a timestamp array which tracks a series of events. The first event always happens when the row is created, so I want the array to be instantiated with an initial default value. I've ...
- 783
1
vote
2
answers
137
views
Is there an sp_reset or the equivalent?
Let's say I was monkeying around on SQL Server and I wanted to reset everything to factory defaults, is that currently possible?
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'stupid ...
- 62.1k
1
vote
0
answers
80
views
How to save SSMS 2012 Object Explorer Scripting Settings?
I'm generating a bunch of object creation scripts in SSMS 2012, using the Object Explorer context menu. The default options for these generated scripts are not to my liking (for example, I want the ...
- 141
5
votes
1
answer
10k
views
Insert Into table ommitting columns that have default values
I have a PostgreSQL database with lots of tables of the same structure, 36 columns in total:
CREATE TABLE some_schema.some_table (
id integer NOT NULL DEFAULT nextval('some_schema.id_seq'::...
- 153
20
votes
2
answers
6k
views
DEFAULT CONSTRAINT, worth it?
I usually design my databases following next rules:
Nobody else than db_owner and sysadmin have access to the database tables.
User roles are controlled at application layer. I usually use one db ...
- 23.6k
10
votes
2
answers
97k
views
PostgreSQL, what is the hostname address of my default database?
I just created a PostgreSQL database on my laptop (following these instructions), which is running Linux CentOS 7.
Now I would like to understand what is the hostname address of my default database. ...
- 327
0
votes
2
answers
532
views
List default column values for all tables in a database [closed]
I would like to list the default values for all columns in all tables in an Oracle database. The objective is to review the default values, and find any that need updating.
There are hundreds of ...
- 1,447
2
votes
0
answers
3k
views
Something similar to GENERATED ALWAYS for a DEFAULT? (SQL Server / Azure SQL)
Question: Is there any way to force a column to always use the default value definition? I would like to always have the user_id populated on an insert/update (I know delete might be a bit different) ...
- 169
1
vote
2
answers
103
views
PostgreSQL to SQL Server Migration Error Caused by Default Value Expression
I am working on integrating databases from both MySQL and Postgres into SQL Server. My data migration from Postgres to SQL Server is failing because of the default value expression in Postgres.
This ...
- 225
0
votes
1
answer
1k
views
In SQL Server, how do you create a default constraint for a column using the result or return value from a Stored procedure?
In SQL Server, can you create a default constraint for a column using the result or return value from a Stored procedure, and if so how?
So I am thinking of something along the lines of this, but ...
- 447
6
votes
2
answers
1k
views
Can you use an inserted variable in a default constraint
Working in SQL Server, let's say I have a database with two tables: events and competitors.
events has the following columns:
[event_id] [event_name] [winner_id] [loser_id]
competitors has the ...
- 101
2
votes
1
answer
1k
views
Calculated Field Updating Every Row
I added a calculated field to my table, and only want to update NULL rows (or "new" rows). Something is going awry, and every row in my table is being updated with the current DateTime when 1 row is ...
5
votes
1
answer
3k
views
In SQL server is a check constraint applied before or after a default constraint on insert?
As per the title: In SQL server is a check constraint applied before or after a default constraint on insert?
So taking the following table (Unnecessary columns omitted for brevity)...
CREATE TABLE [...
- 447