Troubleshooting of PostgreSQL

Troubleshooting of PostgreSQL

Troubleshooting of PostgreSQL

How to fix Syntax error: 7 ERROR: LIMIT #,# syntax is not supported

Query syntax mer error

SELECT * 
FROM my_table
LIMIT 0,10

Error message

Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR:  LIMIT #,# syntax is not supported                                                         
HINT:  Use separate LIMIT and OFFSET clauses.

Correct query syntax

SELECT * 
FROM my_table
LIMIT 10 OFFSET 0

How to fix ERROR: operator does not exist: ` character varying

The following SQL query has a syntax error:

SELECT `my_columm`
from my_table
LIMIT 10

Error message

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.

To fix the error, update the query with the correct syntax:

SELECT my_columm
from my_table
LIMIT 10

How to fix ERROR: argument of WHERE must be type boolean, not type integer

Query syntax mer error

SELECT * 
FROM my_table
WHERE 1

Correct query syntax

SELECT * 
FROM my_table
WHERE 1 = 1

or

SELECT * 
FROM my_table
WHERE TRUE

How to fix the ERROR: column "count" does not exist

The following SQL query has a syntax error:

SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count >= 2

To fix the error, update the query with the correct syntax:

SELECT count(*) count, content_hash
from my_table
GROUP BY content_hash
having count(*) >= 2

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

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

1. Windows / Linux: Go to File → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.
2. Mac: Go to pgAdmin 4 → Preferences → Query Tool → Auto Completion, and make sure "Keywords autocomplete?" is checked.

Further reading

• dynamic sql - Why would someone use WHERE 1=1 AND <conditions> in a SQL clause? - Stack Overflow
• Working with Insert Into Ignore in Postgres

References

Troubleshooting of ...

• Troubleshooting of Excel errors

• PHP, cUrl, Python, selenium, HTTP status code errors

• Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors

• Troubleshooting of regular expression

• HTML/Javascript: Troubleshooting of javascript, XPath

• Software: Mediawiki, Docker, FTP problems, online conference software

• Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting

Template

• Bug report template