1

I am trying to write a PostgreSQL script that meets the following requirements:

  1. Create a table with two columns, Id and Id2, which must always have identical values when a record is added to the table (this design is intentional and cannot be changed).
  2. Use a sequence to generate values for Id.
  3. Be executed from C# code, not from any PostgreSQL management tool like pgAdmin.

I tried with the following script but it failed:

CREATE SEQUENCE IF NOT EXISTS myTable_seq
START 1
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1;

CREATE TABLE IF NOT EXISTS myTable
(
     id bigint NOT NULL nextval('myTable_seq'),
     symbol bigint NOT NULL currval('myTable_seq'),
     CONSTRAINT PK_myTable_Id PRIMARY KEY (Id)
)

I got: ERROR: syntax error at or near 'nextval'

Improve this question
4
  • 2
    Looks like you might be missing the DEFAULT keyword... Commented Jan 27 at 13:05
  • Why not use an identity column? Then there is no need to create the sequence yourself, no need to maintain the permissions to use the sequence, etc. id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, Commented Jan 27 at 15:15
  • @FrankHeikens, because the OP wants the values for id and symbol to be the same and if you used GENERATED ALWAYS AS IDENTITY on each column they would not. Commented Jan 27 at 16:25
  • @AdrianKlaver You can use the sequence from the identity; no problem at all. CREATE TABLE IF NOT EXISTS myTable ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (SEQUENCE NAME myTable_seq), symbol bigint NOT NULL DEFAULT currval('myTable_seq') ); Commented Jan 27 at 16:52

1 Answer 1

Reset to default
1

Your syntax is almost right, but you need to add the word default. So it becomes:

CREATE TABLE IF NOT EXISTS myTable
(
     id bigint NOT NULL default nextval('myTable_seq'),
     symbol bigint NOT NULL default currval('myTable_seq'),
     CONSTRAINT PK_myTable_Id PRIMARY KEY (Id)
);
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I used a bit different script: CREATE TABLE IF NOT EXISTS myTable ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY (SEQUENCE NAME myTable_seq), symbol bigint NOT NULL default currval('myTable_seq'), CONSTRAINT PK_myTable_Id PRIMARY KEY (Id) ); and it works. Thank you for help!
There is a small issue that I should warn you about. The use of default only takes effect if a value is not supplied for the column. Even doing it the way you have done, does not prevent a user issuing an INSERT with a specific value for symbol. This would mean that symbol and id would have differing values. I was curious as to why you needed a table with two identical columns. It would be interesting to know!
You can get around that by doing symbol bigint NOT NULL generated always as (id) stored
Jonathan, the case is to create version of records.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.