Get the Default Values of Table Columns in Postgres

Postgres - How to retrieve default value in table structure query

As documented in the manual the default value is available in column_default

SELECT column_name, is_nullable, character_maximum_length, udt_name, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'USER'
ORDER BY ordinal_position

It is possible to use an expression on default values in postgres?

This is not proper SQL, but even with a CASE expression you couldn't have a column default like this, since DEFAULT expressions cannot contain column references like login.

You will have to create a BEFORE INSERT trigger that sets

NEW.validity := NEW.login < current_timestamp + INTERVAL '1 day';

How to retrieve the actual default value for a column before insertion

There's no way to do what you want directly - you can't preview the value.

Imagine:

regress=> CREATE TABLE crazy (blah integer, rand float4 default random());
CREATE TABLE
regress=> insert into crazy(blah, rand) values (1, DEFAULT);
INSERT 0 1
regress=> select * from crazy;
 blah |   rand   
------+----------
    1 | 0.932575
(1 row)

random() is a volatile function that returns a different value each time. So any attempt to preview the value would only get you a different value to the one that'll be inserted.

The same is true of nextval as concurrent transactions can affect the value - even if you directly read the current sequence position, which PostgreSQL tries to prevent you from doing (because it'll produce wrong results). It's just more obvious to think about this problem with random than nextval.

So, with a volatile default, all you can do is:

• Evaluate the default expression yourself, then supply the value in the insert, i.e. call SELECT nextval('statistical_outputs_id_seq') then INSERT INTO ... VALUES (..., 'the value from nextval()');

• Use RETURNING to obtain the generated value

I suggest the latter. The former is annoying and difficult in the general case, since a default can be any arbitrary expression.

Example for RETURNING:

regress=> insert into crazy(blah, rand) values (1, DEFAULT) RETURNING rand;
   rand   
----------
 0.975092
(1 row)

INSERT 0 1

How does postgres 11+ maintain default value after the default is dropped?

Column defaults are stored in the system catalog pg_attrdef, while column definitions are stored in pg_attribute. If you drop the default value, the pg_attrdef row is deleted, but the pg_attribute row isn't.

The secret behind what you observe is that the “missing attribute value” is stored in pg_attribute, so it is not affected:

SELECT attmissingval
FROM pg_attribute
WHERE attrelid = 'foo'::regclass
  AND attname = 'bar';

 attmissingval 
═══════════════
 {10}
(1 row)

Add a column with a default value to an existing table in postgresql

Referencing the most recent docs, this operation can be done using two statements.

1. Adds the column with the old default value

ALTER TABLE my_table ADD COLUMN description varchar(100) DEFAULT 'A1';

2. Modifies the column to use a different default value

ALTER TABLE my_table ALTER COLUMN description SET DEFAULT 'B2'

A full reproducible sample has been included below:

CREATE TABLE my_table (
  "name" VARCHAR(5),
  "work" VARCHAR(9)
);

INSERT INTO my_table
  ("name", "work")
VALUES
  ('bob', 'fireman'),
  ('carl', 'teacher'),
  ('alice', 'policeman');

---

Query #1

select * from my_table;

---