Postgresql – Syntax error when trying to add columns to table in DBeaver (postgresql)

alter-tableerrorspostgresqlsyntax

I've tried this a few different ways but every time I get a syntax error when trying to add columns to a table.

I'm new to databases so it's probably something basic that I'm doing wrong, but I just don't see why this isn't working.

Any input would be appreciated.

P.S. at the end of the query line there is a ";"… You just can't see it in the screenshot.

So far I've tried changing "add columns" to "add column" and just "add" followed by the columns. I'm aware I can just use the GUI to copy columns in DBeaver but I'd rather get this SQL working.

enter image description here

Best Answer

According to Postgres docs the correct syntax is:

ALTER TABLE <your_table>
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>;

Have a look at Postgres tutorials on PostgreSQL ADD COLUMN: Add One Or More Columns To a Table.

I've set up a minimal example that reproduces your error:

CREATE TABLE newtable (id int);

ALTER TABLE newtable
    ADD COLUMNS f1 float8, f2 int;

ERROR: syntax error at or near "float8"
LINE 2: ADD COLUMNS f1 float8, f2 int; ^

ALTER TABLE newtable
    ADD COLUMN f1 float8, f2 int, f3 text;

ERROR: syntax error at or near "f2"
LINE 2: ADD COLUMN f1 float8, f2 int, f3 text;

Using the correct syntax:

ALTER TABLE newtable
    ADD COLUMN f1 float8,
    ADD COLUMN f2 int,
    ADD COLUMN f3 text;
✓

db<>fiddle here

Related Question