From: | Bharanee Rathna <deepfryed(at)gmail(dot)com> |
---|---|
To: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Altering multiple column types |
Date: | 2019-08-02 08:06:01 |
Message-ID: | CAOX4-H61o9C4t7ZdvoJyV6+NGOqrVMrU1hYu7VqE7G0Wih_3FA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Luca,
testing this using docker images. I can replicate it with 10.9-alpine
bash-5.0# psql -h127.0.0.1 -Upostgres test
psql (10.9)
Type "help" for help.
test=# \d users
Table "public.users"
Column | Type | Collation | Nullable |
Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null |
nextval('users_id_seq'::regclass)
name | character varying(255) | | |
age | integer | | |
email | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_idx" btree (email)
"users_name_idx" btree (name)
test=# alter table users alter column name type text, alter column email
type text;
ERROR: relation "users_name_idx" already exists
test=# select version();
version
---------------------------------------------------------------------------------------
PostgreSQL 10.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0)
8.3.0, 64-bit
(1 row)
and 11.4
psql (11.4)
Type "help" for help.
test=# \d users
Table "public.users"
Column | Type | Collation | Nullable |
Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null |
nextval('users_id_seq'::regclass)
name | character varying(255) | | |
age | integer | | |
email | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_idx" btree (email)
"users_name_idx" btree (name)
test=# alter table users alter column name type text, alter column email
type text;
ERROR: relation "users_name_idx" already exists
test=# select version();
version
---------------------------------------------------------------------------------------
PostgreSQL 11.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0)
8.3.0, 64-bit
(1 row)
Not sure what's going on at my end ...
On Fri, 2 Aug 2019 at 17:44, Luca Ferrari <fluca1978(at)gmail(dot)com> wrote:
> On Fri, Aug 2, 2019 at 9:39 AM Bharanee Rathna <deepfryed(at)gmail(dot)com>
> wrote:
> >
> > Hi Luca,
> >
> > I've tried it with a different client and Postgres 10.9, no luck
> >
> > psql (10.3, server 10.9)
>
> I've fired up a 12beta2 and it works, either with psql 12 or psql 11.4 on
> linux.
> What if you run the statements within another client (pgadmin, a java
> client or something else)?
>
> % psql -U postgres testdb
> psql (12beta2)
> Type "help" for help.
>
> testdb=# create table users(id serial primary key, name varchar(255),
> age int, email varchar(255));
> CREATE TABLE
> testdb=# create index users_email_idx on users(email);
> CREATE INDEX
> testdb=# alter table users alter column name type text, alter column
> email type text;
> ALTER TABLE
> testdb=# SELECT version();
> version
>
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 12beta2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 8.3.0-6ubuntu1~18.10.1) 8.3.0, 64-bit
> (1 row)
>
>
>
>
> % ~/git/misc/PostgreSQL/pgenv/pgsql-11.4/bin/psql -U postgres testdb
>
> psql (11.4, server 12beta2)
> WARNING: psql major version 11, server major version 12.
> Some psql features might not work.
> Type "help" for help.
>
> testdb=# drop table users;
> DROP TABLE
> testdb=# create table users(id serial primary key, name varchar(255),
> age int, email varchar(255));
> CREATE TABLE
> testdb=# create index users_email_idx on users(email);
> CREATE INDEX
> testdb=# alter table users alter column name type text, alter column
> email type text;
> ALTER TABLE
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Shelver | 2019-08-02 08:32:55 | Re: PGAdmin4.11.1 on Ubuntu 18.04 |
Previous Message | Luca Ferrari | 2019-08-02 07:43:49 | Re: Altering multiple column types |