Create a Boolean Column with default value

Question

BACKGROUND

I am attempting to create a table that will have a single column and single row with boolean value. It will be either set True or False by some external application.

CODE

BEGIN;

CREATE TABLE my_table (
  my_flag BOOL DEFAULT TRUE
);


END;


SELECT * FROM my_table

ISSUE

When i do SELECT * FROM my_table i expect it to return one row with the value TRUE except it returns the table and row but with no value. I am not sure why that is?

You don't insert anything into the table, so obviously the select returns no rows either — user330315
– user330315, Commented Oct 29, 2020 at 21:29

Adrian Klaver · Accepted Answer · 2020-10-29 21:30:57Z

2

Pretty sure it is not returning a row:

CREATE TABLE my_table (
  my_flag BOOL DEFAULT TRUE
);

select * from my_table ;
 my_flag 
---------
(0 rows)

The DEFAULT only applies when you INSERT a row:

insert into my_table values (default);
INSERT 0 1
test(5432)=> select * from my_table ;
 my_flag 
---------
 t
(1 row)

answered Oct 29, 2020 at 21:30

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2020-10-29 21:34:11Z

1

The table is empty. At least one row has to be added. Below example of creating table using CTaS:

CREATE TABLE my_table (my_flag) AS SELECT TRUE;

db<>fiddle demo

answered Oct 29, 2020 at 21:34

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

2 Comments

Dinero Over a year ago

This is actaully very neat!

Dinero Over a year ago

Side note: can you any place where i can practice my sql skills ?

Collectives™ on Stack Overflow

Create a Boolean Column with default value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related