pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrename primary key
6+ messages / 3 participants
[nested] [flat]
* rename primary key
@ 2010-01-15 03:28 Seb <spluque@gmail.com>
2010-01-15 03:34 ` Re: rename primary key Ian Barwick <barwick@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Seb @ 2010-01-15 03:28 UTC (permalink / raw)
To: pgsql-sql
Hi,
Is it possible to rename a primary key? I learnt that to rename foreign
keys one has to drop it and recreate with the new name, or add a new new
and drop the old one. What is the approach for primary keys? Thanks.
Cheers,
--
Seb
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: rename primary key
2010-01-15 03:28 rename primary key Seb <spluque@gmail.com>
@ 2010-01-15 03:34 ` Ian Barwick <barwick@gmail.com>
2010-01-15 03:39 ` Re: rename primary key Seb <spluque@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ian Barwick @ 2010-01-15 03:34 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
2010/1/15 Seb <spluque@gmail.com>:
> Hi,
>
> Is it possible to rename a primary key? I learnt that to rename foreign
> keys one has to drop it and recreate with the new name, or add a new new
> and drop the old one. What is the approach for primary keys? Thanks.
Is this what you mean?
psql (8.4.2)
You are now connected to database "test".
test=# CREATE TABLE foo (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
test=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
test=# ALTER INDEX foo_pkey RENAME to bar_fkey;
ALTER INDEX
test=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
"bar_fkey" PRIMARY KEY, btree (id)
test=#
Ian Barwick
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: rename primary key
2010-01-15 03:28 rename primary key Seb <spluque@gmail.com>
2010-01-15 03:34 ` Re: rename primary key Ian Barwick <barwick@gmail.com>
@ 2010-01-15 03:39 ` Seb <spluque@gmail.com>
2010-01-15 06:35 ` Re: rename primary key Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 6+ messages in thread
From: Seb @ 2010-01-15 03:39 UTC (permalink / raw)
To: pgsql-sql
On Fri, 15 Jan 2010 12:34:15 +0900,
Ian Barwick <barwick@gmail.com> wrote:
[...]
> Is this what you mean?
[...]
Exactly! Thanks.
On a related note: how come pgadmin3 shows "Indexes (0)" for such a
table, even though an index does exist for the primary key? Are these
indexes created in a separate table that is looked up by the foo table?
--
Seb
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: rename primary key
2010-01-15 03:28 rename primary key Seb <spluque@gmail.com>
2010-01-15 03:34 ` Re: rename primary key Ian Barwick <barwick@gmail.com>
2010-01-15 03:39 ` Re: rename primary key Seb <spluque@gmail.com>
@ 2010-01-15 06:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-01-15 16:13 ` Re: rename primary key Seb <spluque@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2010-01-15 06:35 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
Le 15/01/2010 04:39, Seb a écrit :
> On Fri, 15 Jan 2010 12:34:15 +0900,
> Ian Barwick <barwick@gmail.com> wrote:
> [...]
> On a related note: how come pgadmin3 shows "Indexes (0)" for such a
> table, even though an index does exist for the primary key? Are these
> indexes created in a separate table that is looked up by the foo table?
>
Primary keys are constraints. They are enforced with an index, but
actually they are constraints. So we put them on the constraints nodes.
There is the same behaviour for unique constraints.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: rename primary key
2010-01-15 03:28 rename primary key Seb <spluque@gmail.com>
2010-01-15 03:34 ` Re: rename primary key Ian Barwick <barwick@gmail.com>
2010-01-15 03:39 ` Re: rename primary key Seb <spluque@gmail.com>
2010-01-15 06:35 ` Re: rename primary key Guillaume Lelarge <guillaume@lelarge.info>
@ 2010-01-15 16:13 ` Seb <spluque@gmail.com>
2010-01-27 14:19 ` Re: rename primary key Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 6+ messages in thread
From: Seb @ 2010-01-15 16:13 UTC (permalink / raw)
To: pgsql-sql
On Fri, 15 Jan 2010 07:35:17 +0100,
Guillaume Lelarge <guillaume@lelarge.info> wrote:
[...]
> Primary keys are constraints. They are enforced with an index, but
> actually they are constraints. So we put them on the constraints
> nodes.
> There is the same behaviour for unique constraints.
Thanks. Would there be any problem with listing the index used to
enforce the primary key constraint in the Indexes node to let us know of
its existence? In fact, psql does report it with the meta-command \d.
It was somewhat confusing to see Indexes(0), and then having to rename
an index to rename a primary key constraint.
--
Seb
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: rename primary key
2010-01-15 03:28 rename primary key Seb <spluque@gmail.com>
2010-01-15 03:34 ` Re: rename primary key Ian Barwick <barwick@gmail.com>
2010-01-15 03:39 ` Re: rename primary key Seb <spluque@gmail.com>
2010-01-15 06:35 ` Re: rename primary key Guillaume Lelarge <guillaume@lelarge.info>
2010-01-15 16:13 ` Re: rename primary key Seb <spluque@gmail.com>
@ 2010-01-27 14:19 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 6+ messages in thread
From: Guillaume Lelarge @ 2010-01-27 14:19 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
Le 15/01/2010 17:13, Seb a écrit :
> On Fri, 15 Jan 2010 07:35:17 +0100,
> Guillaume Lelarge <guillaume@lelarge.info> wrote:
>
> [...]
>
>> Primary keys are constraints. They are enforced with an index, but
>> actually they are constraints. So we put them on the constraints
>> nodes.
>
>> There is the same behaviour for unique constraints.
>
> Thanks. Would there be any problem with listing the index used to
> enforce the primary key constraint in the Indexes node to let us know of
> its existence? In fact, psql does report it with the meta-command \d.
> It was somewhat confusing to see Indexes(0), and then having to rename
> an index to rename a primary key constraint.
>
Sorry, forgot to answer this mail.
It was asked a few times before, and AFAICT the answer is still the
same. It is a constraint before being an index. The fact that it is also
an index is an implementation detail. I understand this is confusing.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-01-27 14:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-15 03:28 rename primary key Seb <spluque@gmail.com>
2010-01-15 03:34 ` Ian Barwick <barwick@gmail.com>
2010-01-15 03:39 ` Seb <spluque@gmail.com>
2010-01-15 06:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-01-15 16:13 ` Seb <spluque@gmail.com>
2010-01-27 14:19 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox