~vcs-imports/mammoth-replicator/trunk

---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------

| 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}

(1 row)

DROP TABLE tmp;

-- the wolf bug - schema mods caused inconsistent row descriptors

CREATE TABLE tmp (

initial int4

);

ALTER TABLE tmp ADD COLUMN a int4;

ALTER TABLE tmp ADD COLUMN b name;

ALTER TABLE tmp ADD COLUMN c text;

ALTER TABLE tmp ADD COLUMN d float8;

ALTER TABLE tmp ADD COLUMN e float4;

ALTER TABLE tmp ADD COLUMN f int2;

ALTER TABLE tmp ADD COLUMN g polygon;

ALTER TABLE tmp ADD COLUMN h abstime;

ALTER TABLE tmp ADD COLUMN i char;

ALTER TABLE tmp ADD COLUMN j abstime[];

ALTER TABLE tmp ADD COLUMN k int4;

ALTER TABLE tmp ADD COLUMN l tid;

ALTER TABLE tmp ADD COLUMN m xid;

ALTER TABLE tmp ADD COLUMN n oidvector;

--ALTER TABLE tmp ADD COLUMN o lock;

ALTER TABLE tmp ADD COLUMN p smgr;

ALTER TABLE tmp ADD COLUMN q point;

ALTER TABLE tmp ADD COLUMN r lseg;

ALTER TABLE tmp ADD COLUMN s path;

ALTER TABLE tmp ADD COLUMN t box;

ALTER TABLE tmp ADD COLUMN u tinterval;

ALTER TABLE tmp ADD COLUMN v timestamp;

ALTER TABLE tmp ADD COLUMN w interval;

ALTER TABLE tmp ADD COLUMN x float8[];

ALTER TABLE tmp ADD COLUMN y float4[];

ALTER TABLE tmp ADD COLUMN z int2[];

INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,

v, w, x, y, z)

VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',

'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',

314159, '(1,1)', '512',

'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',

'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',

'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');

SELECT * FROM tmp;

initial | a | b | c | d | e | f | g | h | i | j | k | l | m | n | p | q | r | s | t | u | v | w | x | y | z

(1 row)

DROP TABLE tmp;

-- rename - check on both non-temp and temp tables

CREATE TABLE tmp (regtable int);

100

CREATE TEMP TABLE tmp (tmptable int);

101

ALTER TABLE tmp RENAME TO tmp_new;

102

SELECT * FROM tmp;

103

regtable

104

----------

105

(0 rows)

106

107

SELECT * FROM tmp_new;

108

tmptable

109

----------

110

(0 rows)

111

112

ALTER TABLE tmp RENAME TO tmp_new2;

113

SELECT * FROM tmp; -- should fail

114

ERROR: relation "tmp" does not exist

115

SELECT * FROM tmp_new;

116

tmptable

117

----------

118

(0 rows)

119

120

SELECT * FROM tmp_new2;

121

regtable

122

----------

123

(0 rows)

124

125

DROP TABLE tmp_new;

126

DROP TABLE tmp_new2;

127

-- ALTER TABLE ... RENAME on non-table relations

128

-- renaming indexes (FIXME: this should probably test the index's functionality)

129

ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;

130

ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;

131

-- renaming views

132

CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;

133

ALTER TABLE tmp_view RENAME TO tmp_view_new;

134

-- analyze to ensure we get an indexscan here

135

ANALYZE tenk1;

136

-- 5 values, sorted

137

SELECT unique1 FROM tenk1 WHERE unique1 < 5;

138

unique1

139

---------

140

141

142

143

144

145

(5 rows)

146

147

DROP VIEW tmp_view_new;

148

-- toast-like relation name

149

alter table stud_emp rename to pg_toast_stud_emp;

150

alter table pg_toast_stud_emp rename to stud_emp;

151

-- FOREIGN KEY CONSTRAINT adding TEST

152

CREATE TABLE tmp2 (a int primary key);

153

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2"

154

CREATE TABLE tmp3 (a int, b int);

155

CREATE TABLE tmp4 (a int, b int, unique(a,b));

156

NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for table "tmp4"

157

CREATE TABLE tmp5 (a int, b int);

158

-- Insert rows into tmp2 (pktable)

159

INSERT INTO tmp2 values (1);

160

INSERT INTO tmp2 values (2);

161

INSERT INTO tmp2 values (3);

162

INSERT INTO tmp2 values (4);

163

-- Insert rows into tmp3

164

INSERT INTO tmp3 values (1,10);

165

INSERT INTO tmp3 values (1,20);

166

INSERT INTO tmp3 values (5,50);

167

-- Try (and fail) to add constraint due to invalid source columns

168

ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;

169

ERROR: column "c" referenced in foreign key constraint does not exist

170

-- Try (and fail) to add constraint due to invalide destination columns explicitly given

171

ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;

172

ERROR: column "b" referenced in foreign key constraint does not exist

173

-- Try (and fail) to add constraint due to invalid data

174

ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;

175

ERROR: insert or update on table "tmp3" violates foreign key constraint "tmpconstr"

176

DETAIL: Key (a)=(5) is not present in table "tmp2".

177

-- Delete failing row

178

DELETE FROM tmp3 where a=5;

179

-- Try (and succeed)

180

ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;

181

-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on

182

-- tmp4 is a,b

183

ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;

184

ERROR: there is no unique constraint matching given keys for referenced table "tmp4"

185

DROP TABLE tmp5;

186

DROP TABLE tmp4;

187

DROP TABLE tmp3;

188

DROP TABLE tmp2;

189

-- Foreign key adding test with mixed types

190

-- Note: these tables are TEMP to avoid name conflicts when this test

191

-- is run in parallel with foreign_key.sql.

192

CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);

193

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"

194

CREATE TEMP TABLE FKTABLE (ftest1 inet);

195

-- This next should fail, because inet=int does not exist

196

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;

197

ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented

198

DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.

199

-- This should also fail for the same reason, but here we

200

-- give the column name

201

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);

202

ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented

203

DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.

204

-- This should succeed, even though they are different types

205

-- because varchar=int does exist

206

DROP TABLE FKTABLE;

207

CREATE TEMP TABLE FKTABLE (ftest1 varchar);

208

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;

209

WARNING: foreign key constraint "fktable_ftest1_fkey" will require costly sequential scans

210

DETAIL: Key columns "ftest1" and "ptest1" are of different types: character varying and integer.

211

-- As should this

212

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);

213

WARNING: foreign key constraint "fktable_ftest1_fkey1" will require costly sequential scans

214

DETAIL: Key columns "ftest1" and "ptest1" are of different types: character varying and integer.

215

DROP TABLE pktable cascade;

216

NOTICE: drop cascades to constraint fktable_ftest1_fkey1 on table fktable

217

NOTICE: drop cascades to constraint fktable_ftest1_fkey on table fktable

218

DROP TABLE fktable;

219

CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,

220

PRIMARY KEY(ptest1, ptest2));

221

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"

222

-- This should fail, because we just chose really odd types

223

CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);

224

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;

225

ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented

226

DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.

227

DROP TABLE FKTABLE;

228

-- Again, so should this...

229

CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);

230

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)

231

references pktable(ptest1, ptest2);

232

ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented

233

DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.

234

DROP TABLE FKTABLE;

235

-- This fails because we mixed up the column ordering

236

CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);

237

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)

238

references pktable(ptest2, ptest1);

239

ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented

240

DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.

241

-- As does this...

242

ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)

243

references pktable(ptest1, ptest2);

244

ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented

245

DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.

246

-- temp tables should go away by themselves, need not drop them.

247

-- test check constraint adding

248

create table atacc1 ( test int );

249

-- add a check constraint

250

alter table atacc1 add constraint atacc_test1 check (test>3);

251

-- should fail

252

insert into atacc1 (test) values (2);

253

ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"

254

-- should succeed

255

insert into atacc1 (test) values (4);

256

drop table atacc1;

257

-- let's do one where the check fails when added

258

create table atacc1 ( test int );

259

-- insert a soon to be failing row

260

insert into atacc1 (test) values (2);

261

-- add a check constraint (fails)

262

alter table atacc1 add constraint atacc_test1 check (test>3);

263

ERROR: check constraint "atacc_test1" is violated by some row

264

insert into atacc1 (test) values (4);

265

drop table atacc1;

266

-- let's do one where the check fails because the column doesn't exist

267

create table atacc1 ( test int );

268

-- add a check constraint (fails)

269

alter table atacc1 add constraint atacc_test1 check (test1>3);

270

ERROR: column "test1" does not exist

271

drop table atacc1;

272

-- something a little more complicated

273

create table atacc1 ( test int, test2 int, test3 int);

274

-- add a check constraint (fails)

275

alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);

276

-- should fail

277

insert into atacc1 (test,test2,test3) values (4,4,2);

278

ERROR: new row for relation "atacc1" violates check constraint "atacc_test1"

279

-- should succeed

280

insert into atacc1 (test,test2,test3) values (4,4,5);

281

drop table atacc1;

282

-- lets do some naming tests

283

create table atacc1 (test int check (test>3), test2 int);

284

alter table atacc1 add check (test2>test);

285

-- should fail for $2

286

insert into atacc1 (test2, test) values (3, 4);

287

ERROR: new row for relation "atacc1" violates check constraint "atacc1_check"

288

drop table atacc1;

289

-- inheritance related tests

290

create table atacc1 (test int);

291

create table atacc2 (test2 int);

292

create table atacc3 (test3 int) inherits (atacc1, atacc2);

293

alter table atacc2 add constraint foo check (test2>0);

294

-- fail and then succeed on atacc2

295

insert into atacc2 (test2) values (-3);

296

ERROR: new row for relation "atacc2" violates check constraint "foo"

297

insert into atacc2 (test2) values (3);

298

-- fail and then succeed on atacc3

299

insert into atacc3 (test2) values (-3);

300

ERROR: new row for relation "atacc3" violates check constraint "foo"

301

insert into atacc3 (test2) values (3);

302

drop table atacc3;

303

drop table atacc2;

304

drop table atacc1;

305

-- let's try only to add only to the parent

306

create table atacc1 (test int);

307

create table atacc2 (test2 int);

308

create table atacc3 (test3 int) inherits (atacc1, atacc2);

309

alter table only atacc2 add constraint foo check (test2>0);

310

-- fail and then succeed on atacc2

311

insert into atacc2 (test2) values (-3);

312

ERROR: new row for relation "atacc2" violates check constraint "foo"

313

insert into atacc2 (test2) values (3);

314

-- both succeed on atacc3

315

insert into atacc3 (test2) values (-3);

316

insert into atacc3 (test2) values (3);

317

drop table atacc3;

318

drop table atacc2;

319

drop table atacc1;

320

-- test unique constraint adding

321

create table atacc1 ( test int ) with oids;

322

-- add a unique constraint

323

alter table atacc1 add constraint atacc_test1 unique (test);

324

NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"

325

-- insert first value

326

insert into atacc1 (test) values (2);

327

-- should fail

328

insert into atacc1 (test) values (2);

329

ERROR: duplicate key violates unique constraint "atacc_test1"

330

-- should succeed

331

insert into atacc1 (test) values (4);

332

-- try adding a unique oid constraint

333

alter table atacc1 add constraint atacc_oid1 unique(oid);

334

NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1"

335

drop table atacc1;

336

-- let's do one where the unique constraint fails when added

337

create table atacc1 ( test int );

338

-- insert soon to be failing rows

339

insert into atacc1 (test) values (2);

340

insert into atacc1 (test) values (2);

341

-- add a unique constraint (fails)

342

alter table atacc1 add constraint atacc_test1 unique (test);

343

NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"

344

ERROR: could not create unique index

345

DETAIL: Table contains duplicated values.

346

insert into atacc1 (test) values (3);

347

drop table atacc1;

348

-- let's do one where the unique constraint fails

349

-- because the column doesn't exist

350

create table atacc1 ( test int );

351

-- add a unique constraint (fails)

352

alter table atacc1 add constraint atacc_test1 unique (test1);

353

ERROR: column "test1" named in key does not exist

354

drop table atacc1;

355

-- something a little more complicated

356

create table atacc1 ( test int, test2 int);

357

-- add a unique constraint

358

alter table atacc1 add constraint atacc_test1 unique (test, test2);

359

NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1"

360

-- insert initial value

361

insert into atacc1 (test,test2) values (4,4);

362

-- should fail

363

insert into atacc1 (test,test2) values (4,4);

364

ERROR: duplicate key violates unique constraint "atacc_test1"

365

-- should all succeed

366

insert into atacc1 (test,test2) values (4,5);

367

insert into atacc1 (test,test2) values (5,4);

368

insert into atacc1 (test,test2) values (5,5);

369

drop table atacc1;

370

-- lets do some naming tests

371

create table atacc1 (test int, test2 int, unique(test));

372

NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1"

373

alter table atacc1 add unique (test2);

374

NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1"

375

-- should fail for @@ second one @@

376

insert into atacc1 (test2, test) values (3, 3);

377

insert into atacc1 (test2, test) values (2, 3);

378

ERROR: duplicate key violates unique constraint "atacc1_test_key"

379

drop table atacc1;

380

-- test primary key constraint adding

381

create table atacc1 ( test int ) with oids;

382

-- add a primary key constraint

383

alter table atacc1 add constraint atacc_test1 primary key (test);

384

NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"

385

-- insert first value

386

insert into atacc1 (test) values (2);

387

-- should fail

388

insert into atacc1 (test) values (2);

389

ERROR: duplicate key violates unique constraint "atacc_test1"

390

-- should succeed

391

insert into atacc1 (test) values (4);

392

-- inserting NULL should fail

393

insert into atacc1 (test) values(NULL);

394

ERROR: null value in column "test" violates not-null constraint

395

-- try adding a second primary key (should fail)

396

alter table atacc1 add constraint atacc_oid1 primary key(oid);

397

ERROR: multiple primary keys for table "atacc1" are not allowed

398

-- drop first primary key constraint

399

alter table atacc1 drop constraint atacc_test1 restrict;

400

-- try adding a primary key on oid (should succeed)

401

alter table atacc1 add constraint atacc_oid1 primary key(oid);

402

NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_oid1" for table "atacc1"

403

drop table atacc1;

404

-- let's do one where the primary key constraint fails when added

405

create table atacc1 ( test int );

406

-- insert soon to be failing rows

407

insert into atacc1 (test) values (2);

408

insert into atacc1 (test) values (2);

409

-- add a primary key (fails)

410

alter table atacc1 add constraint atacc_test1 primary key (test);

411

NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"

412

ERROR: could not create unique index

413

DETAIL: Table contains duplicated values.

414

insert into atacc1 (test) values (3);

415

drop table atacc1;

416

-- let's do another one where the primary key constraint fails when added

417

create table atacc1 ( test int );

418

-- insert soon to be failing row

419

insert into atacc1 (test) values (NULL);

420

-- add a primary key (fails)

421

alter table atacc1 add constraint atacc_test1 primary key (test);

422

ERROR: column "test" contains null values

423

insert into atacc1 (test) values (3);

424

drop table atacc1;

425

-- let's do one where the primary key constraint fails

426

-- because the column doesn't exist

427

create table atacc1 ( test int );

428

-- add a primary key constraint (fails)

429

alter table atacc1 add constraint atacc_test1 primary key (test1);

430

ERROR: column "test1" named in key does not exist

431

drop table atacc1;

432

-- something a little more complicated

433

create table atacc1 ( test int, test2 int);

434

-- add a primary key constraint

435

alter table atacc1 add constraint atacc_test1 primary key (test, test2);

436

NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1"

437

-- try adding a second primary key - should fail

438

alter table atacc1 add constraint atacc_test2 primary key (test);

439

ERROR: multiple primary keys for table "atacc1" are not allowed

440

-- insert initial value

441

insert into atacc1 (test,test2) values (4,4);

442

-- should fail

443

insert into atacc1 (test,test2) values (4,4);

444

ERROR: duplicate key violates unique constraint "atacc_test1"

445

insert into atacc1 (test,test2) values (NULL,3);

446

ERROR: null value in column "test" violates not-null constraint

447

insert into atacc1 (test,test2) values (3, NULL);

448

ERROR: null value in column "test2" violates not-null constraint

449

insert into atacc1 (test,test2) values (NULL,NULL);

450

ERROR: null value in column "test" violates not-null constraint

451

-- should all succeed

452

insert into atacc1 (test,test2) values (4,5);

453

insert into atacc1 (test,test2) values (5,4);

454

insert into atacc1 (test,test2) values (5,5);

455

drop table atacc1;

456

-- lets do some naming tests

457

create table atacc1 (test int, test2 int, primary key(test));

458

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"

459

-- only first should succeed

460

insert into atacc1 (test2, test) values (3, 3);

461

insert into atacc1 (test2, test) values (2, 3);

462

ERROR: duplicate key violates unique constraint "atacc1_pkey"

463

insert into atacc1 (test2, test) values (1, NULL);

464

ERROR: null value in column "test" violates not-null constraint

465

drop table atacc1;

466

-- alter table / alter column [set/drop] not null tests

467

-- try altering system catalogs, should fail

468

alter table pg_class alter column relname drop not null;

469

ERROR: permission denied: "pg_class" is a system catalog

470

alter table pg_class alter relname set not null;

471

ERROR: permission denied: "pg_class" is a system catalog

472

-- try altering non-existent table, should fail

473

alter table non_existent alter column bar set not null;

474

ERROR: relation "non_existent" does not exist

475

alter table non_existent alter column bar drop not null;

476

ERROR: relation "non_existent" does not exist

477

-- test setting columns to null and not null and vice versa

478

-- test checking for null values and primary key

479

create table atacc1 (test int not null) with oids;

480

alter table atacc1 add constraint "atacc1_pkey" primary key (test);

481

NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"

482

alter table atacc1 alter column test drop not null;

483

ERROR: column "test" is in a primary key

484

alter table atacc1 drop constraint "atacc1_pkey";

485

alter table atacc1 alter column test drop not null;

486

insert into atacc1 values (null);

487

alter table atacc1 alter test set not null;

488

ERROR: column "test" contains null values

489

delete from atacc1;

490

alter table atacc1 alter test set not null;

491

-- try altering a non-existent column, should fail

492

alter table atacc1 alter bar set not null;

493

ERROR: column "bar" of relation "atacc1" does not exist

494

alter table atacc1 alter bar drop not null;

495

ERROR: column "bar" of relation "atacc1" does not exist

496

-- try altering the oid column, should fail

497

alter table atacc1 alter oid set not null;

498

ERROR: cannot alter system column "oid"

499

alter table atacc1 alter oid drop not null;

500

ERROR: cannot alter system column "oid"

501

-- try creating a view and altering that, should fail

502

create view myview as select * from atacc1;

503

alter table myview alter column test drop not null;

504

ERROR: "myview" is not a table

505

alter table myview alter column test set not null;

506

ERROR: "myview" is not a table

507

drop view myview;

508

drop table atacc1;

509

-- test inheritance

510

create table parent (a int);

511

create table child (b varchar(255)) inherits (parent);

512

alter table parent alter a set not null;

513

insert into parent values (NULL);

514

ERROR: null value in column "a" violates not-null constraint

515

insert into child (a, b) values (NULL, 'foo');

516

ERROR: null value in column "a" violates not-null constraint

517

alter table parent alter a drop not null;

518

insert into parent values (NULL);

519

insert into child (a, b) values (NULL, 'foo');

520

alter table only parent alter a set not null;

521

ERROR: column "a" contains null values

522

alter table child alter a set not null;

523

ERROR: column "a" contains null values

524

delete from parent;

525

alter table only parent alter a set not null;

526

insert into parent values (NULL);

527

ERROR: null value in column "a" violates not-null constraint

528

alter table child alter a set not null;

529

insert into child (a, b) values (NULL, 'foo');

530

ERROR: null value in column "a" violates not-null constraint

531

delete from child;

532

alter table child alter a set not null;

533

insert into child (a, b) values (NULL, 'foo');

534

ERROR: null value in column "a" violates not-null constraint

535

drop table child;

536

drop table parent;

537

-- test setting and removing default values

538

create table def_test (

539

c1 int4 default 5,

540

c2 text default 'initial_default'

541

);

542

insert into def_test default values;

543

alter table def_test alter column c1 drop default;

544

insert into def_test default values;

545

alter table def_test alter column c2 drop default;

546

insert into def_test default values;

547

alter table def_test alter column c1 set default 10;

548

alter table def_test alter column c2 set default 'new_default';

549

insert into def_test default values;

550

select * from def_test;

551

c1 | c2

552

----+-----------------

553

5 | initial_default

554

| initial_default

555

556

10 | new_default

557

(4 rows)

558

559

-- set defaults to an incorrect type: this should fail

560

alter table def_test alter column c1 set default 'wrong_datatype';

561

ERROR: invalid input syntax for integer: "wrong_datatype"

562

alter table def_test alter column c2 set default 20;

563

-- set defaults on a non-existent column: this should fail

564

alter table def_test alter column c3 set default 30;

565

ERROR: column "c3" of relation "def_test" does not exist

566

-- set defaults on views: we need to create a view, add a rule

567

-- to allow insertions into it, and then alter the view to add

568

-- a default

569

create view def_view_test as select * from def_test;

570

create rule def_view_test_ins as

571

on insert to def_view_test

572

do instead insert into def_test select new.*;

573

insert into def_view_test default values;

574

alter table def_view_test alter column c1 set default 45;

575

insert into def_view_test default values;

576

alter table def_view_test alter column c2 set default 'view_default';

577

insert into def_view_test default values;

578

select * from def_view_test;

579

c1 | c2

580

----+-----------------

581

5 | initial_default

582

| initial_default

583

584

10 | new_default

585

586

45 |

587

45 | view_default

588

(7 rows)

589

590

drop rule def_view_test_ins on def_view_test;

591

drop view def_view_test;

592

drop table def_test;

593

-- alter table / drop column tests

594

-- try altering system catalogs, should fail

595

alter table pg_class drop column relname;

596

ERROR: permission denied: "pg_class" is a system catalog

597

-- try altering non-existent table, should fail

598

alter table nosuchtable drop column bar;

599

ERROR: relation "nosuchtable" does not exist

600

-- test dropping columns

601

create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;

602

insert into atacc1 values (1, 2, 3, 4);

603

alter table atacc1 drop a;

604

alter table atacc1 drop a;

605

ERROR: column "a" of relation "atacc1" does not exist

606

-- SELECTs

607

select * from atacc1;

608

b | c | d

609

---+---+---

610

2 | 3 | 4

611

(1 row)

612

613

select * from atacc1 order by a;

614

ERROR: column "a" does not exist

615

select * from atacc1 order by "........pg.dropped.1........";

616

ERROR: column "........pg.dropped.1........" does not exist

617

select * from atacc1 group by a;

618

ERROR: column "a" does not exist

619

select * from atacc1 group by "........pg.dropped.1........";

620

ERROR: column "........pg.dropped.1........" does not exist

621

select atacc1.* from atacc1;

622

b | c | d

623

---+---+---

624

2 | 3 | 4

625

(1 row)

626

627

select a from atacc1;

628

ERROR: column "a" does not exist

629

select atacc1.a from atacc1;

630

ERROR: column atacc1.a does not exist

631

select b,c,d from atacc1;

632

b | c | d

633

---+---+---

634

2 | 3 | 4

635

(1 row)

636

637

select a,b,c,d from atacc1;

638

ERROR: column "a" does not exist

639

select * from atacc1 where a = 1;

640

ERROR: column "a" does not exist

641

select "........pg.dropped.1........" from atacc1;

642

ERROR: column "........pg.dropped.1........" does not exist

643

select atacc1."........pg.dropped.1........" from atacc1;

644

ERROR: column atacc1.........pg.dropped.1........ does not exist

645

select "........pg.dropped.1........",b,c,d from atacc1;

646

ERROR: column "........pg.dropped.1........" does not exist

647

select * from atacc1 where "........pg.dropped.1........" = 1;

648

ERROR: column "........pg.dropped.1........" does not exist

649

-- UPDATEs

650

update atacc1 set a = 3;

651

ERROR: column "a" of relation "atacc1" does not exist

652

update atacc1 set b = 2 where a = 3;

653

ERROR: column "a" does not exist

654

update atacc1 set "........pg.dropped.1........" = 3;

655

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

656

update atacc1 set b = 2 where "........pg.dropped.1........" = 3;

657

ERROR: column "........pg.dropped.1........" does not exist

658

-- INSERTs

659

insert into atacc1 values (10, 11, 12, 13);

660

ERROR: INSERT has more expressions than target columns

661

insert into atacc1 values (default, 11, 12, 13);

662

ERROR: INSERT has more expressions than target columns

663

insert into atacc1 values (11, 12, 13);

664

insert into atacc1 (a) values (10);

665

ERROR: column "a" of relation "atacc1" does not exist

666

insert into atacc1 (a) values (default);

667

ERROR: column "a" of relation "atacc1" does not exist

668

insert into atacc1 (a,b,c,d) values (10,11,12,13);

669

ERROR: column "a" of relation "atacc1" does not exist

670

insert into atacc1 (a,b,c,d) values (default,11,12,13);

671

ERROR: column "a" of relation "atacc1" does not exist

672

insert into atacc1 (b,c,d) values (11,12,13);

673

insert into atacc1 ("........pg.dropped.1........") values (10);

674

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

675

insert into atacc1 ("........pg.dropped.1........") values (default);

676

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

677

insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);

678

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

679

insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);

680

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

681

-- DELETEs

682

delete from atacc1 where a = 3;

683

ERROR: column "a" does not exist

684

delete from atacc1 where "........pg.dropped.1........" = 3;

685

ERROR: column "........pg.dropped.1........" does not exist

686

delete from atacc1;

687

-- try dropping a non-existent column, should fail

688

alter table atacc1 drop bar;

689

ERROR: column "bar" of relation "atacc1" does not exist

690

-- try dropping the oid column, should succeed

691

alter table atacc1 drop oid;

692

-- try dropping the xmin column, should fail

693

alter table atacc1 drop xmin;

694

ERROR: cannot drop system column "xmin"

695

-- try creating a view and altering that, should fail

696

create view myview as select * from atacc1;

697

select * from myview;

698

b | c | d

699

---+---+---

700

(0 rows)

701

702

alter table myview drop d;

703

ERROR: "myview" is not a table

704

drop view myview;

705

-- test some commands to make sure they fail on the dropped column

706

analyze atacc1(a);

707

ERROR: column "a" of relation "atacc1" does not exist

708

analyze atacc1("........pg.dropped.1........");

709

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

710

vacuum analyze atacc1(a);

711

ERROR: column "a" of relation "atacc1" does not exist

712

vacuum analyze atacc1("........pg.dropped.1........");

713

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

714

comment on column atacc1.a is 'testing';

715

ERROR: column "a" of relation "atacc1" does not exist

716

comment on column atacc1."........pg.dropped.1........" is 'testing';

717

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

718

alter table atacc1 alter a set storage plain;

719

ERROR: column "a" of relation "atacc1" does not exist

720

alter table atacc1 alter "........pg.dropped.1........" set storage plain;

721

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

722

alter table atacc1 alter a set statistics 0;

723

ERROR: column "a" of relation "atacc1" does not exist

724

alter table atacc1 alter "........pg.dropped.1........" set statistics 0;

725

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

726

alter table atacc1 alter a set default 3;

727

ERROR: column "a" of relation "atacc1" does not exist

728

alter table atacc1 alter "........pg.dropped.1........" set default 3;

729

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

730

alter table atacc1 alter a drop default;

731

ERROR: column "a" of relation "atacc1" does not exist

732

alter table atacc1 alter "........pg.dropped.1........" drop default;

733

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

734

alter table atacc1 alter a set not null;

735

ERROR: column "a" of relation "atacc1" does not exist

736

alter table atacc1 alter "........pg.dropped.1........" set not null;

737

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

738

alter table atacc1 alter a drop not null;

739

ERROR: column "a" of relation "atacc1" does not exist

740

alter table atacc1 alter "........pg.dropped.1........" drop not null;

741

ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist

742

alter table atacc1 rename a to x;

743

ERROR: column "a" does not exist

744

alter table atacc1 rename "........pg.dropped.1........" to x;

745

ERROR: column "........pg.dropped.1........" does not exist

746

alter table atacc1 add primary key(a);

747

ERROR: column "a" named in key does not exist

748

alter table atacc1 add primary key("........pg.dropped.1........");

749

ERROR: column "........pg.dropped.1........" named in key does not exist

750

alter table atacc1 add unique(a);

751

ERROR: column "a" named in key does not exist

752

alter table atacc1 add unique("........pg.dropped.1........");

753

ERROR: column "........pg.dropped.1........" named in key does not exist

754

alter table atacc1 add check (a > 3);

755

ERROR: column "a" does not exist

756

alter table atacc1 add check ("........pg.dropped.1........" > 3);

757

ERROR: column "........pg.dropped.1........" does not exist

758

create table atacc2 (id int4 unique);

759

NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2"

760

alter table atacc1 add foreign key (a) references atacc2(id);

761

ERROR: column "a" referenced in foreign key constraint does not exist

762

alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);

763

ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist

764

alter table atacc2 add foreign key (id) references atacc1(a);

765

ERROR: column "a" referenced in foreign key constraint does not exist

766

alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");

767

ERROR: column "........pg.dropped.1........" referenced in foreign key constraint does not exist

768

drop table atacc2;

769

create index "testing_idx" on atacc1(a);

770

ERROR: column "a" does not exist

771

create index "testing_idx" on atacc1("........pg.dropped.1........");

772

ERROR: column "........pg.dropped.1........" does not exist

773

-- test create as and select into

774

insert into atacc1 values (21, 22, 23);

775

create table test1 as select * from atacc1;

776

select * from test1;

777

b | c | d

778

----+----+----

779

21 | 22 | 23

780

(1 row)

781

782

drop table test1;

783

select * into test2 from atacc1;

784

select * from test2;

785

b | c | d

786

----+----+----

787

21 | 22 | 23

788

(1 row)

789

790

drop table test2;

791

-- try dropping all columns

792

alter table atacc1 drop c;

793

alter table atacc1 drop d;

794

alter table atacc1 drop b;

795

select * from atacc1;

796

797

798

(1 row)

799

800

drop table atacc1;

801

-- test inheritance

802

create table parent (a int, b int, c int);

803

insert into parent values (1, 2, 3);

804

alter table parent drop a;

805

create table child (d varchar(255)) inherits (parent);

806

insert into child values (12, 13, 'testing');

807

select * from parent;

808

b | c

809

----+----

810

2 | 3

811

12 | 13

812

(2 rows)

813

814

select * from child;

815

b | c | d

816

----+----+---------

817

12 | 13 | testing

818

(1 row)

819

820

alter table parent drop c;

821

select * from parent;

822

823

----

824

825

826

(2 rows)

827

828

select * from child;

829

b | d

830

----+---------

831

12 | testing

832

(1 row)

833

834

drop table child;

835

drop table parent;

836

-- test copy in/out

837

create table test (a int4, b int4, c int4);

838

insert into test values (1,2,3);

839

alter table test drop a;

840

copy test to stdout;

841

2 3

842

copy test(a) to stdout;

843

ERROR: column "a" of relation "test" does not exist

844

copy test("........pg.dropped.1........") to stdout;

845

ERROR: column "........pg.dropped.1........" of relation "test" does not exist

846

copy test from stdin;

847

ERROR: extra data after last expected column

848

CONTEXT: COPY test, line 1: "10 11 12"

849

select * from test;

850

b | c

851

---+---

852

2 | 3

853

(1 row)

854

855

copy test from stdin;

856

select * from test;

857

b | c

858

----+----

859

2 | 3

860

21 | 22

861

(2 rows)

862

863

copy test(a) from stdin;

864

ERROR: column "a" of relation "test" does not exist

865

copy test("........pg.dropped.1........") from stdin;

866

ERROR: column "........pg.dropped.1........" of relation "test" does not exist

867

copy test(b,c) from stdin;

868

select * from test;

869

b | c

870

----+----

871

2 | 3

872

21 | 22

873

31 | 32

874

(3 rows)

875

876

drop table test;

877

-- test inheritance

878

create table dropColumn (a int, b int, e int);

879

create table dropColumnChild (c int) inherits (dropColumn);

880

create table dropColumnAnother (d int) inherits (dropColumnChild);

881

-- these two should fail

882

alter table dropColumnchild drop column a;

883

ERROR: cannot drop inherited column "a"

884

alter table only dropColumnChild drop column b;

885

ERROR: cannot drop inherited column "b"

886

-- these three should work

887

alter table only dropColumn drop column e;

888

alter table dropColumnChild drop column c;

889

alter table dropColumn drop column a;

890

create table renameColumn (a int);

891

create table renameColumnChild (b int) inherits (renameColumn);

892

create table renameColumnAnother (c int) inherits (renameColumnChild);

893

-- these three should fail

894

alter table renameColumnChild rename column a to d;

895

ERROR: cannot rename inherited column "a"

896

alter table only renameColumnChild rename column a to d;

897

ERROR: inherited column "a" must be renamed in child tables too

898

alter table only renameColumn rename column a to d;

899

ERROR: inherited column "a" must be renamed in child tables too

900

-- these should work

901

alter table renameColumn rename column a to d;

902

alter table renameColumnChild rename column b to a;

903

-- this should work

904

alter table renameColumn add column w int;

905

-- this should fail

906

alter table only renameColumn add column x int;

907

ERROR: column must be added to child tables too

908

-- Test corner cases in dropping of inherited columns

909

create table p1 (f1 int, f2 int);

910

create table c1 (f1 int not null) inherits(p1);

911

NOTICE: merging column "f1" with inherited definition

912

-- should be rejected since c1.f1 is inherited

913

alter table c1 drop column f1;

914

ERROR: cannot drop inherited column "f1"

915

-- should work

916

alter table p1 drop column f1;

917

-- c1.f1 is still there, but no longer inherited

918

select f1 from c1;

919

920

----

921

(0 rows)

922

923

alter table c1 drop column f1;

924

select f1 from c1;

925

ERROR: column "f1" does not exist

926

drop table p1 cascade;

927

NOTICE: drop cascades to table c1

928

create table p1 (f1 int, f2 int);

929

create table c1 () inherits(p1);

930

-- should be rejected since c1.f1 is inherited

931

alter table c1 drop column f1;

932

ERROR: cannot drop inherited column "f1"

933

alter table p1 drop column f1;

934

-- c1.f1 is dropped now, since there is no local definition for it

935

select f1 from c1;

936

ERROR: column "f1" does not exist

937

drop table p1 cascade;

938

NOTICE: drop cascades to table c1

939

create table p1 (f1 int, f2 int);

940

create table c1 () inherits(p1);

941

-- should be rejected since c1.f1 is inherited

942

alter table c1 drop column f1;

943

ERROR: cannot drop inherited column "f1"

944

alter table only p1 drop column f1;

945

-- c1.f1 is NOT dropped, but must now be considered non-inherited

946

alter table c1 drop column f1;

947

drop table p1 cascade;

948

NOTICE: drop cascades to table c1

949

create table p1 (f1 int, f2 int);

950

create table c1 (f1 int not null) inherits(p1);

951

NOTICE: merging column "f1" with inherited definition

952

-- should be rejected since c1.f1 is inherited

953

alter table c1 drop column f1;

954

ERROR: cannot drop inherited column "f1"

955

alter table only p1 drop column f1;

956

-- c1.f1 is still there, but no longer inherited

957

alter table c1 drop column f1;

958

drop table p1 cascade;

959

NOTICE: drop cascades to table c1

960

create table p1(id int, name text);

961

create table p2(id2 int, name text, height int);

962

create table c1(age int) inherits(p1,p2);

963

NOTICE: merging multiple inherited definitions of column "name"

964

create table gc1() inherits (c1);

965

select relname, attname, attinhcount, attislocal

966

from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)

967

where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped

968

order by relname, attnum;

969

relname | attname | attinhcount | attislocal

970

---------+---------+-------------+------------

971

c1 | id | 1 | f

972

c1 | name | 2 | f

973

c1 | id2 | 1 | f

974

c1 | height | 1 | f

975

c1 | age | 0 | t

976

gc1 | id | 1 | f

977

gc1 | name | 1 | f

978

gc1 | id2 | 1 | f

979

gc1 | height | 1 | f

980

gc1 | age | 1 | f

981

p1 | id | 0 | t

982

p1 | name | 0 | t

983

p2 | id2 | 0 | t

984

p2 | name | 0 | t

985

p2 | height | 0 | t

986

(15 rows)

987

988

-- should work

989

alter table only p1 drop column name;

990

-- should work. Now c1.name is local and inhcount is 0.

991

alter table p2 drop column name;

992

-- should be rejected since its inherited

993

alter table gc1 drop column name;

994

ERROR: cannot drop inherited column "name"

995

-- should work, and drop gc1.name along

996

alter table c1 drop column name;

997

-- should fail: column does not exist

998

alter table gc1 drop column name;

999

ERROR: column "name" of relation "gc1" does not exist

1000

-- should work and drop the attribute in all tables

1001

alter table p2 drop column height;

1002

select relname, attname, attinhcount, attislocal

1003

from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)

1004

where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped

1005

order by relname, attnum;

1006

relname | attname | attinhcount | attislocal

1007

---------+---------+-------------+------------

1008

c1 | id | 1 | f

1009

c1 | id2 | 1 | f

1010

c1 | age | 0 | t

1011

gc1 | id | 1 | f

1012

gc1 | id2 | 1 | f

1013

gc1 | age | 1 | f

1014

p1 | id | 0 | t

1015

p2 | id2 | 0 | t

1016

(8 rows)

1017

1018

drop table p1, p2 cascade;

1019

NOTICE: drop cascades to table c1

1020

NOTICE: drop cascades to table gc1

1021

1022

-- Test the ALTER TABLE WITHOUT OIDS command

1023

1024

create table altstartwith (col integer) with oids;

1025

insert into altstartwith values (1);

1026

select oid > 0, * from altstartwith;

1027

?column? | col

1028

----------+-----

1029

t | 1

1030

(1 row)

1031

1032

alter table altstartwith set without oids;

1033

select oid > 0, * from altstartwith; -- fails

1034

ERROR: column "oid" does not exist

1035

select * from altstartwith;

1036

col

1037

-----

1038

1039

(1 row)

1040

1041

-- Run inheritance tests

1042

create table altwithoid (col integer) with oids;

1043

-- Inherits parents oid column

1044

create table altinhoid () inherits (altwithoid) without oids;

1045

insert into altinhoid values (1);

1046

select oid > 0, * from altwithoid;

1047

?column? | col

1048

----------+-----

1049

t | 1

1050

(1 row)

1051

1052

select oid > 0, * from altinhoid;

1053

?column? | col

1054

----------+-----

1055

t | 1

1056

(1 row)

1057

1058

alter table altwithoid set without oids;

1059

alter table altinhoid set without oids;

1060

select oid > 0, * from altwithoid; -- fails

1061

ERROR: column "oid" does not exist

1062

select oid > 0, * from altinhoid; -- fails

1063

ERROR: column "oid" does not exist

1064

select * from altwithoid;

1065

col

1066

-----

1067

1068

(1 row)

1069

1070

select * from altinhoid;

1071

col

1072

-----

1073

1074

(1 row)

1075

1076

-- test renumbering of child-table columns in inherited operations

1077

create table p1 (f1 int);

1078

create table c1 (f2 text, f3 int) inherits (p1);

1079

alter table p1 add column a1 int check (a1 > 0);

1080

alter table p1 add column f2 text;

1081

NOTICE: merging definition of column "f2" for child "c1"

1082

insert into p1 values (1,2,'abc');

1083

insert into c1 values(11,'xyz',33,0); -- should fail

1084

ERROR: new row for relation "c1" violates check constraint "c1_a1_check"

1085

insert into c1 values(11,'xyz',33,22);

1086

select * from p1;

1087

f1 | a1 | f2

1088

----+----+-----

1089

1 | 2 | abc

1090

11 | 22 | xyz

1091

(2 rows)

1092

1093

update p1 set a1 = a1 + 1, f2 = upper(f2);

1094

select * from p1;

1095

f1 | a1 | f2

1096

----+----+-----

1097

1 | 3 | ABC

1098

11 | 23 | XYZ

1099

(2 rows)

1100

1101

drop table p1 cascade;

1102

NOTICE: drop cascades to table c1

1103

NOTICE: drop cascades to constraint c1_a1_check on table c1

1104

-- test that operations with a dropped column do not try to reference

1105

-- its datatype

1106

create domain mytype as text;

1107

create temp table foo (f1 text, f2 mytype, f3 text);

1108

insert into foo values('aa','bb','cc');

1109

select * from foo;

1110

f1 | f2 | f3

1111

----+----+----

1112

aa | bb | cc

1113

(1 row)

1114

1115

drop domain mytype cascade;

1116

NOTICE: drop cascades to table foo column f2

1117

select * from foo;

1118

f1 | f3

1119

----+----

1120

aa | cc

1121

(1 row)

1122

1123

insert into foo values('qq','rr');

1124

select * from foo;

1125

f1 | f3

1126

----+----

1127

aa | cc

1128

qq | rr

1129

(2 rows)

1130

1131

update foo set f3 = 'zz';

1132

select * from foo;

1133

f1 | f3

1134

----+----

1135

aa | zz

1136

qq | zz

1137

(2 rows)

1138

1139

select f3,max(f1) from foo group by f3;

1140

f3 | max

1141

----+-----

1142

zz | qq

1143

(1 row)

1144

1145

-- Simple tests for alter table column type

1146

alter table foo alter f1 TYPE integer; -- fails

1147

ERROR: column "f1" cannot be cast to type "pg_catalog.int4"

1148

alter table foo alter f1 TYPE varchar(10);

1149

create table anothertab (atcol1 serial8, atcol2 boolean,

1150

constraint anothertab_chk check (atcol1 <= 3));

1151

NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1"

1152

insert into anothertab (atcol1, atcol2) values (default, true);

1153

insert into anothertab (atcol1, atcol2) values (default, false);

1154

select * from anothertab;

1155

atcol1 | atcol2

1156

--------+--------

1157

1 | t

1158

2 | f

1159

(2 rows)

1160

1161

alter table anothertab alter column atcol1 type boolean; -- fails

1162

ERROR: column "atcol1" cannot be cast to type "pg_catalog.bool"

1163

alter table anothertab alter column atcol1 type integer;

1164

select * from anothertab;

1165

atcol1 | atcol2

1166

--------+--------

1167

1 | t

1168

2 | f

1169

(2 rows)

1170

1171

insert into anothertab (atcol1, atcol2) values (45, null); -- fails

1172

ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk"

1173

insert into anothertab (atcol1, atcol2) values (default, null);

1174

select * from anothertab;

1175

atcol1 | atcol2

1176

--------+--------

1177

1 | t

1178

2 | f

1179

3 |

1180

(3 rows)

1181

1182

alter table anothertab alter column atcol2 type text

1183

using case when atcol2 is true then 'IT WAS TRUE'

1184

when atcol2 is false then 'IT WAS FALSE'

1185

else 'IT WAS NULL!' end;

1186

select * from anothertab;

1187

atcol1 | atcol2

1188

--------+--------------

1189

1 | IT WAS TRUE

1190

2 | IT WAS FALSE

1191

3 | IT WAS NULL!

1192

(3 rows)

1193

1194

alter table anothertab alter column atcol1 type boolean

1195

using case when atcol1 % 2 = 0 then true else false end; -- fails

1196

ERROR: default for column "atcol1" cannot be cast to type "pg_catalog.bool"

1197

alter table anothertab alter column atcol1 drop default;

1198

alter table anothertab alter column atcol1 type boolean

1199

using case when atcol1 % 2 = 0 then true else false end; -- fails

1200

ERROR: operator does not exist: boolean <= integer

1201

HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

1202

alter table anothertab drop constraint anothertab_chk;

1203

alter table anothertab alter column atcol1 type boolean

1204

using case when atcol1 % 2 = 0 then true else false end;

1205

select * from anothertab;

1206

atcol1 | atcol2

1207

--------+--------------

1208

f | IT WAS TRUE

1209

t | IT WAS FALSE

1210

f | IT WAS NULL!

1211

(3 rows)

1212

1213

drop table anothertab;

1214

create table another (f1 int, f2 text);

1215

insert into another values(1, 'one');

1216

insert into another values(2, 'two');

1217

insert into another values(3, 'three');

1218

select * from another;

1219

f1 | f2

1220

----+-------

1221

1 | one

1222

2 | two

1223

3 | three

1224

(3 rows)

1225

1226

alter table another

1227

alter f1 type text using f2 || ' more',

1228

alter f2 type bigint using f1 * 10;

1229

select * from another;

1230

f1 | f2

1231

------------+----

1232

one more | 10

1233

two more | 20

1234

three more | 30

1235

(3 rows)

1236

1237

drop table another;

Older »