Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfbc4b71

Browse files
committed
The attached patch adds regression tests for setting and removing
default values using ALTER TABLE, on both views and tables.(You'll need to apply the default-values-for-views patch that I sentto -patches earlier for the regression tests to pass.)Neil Conway <neilconway@rogers.com>
1 parent7f45980 commitfbc4b71

File tree

2 files changed

+95
-0
lines changed

2 files changed

+95
-0
lines changed

‎src/test/regress/expected/alter_table.out

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -681,3 +681,59 @@ insert into child (a, b) values (NULL, 'foo');
681681
ERROR: ExecAppend: Fail to add null value in not null attribute a
682682
drop table child;
683683
drop table parent;
684+
-- test setting and removing default values
685+
create table def_test (
686+
c1int4 default 5,
687+
c2text default 'initial_default'
688+
);
689+
insert into def_test default values;
690+
alter table def_test alter column c1 drop default;
691+
insert into def_test default values;
692+
alter table def_test alter column c2 drop default;
693+
insert into def_test default values;
694+
alter table def_test alter column c1 set default 10;
695+
alter table def_test alter column c2 set default 'new_default';
696+
insert into def_test default values;
697+
select * from def_test;
698+
c1 | c2
699+
----+-----------------
700+
5 | initial_default
701+
| initial_default
702+
|
703+
10 | new_default
704+
(4 rows)
705+
706+
-- set defaults to an incorrect type: this should fail
707+
alter table def_test alter column c1 set default 'wrong_datatype';
708+
ERROR: pg_atoi: error in "wrong_datatype": can't parse "wrong_datatype"
709+
alter table def_test alter column c2 set default 20;
710+
-- set defaults on a non-existent column: this should fail
711+
alter table def_test alter column c3 set default 30;
712+
ERROR: ALTER TABLE: relation "def_test" has no column "c3"
713+
-- set defaults on views: we need to create a view, add a rule
714+
-- to allow insertions into it, and then alter the view to add
715+
-- a default
716+
create view def_view_test as select * from def_test;
717+
create rule def_view_test_ins as
718+
on insert to def_view_test
719+
do instead insert into def_test select new.*;
720+
insert into def_view_test default values;
721+
alter table def_view_test alter column c1 set default 45;
722+
insert into def_view_test default values;
723+
alter table def_view_test alter column c2 set default 'view_default';
724+
insert into def_view_test default values;
725+
select * from def_view_test;
726+
c1 | c2
727+
----+-----------------
728+
5 | initial_default
729+
| initial_default
730+
|
731+
10 | new_default
732+
|
733+
45 |
734+
45 | view_default
735+
(7 rows)
736+
737+
drop rule def_view_test_ins;
738+
drop view def_view_test;
739+
drop table def_test;

‎src/test/regress/sql/alter_table.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -532,3 +532,42 @@ insert into child (a, b) values (NULL, 'foo');
532532
droptable child;
533533
droptable parent;
534534

535+
-- test setting and removing default values
536+
createtabledef_test (
537+
c1int4 default5,
538+
c2text default'initial_default'
539+
);
540+
insert into def_test defaultvalues;
541+
altertable def_test alter column c1 drop default;
542+
insert into def_test defaultvalues;
543+
altertable def_test alter column c2 drop default;
544+
insert into def_test defaultvalues;
545+
altertable def_test alter column c1set default10;
546+
altertable def_test alter column c2set default'new_default';
547+
insert into def_test defaultvalues;
548+
select*from def_test;
549+
550+
-- set defaults to an incorrect type: this should fail
551+
altertable def_test alter column c1set default'wrong_datatype';
552+
altertable def_test alter column c2set default20;
553+
554+
-- set defaults on a non-existent column: this should fail
555+
altertable def_test alter column c3set default30;
556+
557+
-- set defaults on views: we need to create a view, add a rule
558+
-- to allow insertions into it, and then alter the view to add
559+
-- a default
560+
createviewdef_view_testasselect*from def_test;
561+
createruledef_view_test_insas
562+
on insert to def_view_test
563+
do insteadinsert into def_testselect new.*;
564+
insert into def_view_test defaultvalues;
565+
altertable def_view_test alter column c1set default45;
566+
insert into def_view_test defaultvalues;
567+
altertable def_view_test alter column c2set default'view_default';
568+
insert into def_view_test defaultvalues;
569+
select*from def_view_test;
570+
571+
droprule def_view_test_ins;
572+
dropview def_view_test;
573+
droptable def_test;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp