Troubleshooting of PostgreSQL: Difference between revisions
No edit summary |
Tag: wikieditor |
||
| (9 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
Troubleshooting of PostgreSQL | Troubleshooting of [https://www.postgresql.org/ PostgreSQL] | ||
== Syntax error: 7 ERROR: LIMIT #,# syntax is not supported == | == Troubleshooting of PostgreSQL == | ||
=== How to fix Syntax error: 7 ERROR: LIMIT #,# syntax is not supported === | |||
Query syntax mer error | Query syntax mer error | ||
<pre> | <pre> | ||
| Line 9: | Line 10: | ||
</pre> | </pre> | ||
Correct query syntax | Error message | ||
<pre> | |||
Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: LIMIT #,# syntax is not supported | |||
HINT: Use separate LIMIT and OFFSET clauses. | |||
</pre> | |||
Correct query syntax<ref>[http://www.sqlines.com/postgresql/limit_offset PostgreSQL Queries - OFFSET and LIMIT - Guide, Examples and Alternatives - SQLines Tools]</ref> | |||
<pre> | <pre> | ||
SELECT * | SELECT * | ||
| Line 16: | Line 23: | ||
</pre> | </pre> | ||
=== How to fix ERROR: operator does not exist: ` character varying === | |||
The following SQL query has a syntax error: | |||
<pre> | |||
SELECT `my_columm` | |||
from my_table | |||
LIMIT 10 | |||
</pre> | |||
== ERROR: | Error message | ||
<pre> | |||
ERROR: operator does not exist: ` character varying | |||
LINE 1: SELECT `content_hash` | |||
^ | |||
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast. | |||
</pre> | |||
To fix the error, update the query with the correct syntax: | |||
<pre> | |||
SELECT my_columm | |||
from my_table | |||
LIMIT 10 | |||
</pre> | |||
=== How to fix ERROR: argument of WHERE must be type boolean, not type integer === | |||
Query syntax mer error | Query syntax mer error | ||
<pre> | <pre> | ||
| Line 41: | Line 68: | ||
</pre> | </pre> | ||
=== How to fix the ERROR: column "count" does not exist === | |||
The following SQL query has a syntax error: | |||
<pre> | |||
SELECT count(*) count, content_hash | |||
from my_table | |||
GROUP BY content_hash | |||
having count >= 2 | |||
</pre> | |||
To fix the error, update the query with the correct syntax: | |||
<pre> | |||
SELECT count(*) count, content_hash | |||
from my_table | |||
GROUP BY content_hash | |||
having count(*) >= 2 | |||
</pre> | |||
=== How to fix: The numeric column data all became null after imported === | |||
[[Troubleshooting of Navicat for PostgreSQL]] | |||
=== pgAdmin 4 on macOS Has No Autocomplete for Field or Table Names === | |||
[https://www.pgadmin.org/download/ pgAdmin] 4 has autocomplete built in by default, but it sometimes needs to be triggered manually or configured first. | |||
'''How to trigger it''' | |||
In the Query Tool, press `Ctrl / Cmd + Space`. However, on macOS, `Ctrl + Space` is reserved for switching input methods, and `Cmd + Space` opens Spotlight, so the shortcut conflict likely prevents the autocomplete menu from appearing. This menu is what normally shows suggestions for tables, columns, schemas, and keywords. | |||
'''How to make autocomplete appear as you type''' | |||
# Windows / Linux: Go to File → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked. | |||
# Mac: Go to pgAdmin 4 → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked. | |||
== Further reading == | |||
* [https://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause dynamic sql - Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? - Stack Overflow] | * [https://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause dynamic sql - Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? - Stack Overflow] | ||
* [https://koalatea.io/postgres-insert-ignore/ Working with Insert Into Ignore in Postgres] | |||
== References == | |||
<references /> | |||
{{Template:Troubleshooting}} | {{Template:Troubleshooting}} | ||
[[Category: PostgreSQL]] | [[Category:PostgreSQL]] | ||
[[Category:Database]] | |||
[[Category:Data Science]] | [[Category:Data Science]] | ||