Ошибка синтаксиса примерное положение varchar

вот такой код:
create procedure spisok_rabot (num INT, date1 DATE, date2 DATE)
language sql
as $$;
declare
work_type VARCHAR(20);
count int;
set work_type= (select work_type, count(work_type)
from works where number=num and date_in between date1 and date2
group by work_type);
set count=(select count(work_type)
from works where number=num and date_in between date1 and date2
group by work_type);
$$;
вот такая ошибка:
ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: «VARCHAR»)
LINE 5: work_type VARCHAR(20);
во видимому, я не правильно объявляю тип переменной, но иного способа я не нашел. Помогите, пожалуйста

• 
  Вопрос задан

  более двух лет назад
  

• 745 просмотров

In this article, we will see the solution for ERROR: syntax error at or near “VARCHAR”.

Reolution:

The above error when I have tried to change the column length of varchar(50) to varchar(80) with below command.

alter table emp11
alter column job varchar(80) 

Solution: We have to provide TYPE in front of data type like below.

alter table emp11
alter column job type VARCHAR(80);

Error not occurred with the above command.

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,

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,

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”;
^

Источник

[Error] 42601: syntax error at or near «SELECT» on pgsql 9.2.5 #813

When trying to remove or update an object from DB i get this exception on pgsql 9.2.5. On 9.4 it works fine.

Npgsql: npgsql-3.1-alpha0058
EF: 6.1.3
posgresql: postgresql92-9.2.5-1PGDG.rhel6.x86_64

[Error] 42601: syntax error at or near «SELECT» — at Npgsql.NpgsqlConnector.DoReadSingleMessage (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) [0x00000] in :0
at Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) [0x00000] in :0

2015-10-07 19:13:17.082 CEST [9318]: LOG: statement: DISCARD ALL
2015-10-07 19:13:17.083 CEST [9318]: ERROR: syntax error at or near «SELECT» at character 4857
2015-10-07 19:13:17.083 CEST [9318]: STATEMENT: SELECT «Project4».»Id1″ AS «Id», «Project4″.»Id» AS «Id1», «Project4″.»Name», «Project4″.»DatastoreRef», «Project4″.»DatastoreName», «Project4″.»TestNetworkName», «Project4″.»TestNetworkRef», «Project4″.»TestNetworkVLanId», «Project4″.»RecoveryNetworkName», «Project4″.»RecoveryNetworkRef», «Project4″.»RecoveryNetworkVLanId», «Project4″.»ResourcePoolName», «Project4″.»ResourcePoolRef», «Project4″.»ComputeResourceName», «Project4″.»ComputeResourceRef», «Project4″.»FolderName», «Project4″.»FolderRef», «Project4″.»PowerOnTimeoutMin», «Project4″.»DestinationPath», «Project4″.»Status», «Project4″.»MaxRecoveryPoint», «Project4″.»ServerId», «Project4″.»Enabled», «Project4″.»StartDateTime», «Project4″.»DaysOfWeek», «Project4″.»Interval», «Project4».»C1″, «Project4″.»IntervalUnit», «Project4″.»EndDateTime», «Project4″.»DateTimeFormat_DateFormat», «Project4″.»DateTimeFormat_TimeFormat», «Project4″.»DateTimeFormat_TimeZoneId», «Project4».»C35″ AS «C2», «Project4».»C3″, «Project4».»C4″, «Project4».»C5″, «Project4».»C6″, «Project4».»C7″, «Project4».»C8″, «Project4».»C9″, «Project4».»C10″, «Project4».»C11″, «Project4».»C12″, «Project4».»C13″, «Project4».»C14″, «Project4».»C2″ AS «C15», «Project4».»C15″ AS «C16», «Project4».»C16″ AS «C17», «Project4».»C17″ AS «C18», «Project4».»C18″ AS «C19», «Project4».»C19″ AS «C20», «Project4».»C20″ AS «C21», «Project4».»C21″ AS «C22», «Project4».»C22″ AS «C23», «Project4».»C23″ AS «C24», «Project4».»C24″ AS «C25», «Project4».»C25″ AS «C26», «Project4».»C26″ AS «C27», «Project4».»C27″ AS «C28», «Project4».»C28″ AS «C29», «Project4».»C29″ AS «C30», «Project4».»C30″ AS «C31», «Project4».»C31″ AS «C32», «Project4».»C32″ AS «C33», «Project4».»C33″ AS «C34», «Project4».»C34″ AS «C35» FROM (SELECT «Alias1″.»Id», «Alias1″.»Name», «Alias1″.»DatastoreRef», «Alias1″.»DatastoreName», «Alias1″.»TestNetworkName», «Alias1″.»TestNetworkRef», «Alias1″.»TestNetworkVLanId», «Alias1″.»RecoveryNetworkName», «Alias1″.»RecoveryNetworkRef», «Alias1″.»RecoveryNetworkVLanId», «Alias1″.»ResourcePoolName», «Alias1″.»ResourcePoolRef», «Alias1″.»ComputeResourceName», «Alias1″.»ComputeResourceRef», «Alias1″.»FolderName», «Alias1″.»FolderRef», «Alias1″.»PowerOnTimeoutMin», «Alias1″.»DestinationPath», «Alias1″.»Status», «Alias1″.»MaxRecoveryPoint», «Alias1″.»ServerId», «Alias1».»Id1″, «Alias1″.»Enabled», «Alias1″.»StartDateTime», «Alias1″.»DaysOfWeek», «Alias1″.»Interval», «Alias1″.»IntervalUnit», «Alias1″.»EndDateTime», «Alias1″.»DateTimeFormat_DateFormat», «Alias1″.»DateTimeFormat_TimeFormat», «Alias1″.»DateTimeFormat_TimeZoneId», «Alias1».»C1″, «UnionAll1».»C1″ AS «C2», «UnionAll1″.»Id» AS «C3», «UnionAll1».»Id1″ AS «C4», «UnionAll1″.»InstanceId» AS «C5», «UnionAll1″.»Name» AS «C6», «UnionAll1″.»Ref» AS «C7», «UnionAll1″.»IsPhysical» AS «C8», «UnionAll1″.»BackupId» AS «C9», «UnionAll1″.»JobId» AS «C10», «UnionAll1″.»OSUsername» AS «C11», «UnionAll1″.»OSUserPwd» AS «C12», «UnionAll1″.»OSType» AS «C13», «UnionAll1″.»SnapshotRef» AS «C14», «UnionAll1».»Id2″ AS «C15», «UnionAll1».»Id3″ AS «C16», «UnionAll1″.»MacAdresss» AS «C17», «UnionAll1″.»NicLabel» AS «C18», «UnionAll1″.»NetworkConfigId» AS «C19», «UnionAll1″.»VirtualMachine_Id» AS «C20», «UnionAll1».»C2″ AS «C21», «UnionAll1».»C3″ AS «C22», «UnionAll1».»C4″ AS «C23», «UnionAll1».»C5″ AS «C24», «UnionAll1».»C6″ AS «C25», «UnionAll1».»C7″ AS «C26», «UnionAll1».»C8″ AS «C27», «UnionAll1».»C9″ AS «C28», «UnionAll1».»C10″ AS «C29», «UnionAll1».»C11″ AS «C30», «UnionAll1».»C12″ AS «C31», «UnionAll1».»C13″ AS «C32», «UnionAll1».»C14″ AS «C33», «UnionAll1».»C15″ AS «C34», CASE WHEN («UnionAll1″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C35» FROM (SELECT «Extent1″.»Id», «Extent1″.»Name», «Extent1″.»DatastoreRef», «Extent1″.»DatastoreName», «Extent1″.»TestNetworkName», «Extent1″.»TestNetworkRef», «Extent1″.»TestNetworkVLanId», «Extent1″.»RecoveryNetworkName», «Extent1″.»RecoveryNetworkRef», «Extent1″.»RecoveryNetworkVLanId», «Extent1″.»ResourcePoolName», «Extent1″.»ResourcePoolRef», «Extent1″.»ComputeResourceName», «Extent1″.»ComputeResourceRef», «Extent1″.»FolderName», «Extent1″.»FolderRef», «Extent1″.»PowerOnTimeoutMin», «Extent1″.»DestinationPath», «Extent1″.»Status», «Extent1″.»MaxRecoveryPoint», «Extent1″.»ServerId», «Extent2″.»Id» AS «Id1», «Extent2″.»Enabled», «Extent2″.»StartDateTime», «Extent2″.»DaysOfWeek», «Extent2″.»Interval», «Extent2″.»IntervalUnit», «Extent2″.»EndDateTime», «Extent2″.»DateTimeFormat_DateFormat», «Extent2″.»DateTimeFormat_TimeFormat», «Extent2″.»DateTimeFormat_TimeZoneId», CASE WHEN («Extent2″.»Id» IS NULL) THEN (CAST (NULL AS int2)) ELSE (CAST («Extent2″.»IntervalValue» AS int2)) END AS «C1» FROM «public».»jobs» AS «Extent1» LEFT OUTER JOIN «public».»schedules» AS «Extent2» ON «Extent1″.»Id» = «Extent2″.»Id» WHERE «Extent1″.»Id» = $1 LIMIT 1) AS «Alias1» LEFT OUTER JOIN LATERAL (SELECT «UnionAll1».»C1″, «UnionAll1″.»Id», «UnionAll1».»Id1″, «UnionAll1″.»InstanceId», «UnionAll1″.»Name», «UnionAll1″.»Ref», «UnionAll1″.»IsPhysical», «UnionAll1″.»BackupId», «UnionAll1″.»JobId», «UnionAll1″.»OSUsername», «UnionAll1″.»OSUserPwd», «UnionAll1″.»OSType», «UnionAll1″.»SnapshotRef», «UnionAll1».»Id2″, «UnionAll1».»Id3″, «UnionAll1″.»MacAdresss», «UnionAll1″.»NicLabel», «UnionAll1″.»NetworkConfigId», «UnionAll1″.»VirtualMachine_Id», «UnionAll1».»C2″, «UnionAll1».»C3″, «UnionAll1».»C4″, «UnionAll1».»C5″, «UnionAll1».»C6″, «UnionAll1».»C7″, «UnionAll1».»C8″, «UnionAll1».»C9″, «UnionAll1».»C10″, «UnionAll1».»C11″, «UnionAll1».»C12″, «UnionAll1».»C13″, «UnionAll1».»C14″, «UnionAll1».»C15″ FROM ((SELECT CASE WHEN («Extent4″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C1», «Extent3″.»Id», «Extent3″.»Id» AS «Id1», «Extent3″.»InstanceId», «Extent3″.»Name», «Extent3″.»Ref», «Extent3″.»IsPhysical», «Extent3″.»BackupId», «Extent3″.»JobId», «Extent3″.»OSUsername», «Extent3″.»OSUserPwd», «Extent3″.»OSType», «Extent3″.»SnapshotRef», «Extent4″.»Id» AS «Id2», «Extent4″.»Id» AS «Id3», «Extent4″.»MacAdresss», «Extent4″.»NicLabel», «Extent4″.»NetworkConfigId», «Extent4″.»VirtualMachine_Id», CAST (NULL AS int4) AS «C2», CAST (NULL AS int4) AS «C3», CAST (NULL AS text) AS «C4», CAST (NULL AS text) AS «C5», CAST (NULL AS bool) AS «C6», CAST (NULL AS int4) AS «C7», CAST (NULL AS int4) AS «C8», CAST (NULL AS int4) AS «C9», CAST (NULL AS int4) AS «C10», CAST (NULL AS int4) AS «C11», CAST (NULL AS int4) AS «C12», CAST (NULL AS text) AS «C13», CAST (NULL AS text) AS «C14», CAST (NULL AS int4) AS «C15» FROM «public».»vms» AS «Extent3» LEFT OUTER JOIN «public».»vm_net» AS «Extent4» ON «Extent3″.»Id» = «Extent4″.»VirtualMachine_Id» WHERE «Alias1″.»Id» = «Extent3″.»JobId») UNION ALL (SELECT 2 AS «C1», «Extent5″.»Id», «Extent5″.»Id» AS «Id1», «Extent5″.»InstanceId», «Extent5″.»Name», «Extent5″.»Ref», «Extent5″.»IsPhysical», «Extent5″.»BackupId», «Extent5″.»JobId», «Extent5″.»OSUsername», «Extent5″.»OSUserPwd», «Extent5″.»OSType», «Extent5″.»SnapshotRef», CAST (NULL AS int4) AS «C2», CAST (NULL AS int4) AS «C3», CAST (NULL AS text) AS «C4», CAST (NULL AS text) AS «C5», CAST (NULL AS int4) AS «C6», CAST (NULL AS int4) AS «C7», «Extent6″.»Id» AS «Id2», «Extent6″.»Id» AS «Id3», «Extent6″.»Name» AS «Name1», «Extent6″.»Command», «Extent6″.»IsCustom», «Extent6″.»TimeoutMin», «Extent6″.»Priority», «Extent6″.»VirtualMachine_Id», CASE WHEN («Extent7″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C8», «Extent7″.»Id» AS «Id4», «Extent7″.»Id» AS «Id5», «Extent7″.»Name» AS «Name2», «Extent7″.»Value», «Extent7″.»VirtualMachineApplicationTest_Id» FROM «public».»vms» AS «Extent5» INNER JOIN «public».»vm_test» AS «Extent6» LEFT OUTER JOIN «public».»vm_testparam» AS «Extent7» ON «Extent6″.»Id» = «Extent7″.»VirtualMachineApplicationTest_Id» ON «Extent5″.»Id» = «Extent6″.»VirtualMachine_Id» WHERE «Alias1″.»Id» = «Extent5″.»JobId»)) AS «UnionAll1») AS «UnionAll1» ON TRUE) AS «Project4» ORDER BY «Project4″.»Id1″ ASC ,»Project4″.»Id» ASC ,»Project4″.»C35″ ASC ,»Project4″.»C4″ ASC ,»Project4″.»C2″ ASC ,»Project4″.»C22″ ASC ,»Project4″.»C29″ ASC
2015-10-07 19:13:18.221 CEST [9318]: LOG: statement: DISCARD ALL
2015-10-07 19:13:18.221 CEST [9318]: ERROR: syntax error at or near «SELECT» at character 4857

The text was updated successfully, but these errors were encountered:

Источник

ERROR Database error 42601: syntax error at or near «unsigned» #1133

part of dump from mysql (Joomla database):

The text was updated successfully, but these errors were encountered:

Hi @iamnumlock ; can you give us the result of the MySQL catalog query that pgloader uses to determine the data types on the source database?

Meanwhile you should be able to install a per-column user-defined casting rule so that your load can make progress.

Hi @iamnumlock ; can you give us the result of the MySQL catalog query that pgloader uses to determine the data types on the source database?

Meanwhile you should be able to install a per-column user-defined casting rule so that your load can make progress.

LOCK TABLES avh2d_ak_profiles WRITE;
/*!40000 ALTER TABLE avh2d_ak_profiles DISABLE KEYS /;
INSERT INTO avh2d_ak_profiles VALUES (1,’Default Backup Profile’,’###AES128###zDDOdvi8wcbpNm/Km0/qJJDhCWdtINqtB+AW56AKzg5BPObtWoUdWfEG9tv4P/Hxp19PrpK7EhP4GgOloKdxUWxVJSKO+34GsQx2i6z3bXBXqOm9WtdNHlr8e/zoTrCGSrzYmT/hoM1qTnR/BmZEFzjxOZlr/H
VfMDZm8Qh0TfA05zb4JHINGFfc5Yl6vDRrZiMrVGMBqn4DnUDYhnURGXduuS2dthRs09TABGPX47c1ulYfDRSvIzremsYrXBUuMk96mMXPI714fiRHEcfB2saTRIh+Dxfr7KVRZZQJzlpL3mxv43/sVuIg5De8x1ntw4banN43mzIHOfhglYTvP1jJTxYQnxqlqrywbUlgyqPOYun/oESdvYt+Py69d+fQ8QetHXzjdO1F
rqP1XiFanBOYqFFh1/gfApERVE1XRPyjFXyXTLfxN/fHy+nfd8FLqBDdqK0W5elbz3eaKF88qcxgOEYOFB7biXYmGoKftvarubyGnskTkdtfZWmhkiw8NsFMaYb2PTvh6kuoOiGYuMkEY26FRgR4Pn8UJjS5X5oDgyLOKBFAhxTZeGNwybhOQ3Ln3jHVSRNc9aRo+vVui/c8PU799+NlDMpAaOq41owmJRigKMOn/uHZ+b
aikYcPlviR1b4fa7nr24tnTkLNnmW6y7pG03MnWhsT83+y7j0zzsed/MjPRgSa4yipYQoqPOW/mo2kBp2zxGSMiXP2CsoKT0zfGqesMoS30QYWBKO9KHIF2qaPuvWrId7+bzlXDTSOhW3ZuarQD7AyRQHR3M7HL04u/1nu562CnoSkoukoVTaIYEQzdx3Qdrby9g1uG08RC0Q167VO6j4A/+D6D2LEg+BymBKVsvId3eOw
Oh3gn9/uEuiAHdzEjF/1ewbdbtJyNqrAiSPKdAER74f3m/Mx34SayS7PPDIZXgXvKuoamzzO3JUr9gz9QRt78BAZf9xWnLP3SMnAQ04vxe6PzOwaOIM4qSwtOnUBw0UHgpV4lsWwtlZdQxVI7gd/gf/sMf8uTODst7oLDS3UDzBtGIZUGGxGBUhbiqIRlLz1DiRoBYdthS9orj1vfQLqAIGilN1UfeyzVgPs4lj82+nTkC
3sae8y1m7ZX8jH9bfW5OGIMTXWjEytPJBScXM1B4DlHN3i3xNUYqQ9921qOWyxU+MNxRjmJex5+p7cS1lmp/T0xkAAUl/7desUbVIw8O+kojymvlRk4XXIx/+T48pjp2oB7kT3+J0kr+SBWKi8E78fiyrnaFnQuLWjf0HMtkct6/zpd/iYLqjlCHLbiWGSytUrk5mFeS32se/Ty+ZpZKwCv0mhaM+YtEgjKvcIILHfclRC
ca6LGSrbGaMgAux3O6jM3oM3tj8egjPVFkvZ0wl0hcGpOXl9rqYOxeHVNtdbHHWpzMmUz5sBR9v3FSYHfTRngM4WFENWMKcyMDMCvZQk8/A5mdi0Fq5vKv6+MU1/RkzRakmO737a7+QgLTXPMQaJV73QLsGi1kdlyvmvkrqrot3BjvPfm1TFOdH46SS3+G3iWhdWMjrm2bw4gG9C5RpjZUT3W0HjvfnpO8KMNUVFzY1jS/
h6A7iGJ28/UkC1l3WHWLvxjigPWFF5n+WIPz1XJMETtN9KgQ50PGGpazAmpzmCS7CMdWL9+mipeGCfPx3csnH6aPe41Qhc61j0uLsVgVgwewPsoJKC7nUgjQsk23pDfc9GqJLUq3d6TjwcM9knl/MCAh3fe9tAehWOYdg6mfqqRQLGSH/Q2Gd2j76mqAirzayWCNviTZOj2jif0KwaiOEv/0K014qJXPD0FmXjbuVKUaEQ
1DWD7TeY65uGMMOOIRI2o8ncMM9I6pXSupyvi98oLTCgjrnDLrqqAEzCVhGj4listBr1rn/oAD4ApZFvS2jTSGK0c90OSqVpIK5o2Ir5VBIbHXTfsBBa5yJzZNgJ98wAwO91+QeFD3k8B+azijwGzXhdyDT0LGgjVaat8s62z0pdHJEvV1Y5gmNl6EvjASnqX98go31TxqBv1CS1tacZ8MVJBxEqYhUeikHqmIDWy39j9J
86xHYDYO9Y2sUrxkkMzXnF8nMx1WfNQ4lMcyKrVaExVtZ1MZCRX+yHHzfaiVO3LEoQelRQiCQVGXxnpaXT4bmYWfA5FcvH5nCVZ7nL/nH3rDmBGhTWOzVxjLwdk3GyU/9ohYY7JTy0HTnKlGTjLPbsnR6Z5YR9VMdAB+iGOkZ8AhVMOtwnXJD9/6kW04l7YvIlihBDO7nWENvnU4kdhtOyGnEH5i7hwUt9QvQBxfZKwDpH
Oax1iPRtx+otg2Ho9rt2B8vhdO7qP8yAp4eM+0zdkrkmfeeZ5xuoilCU0v0RpNqOAkyw9Cx4CanouEY8P7ce+vKf5coxajWzv/QERA8TsUS354SZoHrLdwIta406SZNq2sJk4ZMJsoNfMkJ0FvjPLhpNuXPXd8+/7egKI5YIuwj8nMxm28uWhGed0BYzqK8llo0UxppUyKseqmyx8zrgnOqp9Ktt8u4O68tuNMHftvFGMT
kqvuHNNHEgUqBpZCuYMTXsvW4BgOp01GivLuAVH8OBzV+qLFgcXihIryA74o7noIeaTnTA8IM5IkNCw3El9sfgIRY4pK7yYu4ouO1bYR3aZCsgyE4JIUHhsnbJhqjZeRm5vTCjIrf919s/96wdpbdVeWpjsGrKcDs+I/atleKPbaiFLjFtdnymKWB3s7m5ChD+8Q7QET9+fZdHv2vt7HJsf3b0HdzYTr15WykEKEvxFkqN
9zBqpylsZX1aMx1/seJAeH3EtTc1tcJIiDGwOc1rTgvLzs2Vmx+V4ayBiTDHTZ0NEaH+KLXVfBueeoBoFQPbihGIViVe7cjbj5j9y+xkRhdcBZdiT9t++HIjVGWbz1ZXsq9/cLfZWJ4Ocbf2plB0eFHvhLApzQwXBCOXgjVrDZS9b8w4CsniU5KHUH0LW5YVyEYCIDyaZJF0h6cXcJvstUr1VNraDCwPbWMjchJX1rnfgY
gfzBg1LuxIHH+h28Ae0tGZtDnCqzU90YSGeok3GmRwVpudlX/Z+stRMVQrfngw2mpVMaNf2bPFfBUTXkKGElQqrXh+vcdDpjcZIgvdrCaqoMA+b4ID9MqkgAtJAjmS6c6xQ8aLm/1LU/89j6X4Qs/M7yJGY7zkN59YRWpYgc+5tkaKMctR7SxZP0iIid7kX1l+S2vqA03jIfsxio8+F9Z4Q46MKTry8UlT8oE6XHhTXqC5
ce4hVF/aZwda01fOJq6lYmbZS4l3DCXdeso+Hf1155VYYqLY8MgTS00E2mkRjo+eV9kozm2Z1AOsU0DW1bVH5MOkBIhvyN+8XnFFa6bsN4W8TFUWlHQxkZXLWr0O0LH3Mq6qSVzYPIfd4d4cfapX7Lx6ZLQ/QgrN15eWromDP+xPwKyG/7FaKEL2wLnF/MRxdpGQ570//E8VV5UmPldISWwgk2Y2gmDwYycsHpWdyjD5fN
p2TBNPVYXTXCqFUjIisC5ZcUGDPD1Vyk+WwTZnYhbL/PQOuUXGGOGmQ2I55Jkgj+zQXZKYq5IndV39+ZwdYABXpK22u/4dPlB6MydVUUwM2ATy3EJG7ce5BXHOB0jgOmsuVEYe3mTV+leKWHoUJyZjQiry3LJWfWyz6je4YBdunz7cbHWFm0TV4ADHr/6klNy3xaAi5o1pMdk4qZlNJy7QmrIKKXYHCnJt8SxPiBipgSDI
rDLeUHFmrTsbenSYFI79LijPgaHaHF37b15na7/CTOOXxFq8GTlocjS9VN+j2+RwK73rmxc2BtjjgTL3bbSInYgC03lbYLmA6NRrM1pSP8aXWfxSNDg15DA8PMqxzkTVuXMLyc1jU+halElpTmRQL90f2L3qHejoTen0llyvsM6U+9mbS9hmdBrRY8Si7b8xq7A/9IblZlB2gnMtxJiuA1/MZD/orCQXEncZWE/Vx8C9kG
G7shI8SCF5/M+XKQaE5Ym2wekp+PLbliPXW+9R+mjeyBVpi+DMJ96227HYMp75kOFn60aXy3duZu+UM5fxHrFYZzyRwFcYDuqiBxZXB7m1SqUJxC6Y70/z0Ix0cFnkSjJD5QfY1hAHCb1lNtxsjnQhsoXzprrxsDKt2NIRoE3DBa95sSeAjuSKXT7r8sGcAxqZcKEMFoDcqEVOOupvVgJVf0pQU1SdoRn1SjsVYHOkdOhn
aZs6/6AjNITwPRkhcdusGkXrvzTw6rw4gaAMPndHoDDV9feSmdQOBQMkgTrZLgPxxLDGSlBJVmgkoNkfwDJEdjDCmZUkA/s7DAAA’,»,1);
/!40000 ALTER TABLE avh2d_ak_profiles ENABLE KEYS */;
UNLOCK TABLES;

root@localhost [my_db] > select c.table_name, t.table_comment,
-> c.column_name, c.column_comment,
-> c.data_type, c.column_type, c.column_default,
-> c.is_nullable, c.extra
-> from information_schema.columns c
-> join information_schema.tables t using(table_schema, table_name)
-> where c.table_schema = ‘Joomla’ and t.table_type = ‘avh2d_ak_profiles’
-> order by table_name, ordinal_position;
Empty set (0.01 sec)

I have the same issue not sure what to do — copying data from heroku mysql to postgres on localhost — connects to Heroku successfully but cannot create Schema

Источник

root@thingsboard01:/usr/share/thingsboard/data/sql# cat schema.sql

— Copyright © 2016-2017 The Thingsboard Authors

— Licensed under the Apache License, Version 2.0 (the «License»);
— you may not use this file except in compliance with the License.
— You may obtain a copy of the License at

— http://www.apache.org/licenses/LICENSE-2.0

— Unless required by applicable law or agreed to in writing, software
— distributed under the License is distributed on an «AS IS» BASIS,
— WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
— See the License for the specific language governing permissions and
— limitations under the License.

CREATE TABLE IF NOT EXISTS admin_settings (
id varchar(31) NOT NULL CONSTRAINT admin_settings_pkey PRIMARY KEY,
json_value varchar,
key varchar(255)
);

CREATE TABLE IF NOT EXISTS alarm (
id varchar(31) NOT NULL CONSTRAINT alarm_pkey PRIMARY KEY,
ack_ts bigint,
clear_ts bigint,
additional_info varchar,
end_ts bigint,
originator_id varchar(31),
originator_type integer,
propagate boolean,
severity varchar(255),
start_ts bigint,
status varchar(255),
tenant_id varchar(31),
type varchar(255)
);

CREATE TABLE IF NOT EXISTS asset (
id varchar(31) NOT NULL CONSTRAINT asset_pkey PRIMARY KEY,
additional_info varchar,
customer_id varchar(31),
name varchar(255),
search_text varchar(255),
tenant_id varchar(31),
type varchar(255)
);

CREATE TABLE IF NOT EXISTS attribute_kv (
entity_type varchar(255),
entity_id varchar(31),
attribute_type varchar(255),
attribute_key varchar(255),
bool_v boolean,
str_v varchar(10000000),
long_v bigint,
dbl_v double precision,
last_update_ts bigint,
CONSTRAINT attribute_kv_unq_key UNIQUE (entity_type, entity_id, attribute_type, attribute_key)
);

CREATE TABLE IF NOT EXISTS component_descriptor (
id varchar(31) NOT NULL CONSTRAINT component_descriptor_pkey PRIMARY KEY,
actions varchar(255),
clazz varchar,
configuration_descriptor varchar,
name varchar(255),
scope varchar(255),
search_text varchar(255),
type varchar(255)
);

CREATE TABLE IF NOT EXISTS customer (
id varchar(31) NOT NULL CONSTRAINT customer_pkey PRIMARY KEY,
additional_info varchar,
address varchar,
address2 varchar,
city varchar(255),
country varchar(255),
email varchar(255),
phone varchar(255),
search_text varchar(255),
state varchar(255),
tenant_id varchar(31),
title varchar(255),
zip varchar(255)
);

CREATE TABLE IF NOT EXISTS dashboard (
id varchar(31) NOT NULL CONSTRAINT dashboard_pkey PRIMARY KEY,
configuration varchar(10000000),
customer_id varchar(31),
search_text varchar(255),
tenant_id varchar(31),
title varchar(255)
);

CREATE TABLE IF NOT EXISTS device (
id varchar(31) NOT NULL CONSTRAINT device_pkey PRIMARY KEY,
additional_info varchar,
customer_id varchar(31),
type varchar(255),
name varchar(255),
search_text varchar(255),
tenant_id varchar(31)
);

CREATE TABLE IF NOT EXISTS device_credentials (
id varchar(31) NOT NULL CONSTRAINT device_credentials_pkey PRIMARY KEY,
credentials_id varchar,
credentials_type varchar(255),
credentials_value varchar,
device_id varchar(31)
);

CREATE TABLE IF NOT EXISTS event (
id varchar(31) NOT NULL CONSTRAINT event_pkey PRIMARY KEY,
body varchar,
entity_id varchar(31),
entity_type varchar(255),
event_type varchar(255),
event_uid varchar(255),
tenant_id varchar(31),
CONSTRAINT event_unq_key UNIQUE (tenant_id, entity_type, entity_id, event_type, event_uid)
);

CREATE TABLE IF NOT EXISTS plugin (
id varchar(31) NOT NULL CONSTRAINT plugin_pkey PRIMARY KEY,
additional_info varchar,
api_token varchar(255),
plugin_class varchar(255),
configuration varchar,
name varchar(255),
public_access boolean,
search_text varchar(255),
state varchar(255),
tenant_id varchar(31)
);

CREATE TABLE IF NOT EXISTS relation (
from_id varchar(31),
from_type varchar(255),
to_id varchar(31),
to_type varchar(255),
relation_type_group varchar(255),
relation_type varchar(255),
additional_info varchar,
CONSTRAINT relation_unq_key UNIQUE (from_id, from_type, relation_type_group, relation_type, to_id, to_type)
);

CREATE TABLE IF NOT EXISTS rule (
id varchar(31) NOT NULL CONSTRAINT rule_pkey PRIMARY KEY,
action varchar,
additional_info varchar,
filters varchar,
name varchar(255),
plugin_token varchar(255),
processor varchar,
search_text varchar(255),
state varchar(255),
tenant_id varchar(31),
weight integer
);

CREATE TABLE IF NOT EXISTS tb_user (
id varchar(31) NOT NULL CONSTRAINT tb_user_pkey PRIMARY KEY,
additional_info varchar,
authority varchar(255),
customer_id varchar(31),
email varchar(255) UNIQUE,
first_name varchar(255),
last_name varchar(255),
search_text varchar(255),
tenant_id varchar(31)
);

CREATE TABLE IF NOT EXISTS tenant (
id varchar(31) NOT NULL CONSTRAINT tenant_pkey PRIMARY KEY,
additional_info varchar,
address varchar,
address2 varchar,
city varchar(255),
country varchar(255),
email varchar(255),
phone varchar(255),
region varchar(255),
search_text varchar(255),
state varchar(255),
title varchar(255),
zip varchar(255)
);

CREATE TABLE IF NOT EXISTS ts_kv (
entity_type varchar(255) NOT NULL,
entity_id varchar(31) NOT NULL,
key varchar(255) NOT NULL,
ts bigint NOT NULL,
bool_v boolean,
str_v varchar(10000000),
long_v bigint,
dbl_v double precision,
CONSTRAINT ts_kv_unq_key UNIQUE (entity_type, entity_id, key, ts)
);

CREATE TABLE IF NOT EXISTS ts_kv_latest (
entity_type varchar(255) NOT NULL,
entity_id varchar(31) NOT NULL,
key varchar(255) NOT NULL,
ts bigint NOT NULL,
bool_v boolean,
str_v varchar(10000000),
long_v bigint,
dbl_v double precision,
CONSTRAINT ts_kv_latest_unq_key UNIQUE (entity_type, entity_id, key)
);

CREATE TABLE IF NOT EXISTS user_credentials (
id varchar(31) NOT NULL CONSTRAINT user_credentials_pkey PRIMARY KEY,
activate_token varchar(255) UNIQUE,
enabled boolean,
password varchar(255),
reset_token varchar(255) UNIQUE,
user_id varchar(31) UNIQUE
);

CREATE TABLE IF NOT EXISTS widget_type (
id varchar(31) NOT NULL CONSTRAINT widget_type_pkey PRIMARY KEY,
alias varchar(255),
bundle_alias varchar(255),
descriptor varchar(1000000),
name varchar(255),
tenant_id varchar(31)
);

CREATE TABLE IF NOT EXISTS widgets_bundle (
id varchar(31) NOT NULL CONSTRAINT widgets_bundle_pkey PRIMARY KEY,
alias varchar(255),
search_text varchar(255),
tenant_id varchar(31),
title varchar(255)