SQL domain ERROR: column does not exist, setting default

Question

I created a DOMAIN:

CREATE DOMAIN public."POSTAL_CODE"
  AS character(5)
  NOT NULL;

I tried to set the default value:

ALTER DOMAIN public."POSTAL_CODE"
SET DEFAULT "00000";

but got the error:

ERROR: column "00000" does not exist

Then I managed to set the default value using DEFAULT 00000, but I think it was cast to INTEGER as it shows as 0 instead of 00000. I tried character(5)[] and {'0','0','0','0','0'} without success as well.

How to get a default value as text and not get an error?

I used PostgreSQL.

Answer 1

In SQL double quotes " are used to refer to a column or table named "select"

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. So this is not the same as a double-quote character (")

As a result you have to use single quote like below

select * from test where old_code = '220088242'

so in your case it should be like below

ALTER DOMAIN public."POSTAL_CODE"
    SET DEFAULT '00000';