Syntax error at or near having

8 Tips Absolute Beginners Can Use to Fix SQL Queries

Code errors are common – and frustrating. And when you’re just learning SQL, it can be very challenging to find and fix your mistakes. In this post, we’ll show you eight ways to solve or eliminate common SQL coding errors.

Today, we’ll talk about some tips the SQL beginner can use to avoid several common errors. These tips work in any database environment. As we go along, we’ll be showing some error messages. The exact wording of your error messages may be a little different, but don’t worry; each database engine has their own variants, but the meaning is the same.

That being said, we won’t be focusing on error messages. Instead, we’ll pinpoint the actual cause of the problem (such as forgetting a parenthesis or a quotation mark). This way, you’ll learn how to bypass the issue in the first place.

Ready? Let’s begin!

1. Place Open and Close Parentheses First

Remembering the closing character is key to eliminating unbalanced parentheses, quotes, double quotes, or square brackets. Best practices suggest typing both characters first (open and close) and then typing whatever goes inside.

The following example shows an error when parentheses are not balanced:

Example 1: Unbalanced parentheses

2. Don’t Put a Comma at the End of a Column or Table Sequence

Commas act as a separator in SQL. There should not be any commas between FROM and the first table name or after the final table name. The same idea applies to column definition: when you create a table, be sure not to type an extra comma after the final column name.

This is a really common error.

Example 2: An extra comma

3. Use Partial Query Evaluation to Debug Long SQL Queries

Many SQL clients like Navicat or pgAdmin allow the partial execution of a query. You can do this by using your mouse to highlight part of the code. Using this divide-and-conquer technique, you can easily isolate and fix errors. Obviously, the marked part must be valid SQL.

The upcoming query has two errors. If we execute the opening line of the query, we can see the first problem. (Hint: the «llastname» column doesn’t exist.)

Example 3: An incorrect column name

However if we execute the complete SQL statement, we get an error related to unbalanced parentheses:

Example 4: Subquery with wrong parentheses

We can also mark a subquery and execute it individually, as in the next example:

Example 5: Incorrect function name

4. Pay Attention to Column and Table Names

Pay very close attention when typing column names or table names. If possible, try to copy and paste the name from a command you know is correct – preferably one that’s already executed properly. Best practices suggest copying and pasting names even if you think it’s more time-consuming than typing.

Having a misspelled column name or referring to a column in a table not in the FROM clause is very common indeed. Always look for typos in the column name, make sure the table in the FROM has this column, and make sure that the table is mentioned in FROM .

Example 6: Incorrect table name

Another good practice is to use a table alias or a table name as a column prefix. This is doubly important when you have two or more tables in the <>FROM clause. The following error can appear if you refer to two identically-named columns in different tables:

Example 7: Ambiguous column names

Example 8: Prefixed column names

To fix these errors, add the table name before the column name. (In the above example, that would be employee.depto_id and department.depto_id instead of just depto_id .)

5. Compare Compatible Data Types Only

When you write comparison conditions in the WHERE clause, make sure both data types are compatible with the comparison operator and with each other. If this is not possible, you may have to cast one of the data types. The general rule is to compare numbers against numbers, character strings against character strings, etc.

Some database systems automatically convert data types where possible; others provide enhanced data type conversions (i.e. a TIMESTAMP value can be automatically converted to a DATE before comparisons). Still other database services don’t offer conversions at all. So it is best to look out for these potential issues yourself.

Anyway, the following SQL code gets a data mismatch error because a CHAR string is being compared with an integer value:

Example 9: Mismatched data types

No operator matches the given name and argument type(s). You might need to add explicit type casts to solve this one.

6. Use IS NULL When Comparing NULL Values

If you only need to verify whether a column has a NULL value, pay special attention to which expressions you use. One common mistake is to use = NULL or <> NULL , but these expressions are not syntactically valid. Use IS NULL and IS NOT NULL clauses instead.

Let’s see the incorrect and correct samples:

Example 10: Incorrect NULL comparison

Example 11: Correct NULL comparison

7. Always Include the JOIN Condition

There is more than one valid way to do a join in SQL. The traditional way is to list all the tables to be joined in the FROM clause and put the join conditions in the WHERE clause to build pairs of records. The other (more declarative) way is to use the JOIN clause and list the join conditions after the ON clause. Both are syntactically equivalent, but you should know how to identify the join condition for both.

Here we have two valid joins:

Example 12: Two equivalent joins

However, the tip is: Don’t forget the join condition! Every time you join two or more tables, you must write a join condition to link both tables. If you don’t specify this, you won’t get an error message; you’ll just get incorrect results. These will be wrong because every record from the first table will be joined with all records of the second table. This type of result set is called a Cartesian product of two tables, and usually is not an expected result.

Example 13: A Cartesian product – usually not the result you want

8. Include Non-Aggregated Columns from the SELECT List in GROUP BY Columns

When using aggregate functions, there are some restrictions on what columns can be included in the SELECT list (i.e. the column names after the SELECT clause). You can only include the columns specified in the GROUP BY clause, plus aggregate functions and constants. If you select only aggregate columns, any other column will generate an error. You can see this in the following example.

Example 14: Extra columns in the SELECT list

Example 15: No extra columns in the SELECT list

Sometimes we need to filter data by using an aggregation function. One of the most common mistakes is to put a condition using the aggregate function in the WHERE clause. This is shown below:

Example 16: Incorrect aggregate function in the WHERE

Remember, if you need to filter using an aggregate function, the correct way is to put the condition using the aggregate in the HAVING clause, as in the following example:

Example 17: The aggregate function is in the HAVING clause

Try It Yourself!

LearnSQL is a great place to get started with SQL. Several courses are geared for beginning learners. You can test the tips in this article on the exercises in this SQL course.

Источник

postgresql: syntax error at or near «DO»

I am trying to run a DO block, e.g. this one (copied from a post in this forum)

but what happens is:

ERROR: syntax error at or near «DO»

SQL state: 42601

It doesn’t matter, what DO block I run, the DO statement is simply not working (including on examples copy/pasted from the doucment, and I wonder what could be the reason. Could anybody help me, please?

2 Answers 2

You’re running on an old version of PostgreSQL.

In general, if you get an unexpected syntax error on a keyword or PostgreSQL complains about a missing function that should obviously exist, the first thing to do is check that the feature you’re trying to use actually existed in your version.

You can replace a DO block with:

in an older version.

If you have the same error with postgreSQL 9.0+, then it is due to the wrong execute type. pgAdiminIII has two execute (green triangle) buttons:

• Execute Query
• Execute pgScript

In order to run DO $$ you need the first one.

Linked

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.10.43146

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

SQL Errors: Five Common SQL Mistakes

As you learn SQL, watch out for these common coding mistakes

You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message.

Don’t despair! Coding errors are common in any programming language, and SQL is no exception. In this article, we’ll discuss five common mistakes people make when writing SQL.

The best way to prevent mistakes in SQL is practice. LearnSQL.com offers over 30 interactive SQL courses. Try out our SQL Practice track with 5 courses and over 600 hands-on exercises.

Watch Your Language (and Syntax)

The most common SQL error is a syntax error. What does syntax mean? Basically, it means a set arrangement of words and commands. If you use improper syntax, the database does not know what you’re trying to tell it.

To understand how syntax works, we can think of a spoken language. Imagine saying to a person “Nice dof” when you mean “Nice dog”. The person does not know what “dof” means. So when you tell your database to find a TABEL instead of a TABLE, the database does not know what it needs to do.

People tend to make the same kinds of syntax mistakes, so their errors are usually easy to spot and very much the same. After you read this article, you should be able to remember and avoid (or fix) these common mistakes. Knowing what errors to look for is very important for novice SQL coders, especially early on. New coders tend to make more mistakes and spend more time looking for them.

The types of SQL errors we will look at are:

1. Misspelling Commands
2. Forgetting Brackets and Quotes
3. Specifying an Invalid Statement Order
4. Omitting Table Aliases
5. Using Case-Sensitive Names

Ready? Let’s start.

SQL Errors:

1. Misspelling Commands

This is the most common type of SQL mistake among rookie and experienced developers alike. Let’s see what it looks like. Examine the simple SELECT statement below and see if you can spot a problem:

If you run this query, you’ll get an error which states:

Each database version will tell you the exact word or phrase it doesn’t understand, although the error message may be slightly different.

What is wrong here? You misspelled FROM as FORM. Misspellings are commonly found in keywords (like SELECT, FROM, and WHERE), or in table and column names.

Most common SQL spelling errors are due to:

• “Chubby fingers” where you hit a letter near the right one: SELEVT or FTOM or WJIRE
• “Reckless typing” where you type the right letters in the wrong order: SELETC or FORM or WHEER

Solution:

Use an SQL editor that has syntax highlighting: the SELECT and WHERE keywords will be highlighted, but the misspelled FORM will not get highlighted.

If you’re learning with interactive SQL courses in LearnSQL.com , the code editor puts every SELECT statement keyword in light purple. If the keyword is black, as it is with any other argument, you know there’s a problem. (In our example, FORM is black).

So if we correct our statement we get:

The keyword is now the right color and the statement executes without an error.

2. Forgetting Brackets and Quotes

Brackets group operations together and guide the execution order. In SQL (and in all of the programming languages I use), the following order of operations …

… is not the same as:

Can you figure out why?

A very common SQL mistake is to forget the closing bracket. So if we look at this erroneous statement :

We get an error code with the position of the error (the 102nd character from the beginning):

Remember: brackets always come in pairs.

The same is true with single quotes ( ‘ ‘ ) or double quotes ( ” ” ). There is no situation in SQL where we would find a quote (either a single quote or a double quote) without its mate. Column text values can contain one quote ( e.g. exp.last_name = «O’Reilly» ) and in these situations we must mix two types of quotes or use escape characters. ( In SQL, using escape characters simply means placing another quote near the character you want to deactivate – e.g. exp.last_name = ‘O’’Reilly. )

Solution:

Practice, practice, practice. Writing more SQL code will give you the experience you need to avoid these mistakes. And remember people usually forget the closing bracket or quotation mark. They rarely leave out the opening one. If you’re running into problems, take a close look at all your closing punctuation!

3. Invalid statement order

When writing SELECT statements, keep in mind that there is a predefined keyword order needed for the statement to execute properly. There is no leeway here.

Let’s look at an example of a correctly-ordered statement:

There’s no shortcut here; you simply have to remember the correct keyword order for the SELECT statement:

• SELECT identifies column names and functions
• FROM specifies table name or names (and JOIN conditions if you’re using multiple tables)
• WHERE defines filtering statements
• GROUP BY shows how to group columns
• HAVING filters the grouped values
• ORDER BY sets the order in which the results will be displayed

You cannot write a WHERE keyword before a FROM , and you can’t put a HAVING before a GROUP BY . The statement would be invalid.

Let’s look at what happens when you mix up the statement order. In this instance, we’ll use the common SQL error of placing ORDER BY before GROUP BY :

The error message we see is pretty intimidating!

Solution:

Don’t be discouraged! You can see that all of the keywords are highlighted correctly and all the quotations and brackets are closed. So now you should check the statement order. When you’re just beginning your SQL studies, I suggest using a SELECT order checklist. If you run into a problem, refer to your list for the correct order.

4. Omitting Table Aliases

When joining tables, creating table aliases is a popular practice. These aliases distinguish among columns with the same name across tables; thus the database will know which column values to return. This is not mandatory when we’re joining different tables, since we can use the full table names. But it is mandatory if we join a table to itself.

Suppose we’re writing an SQL statement to find an exhibition’s current location and the location from the previous year:

The database would return an error:

Note: Whenever you encounter “ambiguous column name” in your error message, you surely need table aliases.

The correct statement (with aliases) would be:

Solution:

Practice using table aliases for single-table SELECT statements. Use aliases often – they make your SQL more readable.

5. Using Case-Sensitive Names

This error only occurs when you need to write non-standard names for tables or database objects.

Let’s say that you need to have a table named LargeClient and for some reason you add another table called LARGECLIENT. As you already know, object names in databases are usually case-insensitive. So when you write a query for the LargeClient table, the database will actually query LARGECLIENT.

To avoid this, you must put double quotes around the table name. For example:

When creating a table, you will need to use double quotes if:

• The table will have a case-sensitive name.
• The table name will contain special characters. This includes using a blank space, like “Large Client”.

Solution:

Avoid using these names if you can. If not, remember your double quotes!

Everybody Makes SQL Mistakes

Those are the five most common errors in SQL code. You’ll probably make them many times as you learn this language. Remember, everybody makes mistakes writing code. In fact, making mistakes is a normal and predictable part of software development.

So don’t be discouraged. When you make mistakes in the future, try to analyze your code in a structured way. With a structured analysis, you can find and correct your errors quicker.

If you would like to learn about some other syntactic mistakes that I’ve not included here, please let me know. In an upcoming article, we’ll look at non-syntactic errors. These return or modify data and are therefore much more dangerous. Subscribe to our blog so you won’t miss it!

Источник

  Last tested: Feb 2021

Overview

This SQL error generally means that somewhere in the query, there is invalid syntax.
Some common examples:

• Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD)
• Typo in the SQL (missing comma, misspelled word, etc)
• Missing a sql clause (missed from, join, select, etc)
• An object does not exist in the database or is not accessible from the current query (eg referencing orders.id when there is no orders table joined in the current query, etc)

In some circumstances, the database error message may display extra detail about where the error was raised, which can be helpful in narrowing down where to look.

Error Message

SQL ERROR: syntax error at or near

Troubleshooting

This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.

TIP: To make this process easier, change the group by clause to use position references
eg: group by 1,2,3,4,5 instead of group by orders.status, orders.date, to_char(...)...
as well as separate the where and having clauses onto multiple lines.

So for example, say we have the following query:

play_arrow

WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, sum(cte.sales_amount), count(*) from cte
join foo on cte.date = foo.date
group by status, foo.date
order by 3 desc

We could start by running just the portion in the CTE:

play_arrow

-- WITH cte AS (
select id, status, sales_amountfrom orders
-- )
-- select status, foo.date, sum(cte.sales_amount), count(*)
-- from cte
-- join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Then strip out the aggregates and portions related to them

play_arrow

WITH cte AS (
    select id, status, sales_amountfrom orders
)
select status, foo.date, -- sum(cte.sales_amount), count(*)
from cte
join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc

Iteratively stripping out / adding back in portions of the query until you find the minimum query to trigger the error.

• Lookup functions and syntax If the query is small enough, or if we’ve narrowed the scope enough with 1, google all the functions used in the query and verify that they exist and are being used correctly.

• Verify all objects exist Verify that you’ve joined all tables used in the select, where, and having clause, and that those tables exist in the db. Once we’ve narrowed things down from 1, also check that each column exists in the table specified.

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

@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');