PostgreSql doesn't understand column name

Question

I've created table in PostgreSql:

DROP TABLE IF EXISTS es_events;

CREATE TABLE IF NOT EXISTS es_events (
  id SERIAL,
  name VARCHAR (50) NOT NULL,
  version INT NOT NULL,
  data BYTEA NOT NULL
);

now i'm trying to select from this table:

SELECT COALESCE(MAX(version),0)
                            FROM public.es_events
                            WHERE name = 'asd';

but as a result i receive:

ERROR:  column "version" does not exist
LINE 1: SELECT COALESCE(MAX(version),0)
                            ^
HINT:  Perhaps you meant to reference the column "es_events.  version".
SQL state: 42703
Character: 21

i tried to use "es_events. version" but it doesn'r help:

ERROR:  column "es_events.  version" does not exist
LINE 1: SELECT COALESCE(MAX("es_events.  version"),0)
                            ^
SQL state: 42703
Character: 21

What i'm doing wrong? How to select from table ?

# psql --version
psql (PostgreSQL) 13.3 (Debian 13.3-1.pgdg100+1)

This "es_events. version" seems to indicate there are hidden space characters in the name. Try your select with SELECT COALESCE(MAX("es_events. version"),0) or SELECT COALESCE(MAX(" version"),0) to confirm`. — Adrian Klaver
– Adrian Klaver, Commented Jun 9, 2021 at 21:19
@eshirvana, no the error is just showing the line the error occurred on. It does not include the rest of the query as it is not relevant. — Adrian Klaver
– Adrian Klaver, Commented Jun 9, 2021 at 21:22
I tried to use "es_events. version" insted of just "version", the same result - ERROR: column "es_events. version" does not exist — Maxim Kitsenko
– Maxim Kitsenko, Commented Jun 9, 2021 at 21:22
There are multiple spaces in the name. Cut and paste the column name as returned by the error. Also add to your question the output of \dt es_events in psql. — Adrian Klaver
– Adrian Klaver, Commented Jun 9, 2021 at 21:24

Laurenz Albe · Accepted Answer · 2021-06-09 21:24:26Z

1

According to your error message, you created the table with a column named " version" (with two leading spaces) rather than version.

You can rename the column:

ALTER TABLE es_events RENAME "  version" to version;

answered Jun 9, 2021 at 21:24

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Maxim Kitsenko Over a year ago

Thank you! I've recreated table with "REATE TABLE IF NOT EXISTS es_events (id SERIAL,name VARCHAR (50) NOT NULL,version INT NOT NULL,data BYTEA NOT NULL)" it helped. But why it was created with spaces previously? how to escape column names ? // i created table with help of pgAdmin

Adrian Klaver Over a year ago

@MaximKitsenko. Use psql, pgAdmin is just an unnecessary layer that gets in the way more then it helps.

Jasen Over a year ago

Column names are escaped using "these quotes"

Laurenz Albe Over a year ago

I cannot say what you did to create this column. Perhaps entered a value with two leading spaces in the GUI.

Maxim Kitsenko · Accepted Answer · 2021-06-09 21:34:21Z

0

There were spaces in create table, after fixing table creation script names started to work corectly:

CREATE TABLE IF NOT EXISTS es_events (id SERIAL,name VARCHAR (50) NOT NULL,version INT NOT NULL,data BYTEA NOT NULL)

answered Jun 9, 2021 at 21:34

Maxim Kitsenko

2,3733 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

PostgreSql doesn't understand column name

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related