Unable to add new record for table in another schema

[jcanepa]

I have a Delphi form with two pgQueries one in the public schema and another in a user-created schema "general".
When I try to add a new record in the "general" schema I get the error:
relation "priced_item_priced_item_id_seq" does not exist.

I've tried changing the reference to the sequence to:
"general.priced_item_priced_item_id_seq" with no luck

Any ideas?

Does the VCL support multiple schemas?

[bork]

Hello

There can be some causes:
- The priced_item_priced_item_id_seq table doesn't exist in the current (default) schema. In this case you should specify table name with schema name: select * from general.priced_item_priced_item_id_seq
- The priced_item_priced_item_id_seq table was created with case sensitive name. In this case the table name should be quoted and the TPgQuery.Options.QuoteNames option should be set to True.

Yes, PgDAC supports multiple schemas.

[jcanepa]

Thanks for the quick reply.

It's all lowercase and it is a different schema "general" as opposed to "public". I am still unable to get it to function correctly.

Here's the form definition of the TPgQuery:
object qluLookup: TPgQuery
SQLInsert.Strings = (
'INSERT INTO general.priced_item'
' (priced_item_id)'
'VALUES'
' (:priced_item_id)')
SQLDelete.Strings = (
'DELETE FROM general.priced_item'
'WHERE'
' priced_item_id = :Old_priced_item_id')
SQLUpdate.Strings = (
'UPDATE general.priced_item'
'SET'
' priced_item_id = :priced_item_id'
'WHERE'
' priced_item_id = :Old_priced_item_id')
SQLRefresh.Strings = (
'SELECT priced_item_id FROM general.priced_item'
'WHERE'
' priced_item_id = :priced_item_id')
SQLLock.Strings = (
'SELECT * FROM general.priced_item'
'WHERE'
' priced_item_id = :Old_priced_item_id'
'FOR UPDATE NOWAIT')
Connection = dm.db
SQL.Strings = (
'SELECT priced_item_id FROM general.priced_item'
'WHERE'
' priced_item_id = :priced_item_id')
Options.DefaultValues = True
Left = 315
Top = 5
ParamData =
object qluLookuppriced_item_id: TLargeintField
AutoGenerateValue = arAutoInc
DefaultExpression = 'nextval('#39'general.priced_item_priced_item_id_seq'#39'::regclass)'
FieldName = 'priced_item_id'
end
end

[bork]

Hello

I created a new table with auto-increment field in the public schema and sequence in the other schema:

Code: Select all

CREATE TABLE test_table
(
  id integer NOT NULL DEFAULT nextval('test.seq_test_table'::regclass),
  "name" character varying(250),
  CONSTRAINT test_table_pk PRIMARY KEY (id)
)

CREATE SEQUENCE test.seq_test_table
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

In Delphi an auto-increment field has the following definition:

Code: Select all

    object PgQuery1id: TIntegerField
      AutoGenerateValue = arAutoInc
      DefaultExpression = 'nextval(''test.seq_test_table''::regclass)'
      FieldName = 'id'
    end

And all works correctly.

Maybe you don't have enough permissions. Please try to execute the following query in TPgQuery:

Code: Select all

  select nextval('general.priced_item_priced_item_id_seq')

If you have enough permissions, this query will return the next value of the sequence.