Altering multiple column types
Hi, I'm encountering an issue altering multiple column types in a single ALTER TABLE psql (12beta2, server 11.4) Type "help" for help. test=# create table users(id serial primary key, name varchar(255), age int, email varchar(255)); CREATE TABLEtest=# create index users_name_idx on users(name); CREATE INDEXtest=# create index users_email_idx on users(email); CREATE INDEXtest=# alter table users alter column name type text, alter column email type text; psql: ERROR: relation "users_name_idx" already exists test=# alter table users alter column name type text; ALTER TABLE test=# alter table users alter column email type text; ALTER TABLE Is there a limitation I'm missing in the docs ? https://www.postgresql.org/docs/11/sql-altertable.html Thanks
Re: Altering multiple column types
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 wrote:
> On Fri, Aug 2, 2019 at 9:39 AM Bharanee Rathna
> 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
>
Re: Altering multiple column types
Thanks for confirming, so I know I'm not going crazy :) FWIW, it works on 10.3 to 10.8 (I think) and looks like a regression. psql (10.3 (Debian 10.3-2)) Type "help" for help. test=# create table users(id serial primary key, name varchar(255), email varchar(255), age int); CREATE TABLE Time: 26.650 ms test=# create index users_name_idx on users(name); create index users_email_idx on users(email); CREATE INDEX Time: 15.660 ms CREATE INDEX Time: 12.065 ms test=# alter table users alter column name type text, alter column email type text; ALTER TABLE Time: 7.213 ms test=# select version(); version - PostgreSQL 10.3 (Debian 10.3-2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.3.0-11) 7.3.0, 64-bit (1 row) Time: 2.683 ms
Re: Altering multiple column types
Thanks Tom!
ISO8601 vs POSIX offset clarification
Hi, the documentation around how numeric offsets are parsed from strings is a bit confusing, are they supposed to be treated as ISO8601 or POSIX ? e.g. select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone '+11:00'; timezone - 2017-11-30 13:00:00 select '2017-12-01 11:00:00 -11:00'::timestamp with time zone at time zone '+11:00'; timezone - 2017-12-01 11:00:00 The Table 8-12. Time Zone Input section at https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems to imply that numeric offsets would be treated as ISO8601. It's all a big confusing and would appreciate some clarification or pointer to documentation. Thanks
Re: ISO8601 vs POSIX offset clarification
Sorry I didn't mean for it to come out as a complaint, just that I am confused since the result of the SQL query was not what I expected. I expected +11:00 to be 11 hours east of UTC which wasn't the case. On 4 December 2017 at 13:55, Tom Lane wrote: > Bharanee Rathna writes: > > the documentation around how numeric offsets are parsed from strings is a > > bit confusing, are they supposed to be treated as ISO8601 or POSIX ? > > Our documentation about this says clearly that Postgres considers offsets > to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style > time zone names. > > > The Table 8-12. Time Zone Input section at > > https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems > to > > imply that numeric offsets would be treated as ISO8601. > > How do you read an entry such as > > -8:00 | ISO-8601 offset for PST > > as being in any way vague about which convention the "-8" is read in? > > regards, tom lane >
Re: ISO8601 vs POSIX offset clarification
To be more specific, I expected the output of both these queries to be the same. # select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone '+11:00'; timezone - 2017-11-30 13:00:00 # select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone 'Australia/Melbourne'; timezone - 2017-12-01 11:00:00 Cheers On 4 December 2017 at 13:59, Bharanee Rathna wrote: > Sorry I didn't mean for it to come out as a complaint, just that I am > confused since the result of the SQL query was not what I expected. I > expected +11:00 to be 11 hours east of UTC which wasn't the case. > > > > On 4 December 2017 at 13:55, Tom Lane wrote: > >> Bharanee Rathna writes: >> > the documentation around how numeric offsets are parsed from strings is >> a >> > bit confusing, are they supposed to be treated as ISO8601 or POSIX ? >> >> Our documentation about this says clearly that Postgres considers offsets >> to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style >> time zone names. >> >> > The Table 8-12. Time Zone Input section at >> > https://www.postgresql.org/docs/9.3/static/datatype-datetime.html >> seems to >> > imply that numeric offsets would be treated as ISO8601. >> >> How do you read an entry such as >> >> -8:00 | ISO-8601 offset for PST >> >> as being in any way vague about which convention the "-8" is read in? >> >> regards, tom lane >> > >
