Find the Default Value of a Column in SQL

1. Introduction

When working with SQL, understanding the database structure is essential. One important structural detail is the default value assigned to a column. In this tutorial, we’ll see how these default values can affect insert behavior and data integrity, especially when columns are left unspecified in a query.

We’ll use the Faculty table from the Baeldung University database for the sample queries, which we’ve tested on PostgreSQL 16, MySQL 8, and SQL Server 2022. The Faculty table includes a column named active with a defined default value, and we’ll write queries to extract and view this value from the database.

2. PostgreSQL

We can retrieve the default value of the active column using the information_schema.columns view:

SELECT column_default, datatype
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'faculty'
AND column_name = 'active';

When we run this query, we get:

+----------------+-----------+
| column_default |  datatype |
+----------------+-----------+
| true           | boolean   |
+----------------+-----------+

The result shows that the column active has a default value of true and uses the boolean data type.

3. MySQL

MySQL exposes similar metadata through the INFORMATION_SCHEMA, allowing us to retrieve default values comparably:

SELECT COLUMN_DEFAULT, COLUMN_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Faculty' 
AND COLUMN_NAME = 'active';

When we run this query in MySQL, we see the result:

+----------------+------------+
| column_default |  datatype  |
+----------------+------------+
|              1 | tinyint(1) |
+----------------+------------+

MySQL doesn’t have a native boolean data type; it’s simply an alias. Internally, boolean is represented as tinyint, which is why the results appear in that form.

Additionally, MySQL offers another way to inspect column details using the SHOW command:

SHOW COLUMNS FROM Faculty WHERE Field='active';

When we execute this, it returns:

+--------+------------+------+-----+---------+-------+
| Field  |    Type    | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| active | tinyint(1) | YES  |     |       1 |       |
+--------+------------+------+-----+---------+-------+

In this output, the default value for the active column is displayed as 1, consistent with how MySQL represents boolean values internally.

4. SQL Server

SQL Server also exposes this information using the INFORMATION_SCHEMA:

SELECT COLUMN_DEFAULT, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Faculty'
AND COLUMN_NAME = 'active';

When we run this query, we get:

+----------------+----------+
| COLUMN_DEFAULT | DATA_TYPE|
+----------------+----------+
| ((1))          | bit      |
+----------------+----------+

The default value appears as ((1)), which is SQL Server’s representation of a default literal. The data type is bit, which is SQL Server’s equivalent to a Boolean value.

5. Conclusion

In this tutorial, we saw that while all three databases expose default column values through the information schema, the representation of defaults differs across systems. PostgreSQL uses a native boolean type and shows true directly. MySQL maps boolean to tinyint(1) and shows numeric values. SQL Server uses bit, formatting the default as ((1)).

These subtle differences are useful to understand when building cross-platform applications or querying metadata in migration or schema-inspection tools.