ERROR: column c.relhasoids does not exist

In PostgreSQL 12, the following errors occur when query some pg_ tables as below:

=> \d pg_class
ERROR:  column c.relhasoids does not exist
LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...
                                                             ^
=> select * from pg_class;
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR:  column c.relhasoids does not exist
        ^

Subscribe to get access

Read more of this content when you subscribe today.

ERROR: currval of sequence “SEQUENCE_NAME” is not yet defined in this session

In PostgreSQL, when trying to get the current value of a sequence, the following errors occur:

select currval('SEQUENCE_NAME');

ERROR:  currval of sequence "SEQUENCE_NAME" is not yet defined in this session
SQL state: 55000

SOLUTION

Query PG_SEQUENCES to get the last value of sequences.

=> \d pg_sequences
              View "pg_catalog.pg_sequences"
    Column     |  Type   | Collation | Nullable | Default
---------------+---------+-----------+----------+---------
 schemaname    | name    |           |          |
 sequencename  | name    |           |          |
 sequenceowner | name    |           |          |
 data_type     | regtype |           |          |
 start_value   | bigint  |           |          |
 min_value     | bigint  |           |          |
 max_value     | bigint  |           |          |
 increment_by  | bigint  |           |          |
 cycle         | boolean |           |          |
 cache_size    | bigint  |           |          |
 last_value    | bigint  |           |          |

ORA-00932: inconsistent datatypes from Heterogeneous Service Database Link

There is an Oracle database link between Oracle and PostgreSQL database through Heterogeneous Service ( HS ).

The sql query is working fine in PostgreSQL database:

testdb=> \d test_tbl
Table "testuser.test_tbl"
Column  | Type                    | Collation | Nullable | Default
--------+-------------------------+-----------+----------+---------
id      | double precision        |           |          |
s10     | character varying(10)   |           |          |
s20     | character varying(20)   |           |          |
s30     | character varying(2048) |           |          |


testdb=> select * from test_tbl;
id  | s10    | s20    | s30
----+--------+--------+---------------
1   | tets1 | test2 | Test Record 1

(1 row)

testdb=>select "id", substr("s10",1,5),
                substr("s20",1,5),substr("s30",1,10) 
         from test_tbl;

id | substr | substr | substr
---+--------+--------+------------
1  | test1  | test2  | Test Recor

(1 row)

While run the same query in Oracle database through Heterogeneous Service ( HS ) database link, get “ORA-00932: inconsistent datatypes” error. Sometimes you might get “ORA-02070: database does not support TO_CHAR in this context”.

SQL> select "id", substr("s10",1,5),substr("s20",1,5),substr("s30",1,10) 
      from "test_tbl"@PGLINK;
select "id", substr("s10",1,5),substr("s20",1,5), substr("s30",1,10) 
from "test_tbl"@PGLINK
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

Subscribe to get access

Read more of this content when you subscribe today.

SEQUENCE in PostgreSQL

1)Create Sequence:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] 
[ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ]


CREATE SEQUENCE serial START 101;

2) Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

SELECT nextval('serial');

 nextval
---------
     102

3)Sequence Manipulation Functions:

-- Return value most recently obtained with nextval 
select currval('serial') ;
-----
102
(1 row)

--Return value most recently obtained with nextval for any sequence
select lastval();
lastval
---------
102
(1 row)

-- Advance sequence and return new value
select nextval('serial') ;
-----
103
(1 row)

-- Set sequence's current value
select setval('serial', 1);
setval
--------
1
(1 row)

--setval
SELECT setval('serial', 42); Next nextval will return 43
SELECT setval('serial', 42, true); Same as above
SELECT setval('serial', 42, false); Next nextval will return 42

4) Sequence naming:

nextval('serial')   operates on sequence serial
nextval('SERIAL')   operates on sequence serial
nextval('"Serial"') operates on sequence Serial

5) Alter sequence:

ALTER SEQUENCE [ IF EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] 
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] 
[ RESTART [ [ WITH ] restart ] ] 
[ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ] 

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER 
| SESSION_USER } 
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name 
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

6) Grant Privileges On Sequence:

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 
TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be: 
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

7)Drop sequence:

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE serial;

8) Views about sequences:

PG_SEQUENCES:

NameTypeReferencesDescription
schemanamenamepg_namespace.nspnameName of schema containing sequence
sequencenamenamepg_class.relnameName of sequence
sequenceownernamepg_authid.rolnameName of sequence’s owner
data_typeregtypepg_type.oidData type of the sequence
start_valuebigintStart value of the sequence
min_valuebigintMinimum value of the sequence
max_valuebigintMaximum value of the sequence
increment_bybigintIncrement value of the sequence
cyclebooleanWhether the sequence cycles
cache_sizebigintCache size of the sequence
last_valuebigintThe last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null.

PG_SEQUENCE:

pg_sequence Columns

Name Type References Description
seqrelid oid pg_class.oid The OID of the pg_class entry for this sequence
seqtypid oid pg_type.oid Data type of the sequence
seqstart int8 Start value of the sequence
seqincrement int8 Increment value of the sequence
seqmax int8 Maximum value of the sequence
seqmin int8 Minimum value of the sequence
seqcache int8 Cache size of the sequence
seqcycle bool Whether the sequence cycles