Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [default-value]

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

1
vote
1answer
56 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
34 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
55 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
287 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: ...
1
vote
0answers
24 views

Best Practices/Recommendations for postgresql default values [closed]

Are there any recommendations for specifying default values for NOT NULL fields? I want to create a general rule for NOT NULL default values based on the data type: bigint, smallint, char, varchar, ...
2
votes
1answer
2k 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
51 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
39 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 ...
1
vote
1answer
812 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
138 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 ...
17
votes
2answers
2k 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 ...
3
votes
2answers
11k 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
154 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
819 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
52 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
350 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 ...
5
votes
2answers
523 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 ...
3
votes
1answer
650 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 ...
4
votes
1answer
1k 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 [...
2
votes
1answer
2k 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 ...
2
votes
1answer
916 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 ...
1
vote
1answer
2k 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
8k 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
1k 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 ...
0
votes
2answers
4k 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( ...
9
votes
2answers
44k 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 ...
6
votes
4answers
7k 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 ...
45
votes
2answers
44k 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?
7
votes
3answers
8k 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
827 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 ...
0
votes
1answer
627 views

Using default values in primary key

Good day. I have a table like this: CREATE TABLE mytable ( myname varchar(30), mydate date DEFAULT CURRENT_DATE, PRIMARY KEY(myname, mydate) ); EDIT: Forgot to mention I am using a view ...
0
votes
1answer
40 views

Assign value to a field after checking a condition

I have this query: ALTER TABLE MERCHANTS ADD COLUMN merchant_image bytea, ADD COLUMN merchant_logo bytea, ADD COLUMN merchant_address VARCHAR(100), ADD COLUMN merchant_phone VARCHAR(50), ...
4
votes
2answers
5k views

Default value for “microtime” column in MySQL

My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function. ...
0
votes
0answers
207 views

What are the built-in default configuration values of redis.conf?

If I omit a configuration option in redis.conf, what value will be used upon running redis? The self-documented redis.conf states that just rarely. I have found (but didn't try yet) this command: ...
1
vote
2answers
3k views

MYSQL find if a default value has been set for a table (DateTime Specifically)

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. ...
1
vote
0answers
98 views

How to pass the value of PK to FK

Ok, so here it goes. I have a table called employee. then I have another table called languages. I need to copy the value of PK of employee table to foreign key in Languages table, rather than ...
1
vote
1answer
1k views

Foreign key default value

I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have ...
3
votes
1answer
1k views

Reg: Default Privileges on PostgreSQL Schemas

I have a situation where many users can create database objects across all the available schema. There is a generic user pg_dump_user used for by all the developers to dump the whole database to ...
1
vote
0answers
38 views

Uninitialized values: ignore or set?

From the implementation point of view for a database, is it preferable to handle optional form fields by setting to some default value (e.g. false, 0, "", etc.) or just ignore such fields (do nothing ...
1
vote
2answers
123 views

default constraint issue

I have a table TempTable with one column having a default constraint ALTER TABLE [dbo].[TempTable] ADD CONSTRAINT [DF_TempTable_Version] DEFAULT ((1)) FOR [Version] Version is defined as decimal ...
0
votes
1answer
908 views

Create a default value for session valid_until MySQL

Server version: 5.6.22 Homebrew The table: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `date_created` datetime ...
13
votes
1answer
9k views

Is there a way to insert multiple rows into a table with default values for all columns?

I can insert multiple rows into a table with default values for all columns the RBAR way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into ...
1
vote
0answers
30 views

PostreSQL execute a default query when no row are returned [duplicate]

I need to calculate the average score of a list of userId. I have a table "user_place_score" (user_id / place_id/ score). Sometimes I do not have the score for a user_id so I need to fetch a "...
1
vote
3answers
662 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the table) ...
0
votes
0answers
372 views

Default value for SQL Server CE 4.0 column

I am writing a tool to convert a system from XML data files into a SQL Server CE format. Not the most efficient way to do this, but essentially it will be digesting XML files created by another ...
0
votes
2answers
206 views

In SQL Server what is the database cost for setting a default value on table that already has over 1mil records?

NOTE: I am not a dba. :) I have a database table that a client needs to set a column's default value. This table has 1.5mil+ records in it and this column is already populated on all existing records ...
0
votes
1answer
98 views

SQL Server - Is it possible to forbid creation of default constraint name?

I was having a looking on this article about default constraints and I have a question. Is there a configuration in SQL Server 2008 that forbid naming creation of new default constraints declared ...
3
votes
2answers
7k views

Why is add timestamp column with default not null so slow in Oracle 11g?

In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production. This ...
2
votes
1answer
122 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
0
votes
1answer
287 views

Why is column default value not portable through different RDBMS?

Some ORMs (doctrine) do not support default values in database schema definition, because they states it is not portable. But SQL92 supports the DEFAULT value clause, and all the databases supported ...