From: | Stoyan Genov <genov(at)digsys(dot)bg> |
---|---|
To: | Klaus Woelfel <woelfel(at)mayn(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org, genov(at)lorna(dot)digsys(dot)bg |
Subject: | Re: [SQL] showing the DEFAULT of a field |
Date: | 1999-11-06 12:01:45 |
Message-ID: | 199911061201.OAA00714@lorna.digsys.bg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi.
Please look carefully at the sequence of commands:
shell#>createdb tmp
shell#>psql tmp
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i386-pc-bsdi4.0.1, compiled by gcc 2.7.2.1]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: tmp
tmp=> \d
Couldn't find any tables, sequences or indices!
tmp=> create table test (i int4 primary key, j int4 default 1, t text not
null);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test_pkey'
for table 'test'
CREATE
tmp=> \d
Database = tmp
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| genov | test | table |
| genov | test_pkey | index |
+------------------+----------------------------------+----------+
tmp=> \d test
Table = test
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| i | int4 not null |
4 |
| j | int4 default 1 |
4 |
| t | text not null |
var |
+----------------------------------+----------------------------------+-----
--+
Index: test_pkey
tmp=> \q
shell#>pg_dump tmp
\connect - genov
CREATE TABLE "test" (
"i" int4 NOT NULL,
"j" int4 DEFAULT 1,
"t" text NOT NULL);
COPY "test" FROM stdin;
\.
CREATE UNIQUE INDEX "test_pkey" on "test" using btree ( "i" "int4_ops" );
shell#>
So, I hope you see how restrictions upon fields are expressed and created.
The probably confusing stuff is about the primary key. Primary keys are
const-
ructed using unique indices upon the fields you say "primary key" about
during
the creation of a table. These unique indices will not allow a duplicate
value
in the field, which is, technically speaking, the primary goal of a primary
key
field.
Watch this:
tmp=> insert into test (i) values (1);
ERROR: ExecAppend: Fail to add null value in not null attribute t
tmp=> insert into test (i,t) values (1,'a line');
INSERT 31375 1
tmp=> insert into test (i,t) values (1,'another line');
ERROR: Cannot insert a duplicate key into a unique index
tmp=> \q
This is it. If something is unclear, do write back.
Stoyan Genov
From | Date | Subject | |
---|---|---|---|
Next Message | Stoyan Genov | 1999-11-06 12:13:10 | Re: [SQL] Accessing other database... |
Previous Message | Stoyan Genov | 1999-11-06 11:44:34 | Re: [SQL] pg_dump and "archive = none" ?? |