Postgresql Error: Syntax Error At or Near "Varchar"

PostgreSQL ERROR: syntax error at or near "varchar"

Altering a column's type and adding a foreign key on it are two different statements:

ALTER TABLE takes ALTER COLUMN course_id TYPE VARCHAR(10);

ALTER TABLE takes 
ADD CONSTRAINT takes_course_fk FOREIGN KEY (course_id) 
REFERENCES course(course_id);

'syntax error at or near "VARCHAR"' in DECLARE section when trying to create function

Your posed answer still has a couple of problems. And you can very well use RETURNS TABLE(). Compare:

• plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function:

CREATE OR REPLACE FUNCTION get_name_without_distinct_by_polygon_group(start_time timestamp,
                                                                      end_time timestamp,
                                                                      polygon_group_id int)
  RETURNS TABLE(name_name_name_name VARCHAR(12)) AS
$func$
DECLARE
   name VARCHAR(12);  -- possible naming conflict!
   poly_id int;
BEGIN
   FOR poly_id IN  --  no parentheses needed
      SELECT polygon_id
      FROM   polygon_group_members 
      WHERE  group_id = poly_id  -- I suspect you really want polygon_group_id here
   LOOP
      FOR name_name_name_name IN  -- assign directly
         SELECT DISTINCT name  -- l.name or p.name??
         FROM   polygons p
         JOIN   location l ON st_intersects(l.polygon, p.polygon)
         WHERE  p.id = poly_id
         AND    l.timestamp BETWEEN start_time AND end_time
      LOOP
        RETURN NEXT;  -- already assigned
      END LOOP;
   END LOOP;
   RETURN;
END
$func$  LANGUAGE plpgsql;

Be aware of possible naming conflicts. All declared variable and parameters (including fields in the RETURNS TABLE() clause are visible in SQL queries inside the body of a plpgsql or SQL function. A widespread convention would be to prepend variable names with _ and table-qualify all columns in queries. See earlier answer from today:

• Check for integer in string array

The whole function could probably be replaced with a single SELECT statement.

Getting 'error: syntax error at or near...' in Postgresql insert query

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
(an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

Your string values are not quoted. It would have to be...

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
('an3cxZh8ZD3tdtqG4wuwPR', 'Alan Paton', 'alan-paton');

You could add quotes to your query, but don't. Your query as written is insecure and vulnerable to a SQL injection attack. Do not insert values into queries with string concatenation.

Instead, use parameters.

const insertSQL = `
  INSERT INTO authors (id, author_name, author_slug) 
  VALUES ($1, $2, $3);
`;
await pool.query( insertSQL, [authorID, authorName, authorSlug] );

Postgres will handle the quoting for you. This is safer, more secure, and faster.

---

Note that an3cxZh8ZD3tdtqG4wuwPR is not a valid UUID. A UUID is a 128 bit integer often represented as a 32 character hex string.

Note that you also probably want to use autoincrementing primary keys instead of generating the ID yourself. For a UUID primary key, load the uuid-ossp package and use its UUID function as your default.

create extension "uuid-ossp";

create table authors (
  id uuid primary key default uuid_generate_v4(),

  -- There's no point in arbitrarily limiting the size of your text fields.
  -- They will only use as much space as they need.
  author_name text not null unique check (author_name <> ''),
  author_slug text not null unique check (author_slug <> '')
);

insert into authors (author_name, author_slug) 
values ('Alan Paton', 'alan-paton');

syntax error at or near "VARCHAR" LINE 1: CREATE OR REPLACE FUNCTION DATEADD (interval VARCHAR(4000),

interval is a reserved keyword (a data type), you need to either use double quotes "interval" or find a different name, e.g. you can use add_interval instead.

You can add a number to a timestamp in Postgres. You need to construct an interval from the passed value. The easiest way is to use make_interval() for that.

In PL/pgSQL it's returns XXX not return XXX.

You also need to replace raise_application_error('-20000',sqlerrm) with something else. But I don't see any benefit in obfuscating the original error, so my recommendation would be to simply remove the exception handler completely.

You can also simplify your IF conditions by using IN instead of multiple OR expressions.

So you wind up with something like this:

CREATE OR REPLACE FUNCTION DATEADD (add_type text, adding INT, entry_date TIMESTAMP(0))  
  RETURNS TIMESTAMP(0) as 
$body$ 
declare 
  result TIMESTAMP(0); 
BEGIN 
   If upper(add_type) IN ('D','Y','W','DD','DDD','DAY') THEN 
     result := entry_date + make_interval(days => adding); 
   ELSIF upper(add_type) IN ('WW', 'IW', 'WEEK') THEN 
     result := entry_date + make_interval(weeks => adding); 
   ELSIF  upper(add_type) IN ('YYYY', 'YEAR') THEN 
     result := add_months(entry_date, adding * 12); 
   ELSIF upper(add_type) IN ('Q', 'QUARTER') THEN  
     result := add_months(entry_date, adding * 3); 
   ELSIF upper(add_type) IN ('M', 'MM', 'MONTH') THEN 
     result := add_months(entry_date, adding); 
   ELSIF upper(add_type) IN ('H', 'HH', 'HOUR') THEN 
     result := entry_date + make_interval(hours => adding); 
   ELSIF upper(add_type) IN ('N', 'MI', 'MINUTE') THEN 
      result := entry_date + make_interval(minutes => adding);
   ELSIF upper(add_type) IN ('S', 'SS', 'SECOND') THEN 
      result := entry_date + make_interval(secs => adding); 
   END IF; 
   RETURN result; 
end; 
$body$ 
LANGUAGE plpgsql;

Getting "syntax error at or near '('" Postgresql

Postgres doesn't have a TINYINT type, it has SMALLINT. And both BIGINT and SMALLINT don't have a width:

CREATE TABLE posts (
    title VARCHAR(10000) NOT NULL,
    url VARCHAR(10000) NOT NULL,
    img VARCHAR(10000),
    source VARCHAR(50) NOT NULL,
    content VARCHAR(65535),
    tldr VARCHAR(65535) NOT NULL,
    getTime BIGINT NOT NULL,
    category SMALLINT
);

syntax error at or near "INSERT" when creating a temp table and insert into a subquery--PostgreSQL Error

Note that CREATE TABLE and INSERT INTO are two distinct statements. You need to insert a ; between them:

...
)
;
INSERT INTO ...

I created a temp table

Note that this is not a temporary table because you are missing the TEMPORARY keyword.

postgresql ERROR: syntax error at or near "PRIMARY"

Should all primary keys auto-increment in PostgreSQL?

No, not necessarily.

In the first statement you are not specifying a data type for the column, if you don't want it to be an auto increment, simply use integer:

 songplay_id integer PRIMARY KEY,

---

Note that serial is not a "constraint", it's a data type definition which is a shortcut for an integer column that takes its default value from a sequence.

---

Related Topics