Postgresql – ERROR: relation “codepool_codepoolseq_seq” does not exist SQL state: 42P01

postgresql-9.4

Below is my script but I keep getting an error.

-- Table: public.codepool

-- DROP TABLE public.codepool;

CREATE TABLE public.codepool
(
    codepoolseq bigint NOT NULL DEFAULT nextval('codepool_codepoolseq_seq'::regclass),
    codenumber character(50) COLLATE pg_catalog."default",
    CONSTRAINT codepool_pkey PRIMARY KEY (codepoolseq)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.codepool
    OWNER to postgres;

The error I get is:

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

Best Answer

In your Scheme you must have Sequences called codepool_codepoolseq_seq

Example create:


-- SEQUENCE: public.codepool_codepoolseq_seq

-- DROP SEQUENCE public.codepool_codepoolseq_seq;

CREATE SEQUENCE public.codepool_codepoolseq_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE public.codepool_codepoolseq_seq
    OWNER TO postgres;

Related Solutions

Postgresql – pg_restore does not restore blobs

I've faced the same issue some months ago, and I have managed to solve it with a bash script. This is the piece of code that take care to backup large objects, and save it to a local folder (/path/to/destination/lo/folder/) with filename = oid (the filename is important in order to restore lo with same oid) :

echo -e "going to export largeobjects belonging to community $i..." && sleep 2
psql -U <username> -X -c "SELECT file_oid FROM <my_table>" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    -d <my_source_db> \
| while read file_oid ; do
    echo "Exporting largeobject OID: $file_oid"
    ${PSQL} -d <my_source_db> -c "SELECT lo_export($file_oid, '/path/to/destination/lo/folder/$file_oid');"
done

Then to restore them I've written this other piece of code:

echo "going to import largeobjects belonging to community $i..." && sleep 2
LOBJECTS=/path/to/destination/lo/folder/*
for f in $LOBJECTS
do
    echo "Processing $f file..."
    filename=$(basename "$f")
    oid=${filename}

    psql -U <username> -d <my_source_db> -c"
    -- check if largeobject is already present, if it is delete it
    SELECT lo_unlink(${oid});"

    psql -U <username> -d <my_source_db> -c"
    -- import largeobjects
    SELECT lo_import('$f', ${oid});
    "
done

Out there it may exist a simpler solution, but at the time I've not been able to find it, this is why used this approach. I hope you will be more lucky and find a cleaner solution. I'll follow this post in case I can learn something useful too : )

Postgresql – Querying pg_buffercache: relation “pg_buffercache” does not exist

You need to first connect to the database that you need to check the stats from, and in that do the create extension, after which the query should work ;)

Best Answer

Related Solutions

Postgresql – pg_restore does not restore blobs

Postgresql – Querying pg_buffercache: relation “pg_buffercache” does not exist

Related Question