Questions tagged [default-value]

For questions relating to DBMS-supplied values that are generated when none are explicitly provided.

Filter by
Sorted by
Tagged with
0
votes
2answers
78 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, [...
0
votes
2answers
32 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 ...
3
votes
1answer
482 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 ...
-1
votes
1answer
3k 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 ...
-1
votes
1answer
51 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` ...
0
votes
0answers
10 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 ...
1
vote
2answers
22 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 ...
0
votes
1answer
29 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 (...
4
votes
1answer
1k 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 ...
0
votes
1answer
2k 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 ...
0
votes
1answer
117 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: ...
5
votes
2answers
2k 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 ...
0
votes
1answer
47 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 [¿...
0
votes
1answer
25 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 ...
0
votes
1answer
23 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 ...
0
votes
1answer
2k 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 ...
3
votes
1answer
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 ...
2
votes
1answer
520 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 ...
0
votes
0answers
265 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, ...
0
votes
2answers
3k 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
1answer
53 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 ...
1
vote
1answer
424 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 ...
0
votes
1answer
97 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 ...
1
vote
1answer
464 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 ...
3
votes
1answer
2k 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: ...
2
votes
1answer
8k 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 ...
1
vote
2answers
98 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 ...
1
vote
0answers
63 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 ...
3
votes
1answer
6k 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'::...
0
votes
0answers
205 views

MySQL listens at port 3306 even after changing it

I changed MySQL default port from 3306 to 12929 in my.cnf in both client and mysqld sections on Debian. After that I restarted MySQL service. Then I ran netstat -tln and saw that MySQL is still ...
20
votes
2answers
5k 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 ...
7
votes
2answers
52k 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. ...
0
votes
2answers
308 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 ...
2
votes
0answers
2k 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) ...
1
vote
2answers
71 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 ...
0
votes
1answer
709 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 ...
6
votes
2answers
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 ...
2
votes
1answer
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
1answer
2k 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 [...
4
votes
1answer
5k views

Cannot alter MYSQL Table (stuck in a invalid default value loop)

Some how I have 2 columns with incorrect default values. Not sure how it got created in the first place as I imported the db from another source. Problem now is, I cannot make any changes to the ...
3
votes
1answer
2k views

How to set column DEFAULT using value of a variable

In a script for setting up a database which uses random UUIDs for primary keys, I have: CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT); CREATE TABLE beta (aref UUID ...
2
votes
1answer
6k views

How can I set TIMESTAMP's default to future date?

I know I can set default value with current timestamp like this ... DEFAULT CURRENT_TIMESTAMP Is there any way to set the column with fixed amount of units based on current timestamp? ... DEFAULT ...
0
votes
1answer
15k views

Adding a default constraint in Oracle while altering a column

I'm new to Oracle. I need to port a SQL Server command to Oracle. I want to alter a column to add a new constraint with a default value. SQL Server command ALTER TABLE <schema_name>.<...
3
votes
1answer
2k views

Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

I create a table type in SQL: CREATE TYPE [dbo].[MyObject_TableType] AS TABLE ( [Name] VARCHAR(MAX) DEFAULT '', [Index] VARCHAR(MAX) DEFAULT '' ) I build a DataTable and populate it with one ...
1
vote
2answers
10k views

How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC?

How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone? I tried the following: CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ( ...
25
votes
2answers
174k views

Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47. Query is ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ; While it is working ...
10
votes
3answers
18k views

Return the uniqueidentifier generated by a default on insert

Goal Retrieve the latest guid value in real time after you have inserted the value in the table Problem Don't know how to do it Info The code should only specify new values for address and zipcode ...
97
votes
2answers
150k views

Default value for UUID column in Postgres

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?
10
votes
3answers
18k views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | +===========+=========+=...
0
votes
0answers
2k views

How do I alter containment to none for partially contained db with default language?

Experimented with Contained DBs in an Always-On Availability Group and decided against them. I want to UN-contain the databases now but when I run: alter database <myDB> set containment = NONE ...