Ошибка синтаксиса примерное положение record - TopOshibok.ru - решение и исправление самых разных ошибок

Пытаюсь загрузить данные в таблицу Postgres. Скрипт и данные находятся на локальном сервере Open Server, а бд на другом, удаленном серваке, к которому есть полный доступ (соединяюсь с базой через менеджер бд и все редактирую). Подключение к базе при запуске скрипта успешно происходит, но вот запрос INSERT INTO не срабатывает:

$pg = "INSERT INTO public.1253_data () VALUES " . implode(",", $temp_array);

Ошибка выдается от функции pg_query, пишет: Warning: pg_query(): Query failed: Ошибка синтаксиса (примерное положение: .1253_data).

Я запускаю функцию pg_result_query():

$result = pg_query($conn, $sql);


 if ($result) {
   echo "New record created successfully";
} else {
      $k = pg_get_result($result);
      echo 'Query failed1: ' . pg_result_error($k);
}
pg_close($conn);

В результате pg_result_error дает пустоту:

Судя по документации, pg_result_дает такое только тогда, когда ошибок никаких нет. Но тогда почему собственный дескриптор ошибки pg_query такой?

Только начинаю pg изучать, все перепробовал в поисках ошибки ( Эксперты, подскажите, в чем она может быть?

Источник

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

восстановить базу из дампа:

--
-- PostgreSQL database dump
--
-- Dumped from database version 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
-- Dumped by pg_dump version 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--
-- Name: attribute_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.attribute_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.attribute_id_seq OWNER TO bender;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: attribute; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.attribute (
attribute_id integer DEFAULT nextval('public.attribute_id_seq'::regclass) NOT NULL,
name character varying(30) NOT NULL,
attribute_type_id integer NOT NULL
);
ALTER TABLE public.attribute OWNER TO bender;
--
-- Name: attribute_type_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.attribute_type_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.attribute_type_id_seq OWNER TO bender;
--
-- Name: attribute_type; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.attribute_type (
attribute_type_id integer DEFAULT nextval('public.attribute_type_id_seq'::regclass) NOT NULL,
name character varying(50) NOT NULL
);
ALTER TABLE public.attribute_type OWNER TO bender;
--
-- Name: film_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.film_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.film_id_seq OWNER TO bender;
--
-- Name: film; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.film (
film_id integer DEFAULT nextval('public.film_id_seq'::regclass) NOT NULL,
name character varying(50) NOT NULL
);
ALTER TABLE public.film OWNER TO bender;
--
-- Name: film_attributes_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.film_attributes_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.film_attributes_id_seq OWNER TO bender;
--
-- Name: film_attributes; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.film_attributes (
film_attributes_id integer DEFAULT nextval('public.film_attributes_id_seq'::regclass) NOT NULL,
attribute_id integer NOT NULL,
film_id integer NOT NULL,
value_text character varying,
value_integer integer,
value_float double precision,
value_boolean boolean,
value_timestamp timestamp with time zone
);
ALTER TABLE public.film_attributes OWNER TO bender;
--
-- Name: film_attributes_values; Type: VIEW; Schema: public; Owner: bender
--
CREATE VIEW public.film_attributes_values AS
SELECT
NULL::character varying(50) AS name,
NULL::character varying(50) AS attribute_type,
NULL::character varying(30) AS attribute_name,
NULL::character varying AS attribute_value;
ALTER TABLE public.film_attributes_values OWNER TO bender;
--
-- Name: film_tasks; Type: VIEW; Schema: public; Owner: bender
--
CREATE VIEW public.film_tasks AS
SELECT
NULL::character varying(50) AS name,
NULL::character varying[] AS today_tasks,
NULL::character varying[] AS twenty_days_tasks;
ALTER TABLE public.film_tasks OWNER TO bender;
--
-- Data for Name: attribute; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.attribute (attribute_id, name, attribute_type_id) FROM stdin;
1	Рецензии	3
3	Премия Оскар	2
4	Премия Ника	2
5	Премия Золотой Глобус	2
10	Описание фильма	3
11	Длительность (мин.)	1
12	Длительность проката (дней)	1
2	Рейтинг	7
6	Премьера в мире	6
7	Премьера в России	6
8	Старт продажи билетов	6
9	Старт проката	6
13	Окончание проката	6
.
--
-- Data for Name: attribute_type; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.attribute_type (attribute_type_id, name) FROM stdin;
1	integer
2	boolean
3	text
4	date
5	numeric
6	timestamp
7	float
.
--
-- Data for Name: film; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.film (film_id, name) FROM stdin;
1	Spoiler-man: No Way
2	Matrix 4
.
--
-- Data for Name: film_attributes; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.film_attributes (film_attributes_id, attribute_id, film_id, value_text, value_integer, value_float, value_boolean, value_timestamp) FROM stdin;
1	1	1	Годный фильм, распинаюсь про сюжет, пишу про игру актеров, все круто	N	N	N	N
2	1	2	Джон Уик уже не тот, сестры Вачовски сбрендили, полная фигня	N	N	N	N
5	3	1	f	N	N	N	N
7	6	2	N	N	N	N	2021-12-10 00:00:00+03
9	7	2	N	N	N	N	2021-12-30 00:00:00+03
10	8	1	N	N	N	N	2021-12-10 00:00:00+03
11	8	2	N	N	N	N	2021-12-07 00:00:00+03
12	12	1	N	21	N	N	N
13	12	2	N	14	N	N	N
14	9	1	N	N	N	N	2021-12-15 00:00:00+03
15	9	2	N	N	N	N	2021-12-15 00:00:00+03
16	13	1	N	N	N	N	2022-01-04 00:00:00+03
17	13	2	N	N	N	N	2022-01-04 00:00:00+03
18	3	2	t	N	N	N	N
6	6	1	N	N	N	N	2021-12-15 00:00:00+03
8	7	1	N	N	N	N	2022-01-04 00:00:00+03
.
--
-- Name: attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.attribute_id_seq', 13, true);
--
-- Name: attribute_type_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.attribute_type_id_seq', 6, true);
--
-- Name: film_attributes_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.film_attributes_id_seq', 18, true);
--
-- Name: film_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.film_id_seq', 2, true);
--
-- Name: attribute attribute_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute
ADD CONSTRAINT attribute_pkey PRIMARY KEY (attribute_id);
--
-- Name: attribute_type attribute_type_name_key; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute_type
ADD CONSTRAINT attribute_type_name_key UNIQUE (name);
--
-- Name: attribute_type attribute_type_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute_type
ADD CONSTRAINT attribute_type_pkey PRIMARY KEY (attribute_type_id);
--
-- Name: attribute attribute_unq; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute
ADD CONSTRAINT attribute_unq UNIQUE (name);
--
-- Name: film_attributes film_attributes_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film_attributes
ADD CONSTRAINT film_attributes_pkey PRIMARY KEY (film_attributes_id);
--
-- Name: film film_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film
ADD CONSTRAINT film_pkey PRIMARY KEY (film_id);
--
-- Name: film film_unq; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film
ADD CONSTRAINT film_unq UNIQUE (name);
--
-- Name: attribute_index; Type: INDEX; Schema: public; Owner: bender
--
CREATE INDEX attribute_index ON public.attribute USING btree (name COLLATE "C.UTF-8" varchar_ops);
--
-- Name: film_index; Type: INDEX; Schema: public; Owner: bender
--
CREATE INDEX film_index ON public.film USING btree (name COLLATE "C.UTF-8");
--
-- Name: attribute attribute_type_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute
ADD CONSTRAINT attribute_type_fkey FOREIGN KEY (attribute_type_id) REFERENCES public.attribute_type(attribute_type_id) NOT VALID;
--
-- Name: film_attributes film_attribute_attribute_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film_attributes
ADD CONSTRAINT film_attribute_attribute_fkey FOREIGN KEY (attribute_id) REFERENCES public.attribute(attribute_id);
--
-- Name: film_attributes film_attribute_film_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film_attributes
ADD CONSTRAINT film_attribute_film_fkey FOREIGN KEY (film_id) REFERENCES public.film(film_id);
--
-- PostgreSQL database dump complete
--

ERROR: ОШИБКА:  ошибка синтаксиса (примерное положение: "1")
LINE 180: 1 Рецензии 3
^
SQL state: 42601
Character: 4115

Пользователь заполняет форму, где вводит значения в textBox-ы.

NpgsqlCommand com = new NpgsqlCommand("INSERT INTO 'Tip' (code_tip, 
name_tip) VALUES (@p1, @p2)", con);
com.Parameters.Add("code_tip", NpgsqlTypes.NpgsqlDbType.Bigint).Value = 
textBox1;
com.Parameters.Add("name_tip", NpgsqlTypes.NpgsqlDbType.Char, 40).Value = 
textBox2;
com.ExecuteNonQuery();

На этом моменте visual-studio выдает мне ошибку:

Npgsql.NpgsqlException: "ОШИБКА: 42601: ошибка синтаксиса (примерное положение: "'Tip'")"

Подскажите, что не так?

задан 21 сен 2018 в 11:03

INSERT INTO ‘Tip’

По синтаксису (и стандарту SQL) insert запроса после ключевого слова into должно идти имя таблицы. Вы указали строковой литерал. Парсер соответственно удивляется и отвечает, что вы написали непонятно что.

в одинарных кавычках 'Tip' — строковой литерал.
без кавычек Tip — имя объекта, принудительно приводимое парсером к нижнему регистру, т.е. tip
в двойных кавычках "Tip" — регистрозависимое имя объекта

Если у вас таблица именно Tip, то единственным корректным способом к ней обращаться будут двойные кавычки:

INSERT INTO "Tip" ...

ответ дан 21 сен 2018 в 11:56

МелкийМелкий

20.8k3 золотых знака26 серебряных знаков52 бронзовых знака

Содержание

PostgreSQL error 42601- How we fix it
What causes error 42601 in PostgreSQL?
How we fix the error?
Conclusion
PREVENT YOUR SERVER FROM CRASHING!
10 Comments

PostgreSQL error 42601- How we fix it

by Sijin George | Sep 12, 2019

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

But, this ended up in PostgreSQL error 42601. And he got the following error message,

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SELECT * FROM long_term_prediction_anomaly WHERE + “‘Timestamp’” + ‘”BETWEEN ‘” +
2019-12-05 09:10:00+ ‘”AND’” + 2019-12-06 09:10:00 + “‘;”)

Hello Joe,
Do you still get PostgreSQL errors? If you need help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).

У меня ошибка drop table exists “companiya”;

CREATE TABLE “companiya” (
“compania_id” int4 NOT NULL,
“fio vladelca” text NOT NULL,
“name” text NOT NULL,
“id_operator” int4 NOT NULL,
“id_uslugi” int4 NOT NULL,
“id_reklama” int4 NOT NULL,
“id_tex-specialist” int4 NOT NULL,
“id_filial” int4 NOT NULL,
CONSTRAINT “_copy_8” PRIMARY KEY (“compania_id”)
);

CREATE TABLE “filial” (
“id_filial” int4 NOT NULL,
“street” text NOT NULL,
“house” int4 NOT NULL,
“city” text NOT NULL,
CONSTRAINT “_copy_5” PRIMARY KEY (“id_filial”)
);

CREATE TABLE “login” (
“id_name” int4 NOT NULL,
“name” char(20) NOT NULL,
“pass” char(20) NOT NULL,
PRIMARY KEY (“id_name”)
);

CREATE TABLE “operator” (
“id_operator” int4 NOT NULL,
“obrabotka obrasheniya” int4 NOT NULL,
“konsultirovanie” text NOT NULL,
“grafick work” date NOT NULL,
CONSTRAINT “_copy_2” PRIMARY KEY (“id_operator”)
);

CREATE TABLE “polsovateli” (
“id_user” int4 NOT NULL,
“id_companiya” int4 NOT NULL,
“id_obrasheniya” int4 NOT NULL,
“id_oshibka” int4 NOT NULL,
CONSTRAINT “_copy_6” PRIMARY KEY (“id_user”)
);

CREATE TABLE “reklama” (
“id_reklama” int4 NOT NULL,
“tele-marketing” text NOT NULL,
“soc-seti” text NOT NULL,
“mobile” int4 NOT NULL,
CONSTRAINT “_copy_3” PRIMARY KEY (“id_reklama”)
);

CREATE TABLE “tex-specialist” (
“id_tex-specialist” int4 NOT NULL,
“grafik” date NOT NULL,
“zarplata” int4 NOT NULL,
“ispravlenie oshibok” int4 NOT NULL,
CONSTRAINT “_copy_7” PRIMARY KEY (“id_tex-specialist”)
);

CREATE TABLE “uslugi” (
“id_uslugi” int4 NOT NULL,
“vostanavlenia parola” int4 NOT NULL,
“poterya acaunta” int4 NOT NULL,
CONSTRAINT “_copy_4” PRIMARY KEY (“id_uslugi”)
);

ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_operator_1” FOREIGN KEY (“id_operator”) REFERENCES “operator” (“id_operator”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_uslugi_1” FOREIGN KEY (“id_uslugi”) REFERENCES “uslugi” (“id_uslugi”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_filial_1” FOREIGN KEY (“id_filial”) REFERENCES “filial” (“id_filial”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_reklama_1” FOREIGN KEY (“id_reklama”) REFERENCES “reklama” (“id_reklama”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_tex-specialist_1” FOREIGN KEY (“id_tex-specialist”) REFERENCES “tex-specialist” (“id_tex-specialist”);
ALTER TABLE “polsovateli” ADD CONSTRAINT “fk_polsovateli_companiya_1” FOREIGN KEY (“id_companiya”) REFERENCES “companiya” (“compania_id”);

ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: “”companiya””)
LINE 1: drop table exists “companiya”;
^

Источник

Здравствуйте! Довольно распространенный вопрос, находил решения, но все же компилятор выводит ошибку. Как-то неправильно задаю первичный ключ.

Использую Postgresql

Java

@Entity
@Table(name="users")
public class Users{
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(columnDefinition="serial")
    private Long id;
    @Column(name="login")
    private String login;
    @Column(name="password")
    private String password;
}

SQL

CREATE TABLE IF NOT EXISTS users (
  id  SERIAL PRIMARY KEY NOT NULL,
  login VARCHAR(20) UNIQUE NOT NULL,
  password VARCHAR(20) NOT NULL
);

Ошибка:
org.hibernate.tool.schema.spi.CommandAcceptanceExc eption: Error executing DDL via JDBC Statement
Caused by: org.postgresql.util.PSQLException: ОШИБКА: ошибка синтаксиса (примерное положение: «auto_increment»)

Добавлено через 27 минут
SQL-запрос пишу в консоли postgresql. Таблица без записей.
Также в проекте имеется обычный конфигурационный файл для JPA. Думаю, что роли он здесь не играет

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

@YohDeadfall — I understand that part about it, but this is not script that I am creating or even code that I am creating. This is all created under the hood by Npsql/EntityFramework. My quick guess is that I am extending my DbContext from IdentityDbContext<IdentityUser> which wants to create all of the tables for roles, users, claims, etc. If I change this to just extend from DbContext, then everything works as advertised.

Below is the script that EF is trying to use created from dotnet ef migrations script — please be aware that I have removed my custom part of the script for brevity.

You can see there are two specific calls that are being made where [NormalizedName] and [NormalizedUserName] are being used.

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
"MigrationId" varchar(150) NOT NULL,
"ProductVersion" varchar(32) NOT NULL,
CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);
CREATE TABLE "AspNetRoles" (
"Id" text NOT NULL,
"ConcurrencyStamp" text NULL,
"Name" varchar(256) NULL,
"NormalizedName" varchar(256) NULL,
CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUsers" (
"Id" text NOT NULL,
"AccessFailedCount" int4 NOT NULL,
"ConcurrencyStamp" text NULL,
"Email" varchar(256) NULL,
"EmailConfirmed" bool NOT NULL,
"LockoutEnabled" bool NOT NULL,
"LockoutEnd" timestamptz NULL,
"NormalizedEmail" varchar(256) NULL,
"NormalizedUserName" varchar(256) NULL,
"PasswordHash" text NULL,
"PhoneNumber" text NULL,
"PhoneNumberConfirmed" bool NOT NULL,
"SecurityStamp" text NULL,
"TwoFactorEnabled" bool NOT NULL,
"UserName" varchar(256) NULL,
CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetRoleClaims" (
"Id" int4 NOT NULL,
"ClaimType" text NULL,
"ClaimValue" text NULL,
"RoleId" text NOT NULL,
CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);
CREATE TABLE "AspNetUserClaims" (
"Id" int4 NOT NULL,
"ClaimType" text NULL,
"ClaimValue" text NULL,
"UserId" text NOT NULL,
CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id"),
CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);
CREATE TABLE "AspNetUserLogins" (
"LoginProvider" text NOT NULL,
"ProviderKey" text NOT NULL,
"ProviderDisplayName" text NULL,
"UserId" text NOT NULL,
CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);
CREATE TABLE "AspNetUserRoles" (
"UserId" text NOT NULL,
"RoleId" text NOT NULL,
CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);
CREATE TABLE "AspNetUserTokens" (
"UserId" text NOT NULL,
"LoginProvider" text NOT NULL,
"Name" text NOT NULL,
"Value" text NULL,
CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);
CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");
CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;
CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");
CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");
CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");
CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");
CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName") WHERE [NormalizedUserName] IS NOT NULL;
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20180514204732_initial', '2.0.3-rtm-10026');

Перейти к содержимому

При попытке восстановления дампа под Windopws 7 столкнулся с ошибкой:

COPY carriers (business_entity_id, name) FROM stdin;
8	Arriva
50000	ASEAG
.

[Err] ОШИБКА: ошибка синтаксиса (примерное положение: «8»)
LINE 2: 8 Arriva
^

Мы получаем простую синтаксическую ошибку, потому что Postgres получает данные как код SQL.

Пример ниже не поддерживается утилитой pgAdmin.

COPY tablel FROM STDIN;

Как сделать резервную копию базы в Postgress?

pg_dump -U user database > fileName.sql

где:

pg_dump — это программа для создания резервных копий базы данных Postgres Pro;
postgres — имя пользователя БД (совпадает с именем базы данных);
transactions — имя базы к которой есть доступ у нашего пользователя postgres;
transactions.sql — имя создаваемого файла дампа;
hostname — имя сервера БД, это pg.sweb.ru;
format — формат дампа (может быть одной из трех букв: ‘с’ (custom — архив .tar.gz), ‘t’ (tar — tar-файл), ‘p’ (plain — текстовый файл). В команде букву надо указывать без кавычек.);
dbname — имя базы данных.

pg_dump -U postgres transactions > transactions.sql

Как сделать restore в Postgress?

Тут все несколько запутанней, поэтому выкладываю все 3 варианта начну с того который решил мою проблему:

psql -U postgres -d belgianbeers -a -f beers.sql

pg_restore -h localhost -U postgres -F t -d transactions «D:/transactions.sql»

pg_restore —host localhost —port 5432 —username postgres —dbname transactions —clean —verbose «D:transactions.sql»

Не забывайте если указываете полный путь брать его в двойные кавычки!!!

when I am using this command to update table in PostgreSQL 13:

UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/'
limit 10

but shows this error:

SQL Error [42601]: ERROR: syntax error at or near "limit"
Position: 111

why would this error happen and what should I do to fix it?

asked Jul 22, 2021 at 14:09

LIMIT isn’t a valid keyword in an UPDATE statement according to the official PostgreSQL documentation:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

^{Reference: UPDATE (PostgreSQL Documentation )}

Solution

Remove LIMIT 10 from your statement.

answered Jul 22, 2021 at 14:32

John K. N.John K. N.

15.7k10 gold badges45 silver badges100 bronze badges

You could make something like this

But a Limit without an ORDER BY makes no sense, so you must choose one that gets you the correct 10 rows

UPDATE rss_sub_source t1
SET t1.sub_url = SUBSTRING(t1.sub_url, 1, CHAR_LENGTH(t1.sub_url) - 1) 
FROM (SELECT id FROM rss_sub_source WHERE sub_url LIKE '%/' ORDER BY id LIMIT 10) t2 
WHERE t2.id = t1.id

answered Jul 22, 2021 at 14:51

nbknbk

7,7295 gold badges12 silver badges27 bronze badges

when I am using this command to update table in PostgreSQL 13:

UPDATE rss_sub_source 
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1) 
WHERE sub_url LIKE '%/'
limit 10

but shows this error:

SQL Error [42601]: ERROR: syntax error at or near "limit"
Position: 111

why would this error happen and what should I do to fix it?

asked Jul 22, 2021 at 14:09

LIMIT isn’t a valid keyword in an UPDATE statement according to the official PostgreSQL documentation:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

^{Reference: UPDATE (PostgreSQL Documentation )}

Solution

Remove LIMIT 10 from your statement.

answered Jul 22, 2021 at 14:32

John K. N.John K. N.

15.7k10 gold badges45 silver badges100 bronze badges

You could make something like this

But a Limit without an ORDER BY makes no sense, so you must choose one that gets you the correct 10 rows

UPDATE rss_sub_source t1
SET t1.sub_url = SUBSTRING(t1.sub_url, 1, CHAR_LENGTH(t1.sub_url) - 1) 
FROM (SELECT id FROM rss_sub_source WHERE sub_url LIKE '%/' ORDER BY id LIMIT 10) t2 
WHERE t2.id = t1.id

answered Jul 22, 2021 at 14:51

nbknbk

7,7295 gold badges12 silver badges27 bronze badges

Источник

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

So, you’re creating a custom SQL query to perform a task in the database. After putting the code together and running it in PHPmyAdmin it responds with a 1064 error. It may look similar to this:

The 1064 error displays any time you have an issue with your SQL syntax, and is often due to using reserved words, missing data in the database, or mistyped/obsolete commands. So follow along and learn more about what the 1064 error is, some likely causes, and general troubleshooting steps.

Note: Since syntax errors can be hard to locate in long queries, the following online tools can often save time by checking your code and locating issues:

PiliApp MySQL Syntax Check
EverSQL SQL Query Syntax Check & Validator

Causes for the 1064 error

Reserved Words
Missing Data
Mistyped Commands
Obsolete Commands

This may seem cryptic since it is a general error pointing to a syntax issue in the SQL Query statement. Since the 1064 error can have multiple causes, we will go over the most common things that will result in this error and show you how to fix them. Follow along so you can get your SQL queries updated and running successfully.

Using Reserved Words

Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or to perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.

CREATE TABLE alter (first_day DATE, last_day DATE);

How to fix it:

Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.

CREATE TABLE `alter` (first_day DATE, last_day DATE);

Missing Data

Sometimes data can be missing from the database. This causes issues when the data is required for a query to complete. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like this:

SELECT * from students WHERE studentID = $id

If the $id is never properly filled in the code, the query would look like this to the server:

SELECT * from students WHERE studentID =

Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error.

How to fix it:

Hopefully, your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.

Mistyping of Commands

One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.

UDPATE table1 SET id = 0;

How to fix it:

Be sure to check your commands prior to running them and ensure they are all spelled correctly.

Below is the syntax for the correct query statement.

UPDATE table1 SET id = 0;

Obsolete Commands

Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE‘ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE‘ command has been replaced with the ‘ENGINE‘ command. Below is an example of the old version:

CREATE TABLE t (i INT) TYPE = INNODB;

This should be replaced with the new command as below:

CREATE TABLE t (i INT) ENGINE = INNODB;

For developers or sysadmins experienced with the command line, get High-Availability and Root Access for your application, service, and websites with Cloud VPS Hosting.

Error 1064 Summary

As you can see there is more than one cause for the 1064 error within MySQL code. Now, you know how to correct the issues with your SQL Syntax, so your query can run successfully. This list will be updated as more specific instances are reported.

Источник

Алексей

@falcon_digit

Начинающий в AI

Делаю задачи по курсу SQL на Stepik’е, параллельно их же делаю в PostgreSQL.
Такой запрос UPDATE с вложенным SELECT’ом в PostgreSQL выдаёт ошибку синтаксиса

UPDATE fine,
(SELECT  name, number_plate, violation
FROM fine
GROUP BY name, number_plate, violation
HAVING count(*) > 1
    ) query_in
SET sum_fine = sum_fine * 2 
WHERE fine.name = query_in.name AND fine.number_plate = query_in.number_plate AND fine.violation = query_in.violation AND fine.date_payment IS NULL;

ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: «,»)
LINE 1: UPDATE fine,
На Stepik’е этот код принимается без ошибки.
Т.е. в PostgreSQL так нельзя делать? Или это как то лечится?
P.S. В SQLiteStudio тоже ошибка синтаксиса.

Вопрос задан

04 дек. 2022
241 просмотр

Пригласить эксперта

А чего спрашивать-то? Прочитайте документацию postgresql про update — есть там подобный синтаксис или нет? Уже понятно, что нет. Возможно, Вам подойдёт with?
А что касается Степиков и подобных, то в курсах по SQL обязательно должна быть указана версия/стандарт sql на которых курс основан, чтобы подобные казусы выявлять.

Это скорее всего неправильно.

SELECT  name, number_plate, violation
FROM fine
GROUP BY name, number_plate, violation
HAVING count(*) > 1

если используется GROUP BY то в выражении SELECT должна стоять функция агрегации (count в данном случае)

Показать ещё
Загружается…

22 сент. 2023, в 21:27

300 руб./за проект

22 сент. 2023, в 19:59

5000 руб./за проект

22 сент. 2023, в 18:59

30000 руб./за проект

Минуточку внимания

Источник

What causes error 42601 in PostgreSQL?

How we fix the error?

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

PostgreSQL error 42601- How we fix it

What causes error 42601 in PostgreSQL?

How we fix the error?

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Как сделать резервную копию базы в Postgress?

Как сделать restore в Postgress?

Solution

Solution

What causes error 42601 in PostgreSQL?

How we fix the error?

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Causes for the 1064 error

Using Reserved Words

Missing Data

Mistyping of Commands

Obsolete Commands

Error 1064 Summary

Минуточку внимания

Интересное по теме: