Обсуждение: Some newbie question

Hello

Just trying to play around with PostgreSQL. Some commands and their
result are:

test=# create table ncbi ( id serial, title varchar(50), abstract text,
primary key(id));
NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
serial column "ncbi.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"ncbi_pkey" for table "ncbi"
CREATE TABLE
test=# drop table ncbi;
DROP TABLE
test=# create table ncbi ( id serial, title varchar(50), abstract text);

NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
serial column "ncbi.id"
CREATE TABLE

======

q1) Does 'implicit index' means that they have given a default name to
the PK which I defined in the first query since I didnt give a name or
does it mean one another index is created apart from the PRIMARY KEY?

Ritesh

On Thu, 2006-10-19 at 18:32 -0500, Ritesh Nadhani wrote:
> Hello
>
> Just trying to play around with PostgreSQL. Some commands and their
> result are:
>
> test=# create table ncbi ( id serial, title varchar(50), abstract text,
> primary key(id));
> NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
> serial column "ncbi.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "ncbi_pkey" for table "ncbi"
> CREATE TABLE
> test=# drop table ncbi;
> DROP TABLE
> test=# create table ncbi ( id serial, title varchar(50), abstract text);
>
> NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
> serial column "ncbi.id"
> CREATE TABLE
>
> ======
>
> q1) Does 'implicit index' means that they have given a default name to
> the PK which I defined in the first query since I didnt give a name or
> does it mean one another index is created apart from the PRIMARY KEY?
>

It only creates one index, the primary key index.

Regards,
    Jeff Davis

Thanks.

So the implicit value means that PG gave it a name?

Jeff Davis wrote:
> On Thu, 2006-10-19 at 18:32 -0500, Ritesh Nadhani wrote:
>> Hello
>>
>> Just trying to play around with PostgreSQL. Some commands and their
>> result are:
>>
>> test=# create table ncbi ( id serial, title varchar(50), abstract text,
>> primary key(id));
>> NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
>> serial column "ncbi.id"
>> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>> "ncbi_pkey" for table "ncbi"
>> CREATE TABLE
>> test=# drop table ncbi;
>> DROP TABLE
>> test=# create table ncbi ( id serial, title varchar(50), abstract text);
>>
>> NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
>> serial column "ncbi.id"
>> CREATE TABLE
>>
>> ======
>>
>> q1) Does 'implicit index' means that they have given a default name to
>> the PK which I defined in the first query since I didnt give a name or
>> does it mean one another index is created apart from the PRIMARY KEY?
>>
>
> It only creates one index, the primary key index.
>
> Regards,
>     Jeff Davis
>
>
>

On Thu, 2006-10-19 at 19:05 -0500, Ritesh Nadhani wrote:
> Thanks.
>
> So the implicit value means that PG gave it a name?
>

Right, when you declare a column to be a primary key, PostgreSQL must
create an index to enforce the unique constraint. This index must have
some kind of name, so PostgreSQL makes up a name, and the NOTICE is just
to let you know the name of the index that it's creating.

Regards,
    Jeff Davis

Ritesh Nadhani wrote:
> Hello
>
> Just trying to play around with PostgreSQL. Some commands and their
> result are:
>
> test=# create table ncbi ( id serial, title varchar(50), abstract text,
> primary key(id));
> NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
> serial column "ncbi.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "ncbi_pkey" for table "ncbi"
> CREATE TABLE
> test=# drop table ncbi;
> DROP TABLE
> test=# create table ncbi ( id serial, title varchar(50), abstract text);
> NOTICE:  CREATE TABLE will create implicit sequence "ncbi_id_seq" for
> serial column "ncbi.id"
> CREATE TABLE
>
> ======
>
> q1) Does 'implicit index' means that they have given a default name to
> the PK which I defined in the first query since I didnt give a name or
> does it mean one another index is created apart from the PRIMARY KEY?
>
> Ritesh
>

Precisely - 'create implicit index' means you didn't give a CREATE INDEX
command - but the index was created to satisfy the primary key
constraint you entered in your first create table command.
Just as the sequence is implicitly created to satisfy the serial datatype.


--

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz