Postgres Syntax Error at or Near "On"

postgres syntax error at or near ON

Supported Version

Per @klin's comment above, ON CONFLICT is only supported from PostgreSQL 9.5 onwards.

If you're on an earlier version, there's some great info in this answer: https://stackoverflow.com/a/17267423/361842

Unique Constraint

Add a unique index on activity_name. At present there's no constraints on that column, so there's no possibility for a conflict on that column.

CREATE UNIQUE INDEX UK_config_activity_log__activity_name 
ON config_activity_log (activity_name);

If, however, you don't want that column to be unique, what conflict are you envisaging / what's the issue you're hoping to resolve with the on conflict action?

See conflict_target in https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT

---

An alternative syntax is to modify your create statement to include the unique condition there; e.g.

CREATE TABLE IF NOT EXISTS config_activity_log
(
  id                      serial primary key,
  activity_name           varchar(100) NOT NULL UNIQUE,
  last_config_version     varchar(50) NOT NULL,
  activity_status         varchar(100) NOT NULL DEFAULT 'Awaiting for cofman',
  cofman_last_update      bigint NOT NULL DEFAULT -1,
  is_error                boolean DEFAULT FALSE,
  activity_timestamp      timestamp DEFAULT current_timestamp
);

Postgresql : syntax error at or near -

I have reproduced the issue in my system,

postgres=# alter user my-sys with password 'pass11';
ERROR:  syntax error at or near "-"
LINE 1: alter user my-sys with password 'pass11';
                       ^

Here is the issue,

psql is asking for input and you have given again the alter query see postgres-#That's why it's giving error at alter

postgres-# alter user "my-sys" with password 'pass11';
ERROR:  syntax error at or near "alter"
LINE 2: alter user "my-sys" with password 'pass11';
        ^

Solution is as simple as the error,

postgres=# alter user "my-sys" with password 'pass11';
ALTER ROLE

Postgres Error : syntax error at or near (

Probably you need commas between paranthesis for each record. And "Kenit" should be in quotes

insert into  Employee(no,name,phone) values ((1,'Kenit',999999999),
(2,'Kenit',999999999),(3,'Kenit',999999999),(4,'Kenit',999999999),
(5,'Kenit',999999999),(6,'Kenit',999999999))

Getting 'error: syntax error at or near...' in Postgresql insert query

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
(an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

Your string values are not quoted. It would have to be...

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
('an3cxZh8ZD3tdtqG4wuwPR', 'Alan Paton', 'alan-paton');

You could add quotes to your query, but don't. Your query as written is insecure and vulnerable to a SQL injection attack. Do not insert values into queries with string concatenation.

Instead, use parameters.

const insertSQL = `
  INSERT INTO authors (id, author_name, author_slug) 
  VALUES ($1, $2, $3);
`;
await pool.query( insertSQL, [authorID, authorName, authorSlug] );

Postgres will handle the quoting for you. This is safer, more secure, and faster.

---

Note that an3cxZh8ZD3tdtqG4wuwPR is not a valid UUID. A UUID is a 128 bit integer often represented as a 32 character hex string.

Note that you also probably want to use autoincrementing primary keys instead of generating the ID yourself. For a UUID primary key, load the uuid-ossp package and use its UUID function as your default.

create extension "uuid-ossp";

create table authors (
  id uuid primary key default uuid_generate_v4(),

  -- There's no point in arbitrarily limiting the size of your text fields.
  -- They will only use as much space as they need.
  author_name text not null unique check (author_name <> ''),
  author_slug text not null unique check (author_slug <> '')
);

insert into authors (author_name, author_slug) 
values ('Alan Paton', 'alan-paton');

POSTGRESQL is showing ERROR: syntax error at or near DISTINCT

DISTINCT is applied to the output results and needs to follow SELECT, you can't call it on an individual column. GROUP BY can give you single column results if that's what you're looking for.

-- filter all results
SELECT DISTINCT film_id, rental_rate FROM film;

-- filter on rental_rate
SELECT film_id, rental_rate FROM film GROUP BY rental_rate;

-- syntax error
SELECT film_id, DISTINCT(rental_rate) FROM film;

syntax error at or near AS in creating trigger in postgresql

You can not write trigger in PostgreSQL in this way. Here its a 2 step process.

1. First create a Trigger function:

CREATE OR REPLACE FUNCTION updateAvailableQuantity()
RETURNS TRIGGER
AS
$$
BEGIN
    IF NEW.quantity > 0 THEN
    UPDATE products
    SET quantity_instock = quantity_instock - NEW.quantity WHERE products.barcode =  NEW.barcode;
    END IF;

RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. Then write the trigger on your table to call the function like below:

CREATE TRIGGER  trg_updateAvailableQuantity
AFTER INSERT ON sale_item 
FOR EACH ROW 
EXECUTE PROCEDURE updateAvailableQuantity();

PostgreSQL: syntax error at or near refcursor

You used the wrong language declaration. Your procedure is in plpgsql but you declared it as plain sql through language sql statement at the top.

Replacing

create or replace function divide(
  a integer,
  b integer
)
returns setof refcursor
language sql as

with

create or replace function divide(
  a integer,
  b integer
)
returns setof refcursor
language plpgsql as

Solves the problem.

Syntax Error when using a case when conditional

An alias like 2020Q4_revenue is an “identifier” in SQL, so it must conform with the rules:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable.

You can still use that alias name if you quote it with double quotes:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.)

---

Related Topics