Received: from danbo.digsys.bg (root@danbo.digsys.bg [192.92.129.10]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA31864 for ; Sat, 6 Nov 1999 07:01:50 -0500 (EST) (envelope-from genov@lorna.digsys.bg) Received: from lorna.digsys.bg (root@lorna.digsys.bg [193.68.6.15]) by danbo.digsys.bg (8.9.0/8.9.0) with ESMTP id OAA04502; Sat, 6 Nov 1999 14:01:46 +0200 (EET) Received: from lorna.digsys.bg (genov@localhost.digsys.bg [127.0.0.1]) by lorna.digsys.bg (8.9.0/8.9.0) with ESMTP id OAA00714; Sat, 6 Nov 1999 14:01:45 +0200 (EET) Message-Id: <199911061201.OAA00714@lorna.digsys.bg> X-Mailer: exmh version 2.0.2 2/24/98 To: Klaus Woelfel cc: pgsql-sql@postgresql.org, genov@lorna.digsys.bg Subject: Re: [SQL] showing the DEFAULT of a field In-reply-to: Your message of "Fri, 05 Nov 1999 23:15:58 +0100." <99110523182005.02127@server> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Sat, 06 Nov 1999 14:01:45 +0200 From: Stoyan Genov Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id HAA31877 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