1

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.

banan3'14
  • 3,810
  • 3
  • 24
  • 47

3 Answers3

4

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';
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
1

Try this: value should be in single quote like '00000'

ALTER DOMAIN public."POSTAL_CODE" set default '00000';
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

Try this

Alter table <table name> ALTER <column name> TYPE character varying, ALTER <column name> SET DEFAULT '00000'
Avi
  • 1,424
  • 1
  • 11
  • 32
  • I don't have any table in my database, so what table should I use? A domain doesn't have anything to do with a table, AFAIK. – banan3'14 Aug 14 '18 at 09:28
  • I have just trying give you syntax for further use also .. you can replace if it is not table then just remove name table Alter DOMAIN.... and so on your query... – Avi Aug 14 '18 at 09:32
  • Do you see `ALTER TABLE` in the documentation? Check https://www.postgresql.org/docs/9.5/static/sql-alterdomain.html – banan3'14 Aug 14 '18 at 09:36