Postgresql – Postgres Error for table

postgresql-9.3

I want to create this table, but i can’t,

Look this error.

CREATE TABLE ATTR(

    window character varying(64) NOT NULL

);

ERROR: syntax error at or near "window"
LINE 2: window character varying(64) NOT NULL

Best Answer

window is a reserved word. You can create a column with that name if you enclose it in double quotes:

CREATE TABLE ATTR
(
  "window" character varying(64) NOT NULL
);

More details about identifiers, quoted identifiers and keywords can be found in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

But I highly recommend you find a different name that does not need to be quoted. Using quoted identifiers is more trouble in the long run than they are worth it.

Related Solutions

Postgresql – How to create an index on an integer json property in postgres

Try this instead:

CREATE INDEX user_reputation_idx ON users(cast("user"->>'reputation' AS int));

The Postgres syntax shortcut :: for casts is not allowed without additional parentheses in an index definition (see @bma's comment). It works with the standard SQL function, though: cast(expression AS type) This is not related to the json type per se.

Either way, you can still use the syntax shortcut expression::type in expressions that utilize the index.

PostgreSQL – RANGE Clause Error in Window Function

PostgreSQL doesn't support the RANGE clause yet. So even if you'd got the syntax right, it would've just failed with:

regress=> SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 FOLLOWING);
ERROR:  RANGE FOLLOWING is only supported with UNBOUNDED
LINE 1: SELECT row_number() OVER (RANGE BETWEEN CURRENT ROW AND 42 F...

To work around this, you can do a left outer join on generate_series(first_day, last_day, INTERVAL '1' DAY) then use a ROWS based window. That way a row exists for every day. It's a whole lot less efficient but unless you feel like getting your hands dirty in PostgreSQL's source code, it's probably the only option.

Best Answer

Related Solutions

Postgresql – How to create an index on an integer json property in postgres

PostgreSQL – RANGE Clause Error in Window Function

Related Question