How can I pass this table from sql server to postgress?-CodePudding

Does anyone know how I can make a generated column in postgress? This is the code in SQL server:

CREATE TABLE [dbo].[PRICE](
    [ISIN] [varchar](50) NOT NULL,
    [Nemo] [varchar](50) NOT NULL,
    [Month(t)] [int] NOT NULL,
    [Month(t-1)] [int] NOT NULL,
    [Month(t-2)] [int] NOT NULL
        )

I searched the internet and found that the way to do it in postgress is by using GENERATE ALWAYS AS but I get an error because the month column is repeated several times, that is the question that is supposed to be differentiated when doing the subtraction.

CREATE TABLE PRICE(
    ISIN varchar(50) NOT NULL,
    Nemo varchar(50) NOT NULL,
    Month int GENERATED ALWAYS AS (t) STORED,
    Month int GENERATED ALWAYS AS (t-1) STORED,
    Month int GENERATED ALWAYS AS (t-2) STORED,
)

CodePudding user response：

postgres has a different syntax, You refer the column by name and create new columns.

Reference

CREATE TABLE PRICE(
    ISIN varchar(50) NOT NULL,
    Nemo varchar(50) NOT NULL,
    Month int,
    Month_1 int GENERATED ALWAYS AS (Month-1) STORED,
    Month_2 int GENERATED ALWAYS AS (Month-2) STORED
);

Testing

insert into PRICE values('aaa','bbb',20)
select * from price;

isin	nemo	month	month_1	month_2
aaa	bbb	20	19	18

View on DB Fiddle

CodePudding user response：

Those aren't generated columns, they just have non-standard names that need quoted identifiers. In SQL Server such a column needs to be enclosed in square brackets. Postgres follows the SQL standard where such names need to be enclosed in double quotes:

CREATE TABLE PRICE (
  ISIN varchar(50) NOT NULL,
  Nemo varchar(50) NOT NULL,
  "Month(t)" int NOT NULL,
  "Month(t-1)" int NOT NULL,
  "Month(t-2)" int NOT NULL
)

Example with SQL Server

Example with PostgreSQL