Question 90177 in PostgreSQL Question posted in
The official documentation can be found here.

How can I pass this table from SQL Server to Postgress? – Postgresql

Does anyone know how I can make a generated column in Postgres?

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 Postgres 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,
)
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search