How to syntactically indicate a table for altering its column in postgres sql-CodePudding

I am trying to alter a table by changing the datatypes of a few columns from text to integer/boolean/date

When I try to execute it as

alter table "TimeSeriesData" alter COLUMN "details_id" TYPE BIGINT;

I get this error

ERROR:  column "details_id" cannot be cast automatically to type bigintHINT:  You might need
to specify "USING "details_id"::bigint".uery

When I then tried to execute as

alter table "TimeSeriesData" alter COLUMN "details_id" TYPE BIGINT USING (details_id::BIGINT);

I got the following error

ERROR:  column "details_id" does not existLINE 1: ... COLUMN "details_id" TYPE BIGINT USING 
(timeSeries...^HINT:  Perhaps you meant to reference the column "TimeSeriesData.details_id".uery

Finally tried this way, as per suggestion from the SQL GUI

alter table "TimeSeriesData" alter COLUMN "TimeSeriesData"."details_id" TYPE BIGINT 
USING (details_id::BIGINT);

Again, it let to this error

ERROR:  syntax error at or near "."LINE 1: ...eriesData" alter COLUMN 
"TimeSeriesData"."details_...  ^uery

I am using TimescaleDB and heidiSQL as GUI for it. Can you please help on how to proceed ahead

CodePudding user response：

You can follow these steps:

Create a new column.
Migrate (cast) data to this column.
Review data migration.
Drop old column.
Rename new column.

For example:

CREATE TABLE test (
  id Int PRIMARY KEY,
  my_bool VARCHAR(5)
);

INSERT INTO test (id, my_bool)
VALUES (1, '1');
INSERT INTO test (id, my_bool)
VALUES (2, '0');

ALTER TABLE test ADD COLUMN my_bool_2 int;

UPDATE test SET my_bool_2 = CAST(my_bool as int);
ALTER TABLE test DROP COLUMN my_bool;
ALTER TABLE test RENAME COLUMN my_bool_2 TO my_bool;

CodePudding user response：

I tried a small example here, and I think you missed the USING clause.

ALTER TABLE my_table ALTER COLUMN value TYPE BIGINT USING value::BIGINT;

Here is the full example:

DROP TABLE my_table;
CREATE TABLE my_table ( time TIMESTAMP NOT NULL,  value varchar);
SELECT create_hypertable('my_table', 'time');


INSERT INTO my_table (time, value) VALUES
('2021-08-26 10:09:00.01', '1012311'),
('2021-08-26 10:09:00.08',  '1022220'),
('2021-08-26 10:09:00.40',  '103333000');
ALTER TABLE my_table ALTER COLUMN value TYPE BIGINT USING value::BIGINT;

Then, check the results:

tsdb=> table my_table;
┌────────────────────────┬───────────┐
│          time          │   value   │
├────────────────────────┼───────────┤
│ 2021-08-26 10:09:00.01 │   1012311 │
│ 2021-08-26 10:09:00.08 │   1022220 │
│ 2021-08-26 10:09:00.4  │ 103333000 │
└────────────────────────┴───────────┘
(3 rows)