Error in postgresql

Question

Error in postgresql

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a question about postgresql that does not allow me to move forward and maybe it's something simple that I'm forgetting or omitting.

This is the structure of my table:

CREATE TABLE public.p_proyecto
(
    id_proyecto INTEGER NOT NULL DEFAULT NEXTVAL(('p_proyecto_id_proyecto_seq'::text)::regclass),
    id_municipio INTEGER,
    denominacion CHARACTER VARYING(250) COLLATE pg_catalog."default",
    fase CHARACTER VARYING(50) COLLATE pg_catalog."default",
    id_financiamiento INTEGER,
    montofinanciamiento NUMERIC(10,2),
    tiempoejecucion CHARACTER VARYING(10) COLLATE pg_catalog."default",
    supervision CHARACTER VARYING(50) COLLATE pg_catalog."default",
    estado CHARACTER VARYING(1) COLLATE pg_catalog."default",
    usuarioregistrosistema CHARACTER VARYING(50) COLLATE pg_catalog."default",
    fecharegistrosistema TIMESTAMP WITHOUT TIME zone,
    CONSTRAINT pk_p_proyecto PRIMARY KEY (id_proyecto),
    CONSTRAINT fk_p_financiamiento_p_financiamiento FOREIGN KEY (id_financiamiento)
        REFERENCES public.p_financiamiento (id_financiamiento) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_p_municipio_p_municipio FOREIGN KEY (id_municipio)
        REFERENCES public.p_municipio (id_municipio) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

The problem is when I want to make an insert omitting the field project_id that should be added automatically. But I get this error:

ERROR:  relation "p_proyecto_id_proyecto_seq" does not exist
SQL state: 42P01

Both in the pgadmin and in the PHP I have the same error. Did I do something wrong by assigning the autoincrementable value?

sql postgresql postgresql-9.3

asked by user3123766 14.11.2017 в 15:30

source

2 answers

Raspberry pi 3 Kali Linux Assign an intent to each item of a spinner

score 1 · Answer 1

If you declare the first field as SERIAL, say something like

CREATE TABLE test_table (
    id SERIAL NOT NULL,
    nombre text
);

A sequence whose name is derived from the name of the table will be created below, and the DEFAULT value is assigned for that column. If you inspect the newly created table you will see that its definition is

What you are doing is baptizing the sequence arbitrarily, and PostgreSQL will not make any automagic creation below.

score 0 · Answer 2

The error simply means that it does not find the p_proyecto_id_proyecto_seq sequence. If you have not already done so, it is necessary that you create the sequence in advance:

create sequence p_proyecto_id_proyecto_seq

Also, although technically the nextval syntax that you use is not incorrect, it is not ideal. The syntax you use is an old one that does not verify the existence of the sequence until you try to insert a record. It is much better that the verification is done once with the create table ... .

For this, you simply need to express it in the following way, without the additional casts:

CREATE TABLE public.p_proyecto
(
    id_proyecto INTEGER NOT NULL DEFAULT NEXTVAL('p_proyecto_id_proyecto_seq'),
...

Maybe you tried it this way before and it gave you the error you mentioned, and you managed to eliminate the error with the casts. But in reality, the only thing you did is postpone the moment where error appears. In the end, in one way or another, you must make sure that the sequence exists.