postgresql: force create SERIAL without primary key

[Павел Фролов]

Hi all,

I need just autoincrement without primary key, like:

CREATE TABLE test (

    id BIGSERIAL NOT NULL,

    run_id INTEGER NOT NULL

);

How it's do with sqlalchemy core?

I tried:

sa.Column('id', sa.BIGINT(), sa.Sequence('test_seq')),

or

sa.Column('id', sa.BIGINT(), autoincrement=True),

->

id BIGINT,
(no serial)

I see in docs that autoincrement can be only with primary key.

I asked docs and google - can not find solution.

[Mike Bayer]

set autoincrement=False in the Column definition

Column('id', BIGINT, autoincrement=False)

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4629a9b3-3a8e-47b8-8d9a-580242e81d9fn%40googlegroups.com.

[Павел Фролов]

Thank you for the answer, but how it can help...

I tried, but I NEED autoincrement (SERIAL in postgres), NO NEED primary key on this, particular column.

Also I tried with Sequence and without autoincrement:
Column('id', BIGINT, sa.Sequence('test_seq'), autoincrement=False, nullable=False)

no result too.
I need to get:
[sql] id BIGSERIAL

but got:

[sql] id BIGINT

I found some workaround, but with warning:
t = Table(

    'outputs', metadata,
    Column('id', BIGINT, primary_key=True, autoincrement=True, nullable=False)

    # Partitioning columns
    Column('run_id', None, ForeignKey('runs.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False, index=True),

    Column('hotel', Integer, nullable=False),
    sa.PrimaryKeyConstraint('id', 'hotel', 'run_id'),
    postgresql_partition_by='range(hotel, run_id)'
)

in this case I got exactly what I want:

CREATE TABLE me_hotels_bc_fb_prediction_steps_outputs (

id BIGSERIAL NOT NULL, 

run_id INTEGER NOT NULL, 

hotel INTEGER NOT NULL, 

PRIMARY KEY (id, hotel, run_id), 

FOREIGN KEY(run_id) REFERENCES runs (id) ON DELETE CASCADE ON UPDATE CASCADE

)

 PARTITION BY range(hotel, run_id)

warning:

sqlalchemy/sql/schema.py:3615: SAWarning:

Table 'outputs' specifies columns 'id' as primary_key=True, not matching locally specified columns 'id', 'hotel', 'run_id'; setting the current primary key columns to 'id', 'hotel', 'run_id'. This warning may become an exception in a future release

вторник, 29 сентября 2020 г. в 21:05:47 UTC+4, Mike Bayer:

[Mike Bayer]

oh other way around, you want BIGSERIAL

make yourself a BIGSERIAL type.

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import Integer

from sqlalchemy import MetaData

from sqlalchemy import Table

from sqlalchemy import types as sqltypes

class BIGSERIAL(sqltypes.UserDefinedType):

    def get_col_spec(self, **kw):

        return "BIGSERIAL"

m = MetaData()

t = Table("t", m, Column("x", BIGSERIAL), Column("y", Integer))

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.create_all(e)

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f8a2df2f-05ff-49da-80ef-8eb71aa326a1n%40googlegroups.com.

[Mike Bayer]

I just ran your test case, it generates BIGSERIAL, no warning is emitted because I don't include primary_key=True on the column.  Can you ensure SQLAlchemy is on latest 1.3 version and try the below MCVE?  if you can modify it to show your problem that would help.

import sqlalchemy as sa

from sqlalchemy import BIGINT

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import ForeignKey

from sqlalchemy import Integer

from sqlalchemy import MetaData

from sqlalchemy import Table

m = MetaData()

Table("runs", m, Column("id", Integer, primary_key=True))

t = Table(

    "outputs",

    m,

    Column("id", BIGINT, autoincrement=True, nullable=False),

    # Partitioning columns

    Column(

        "run_id",

        ForeignKey("runs.id", onupdate="CASCADE", ondelete="CASCADE"),

        nullable=False,

        index=True,

    ),

    Column("hotel", Integer, nullable=False),

    sa.PrimaryKeyConstraint("id", "hotel", "run_id"),

    postgresql_partition_by="range(hotel, run_id)",

)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.drop_all(e)

m.create_all(e)

output:

CREATE TABLE runs (

id SERIAL NOT NULL,

PRIMARY KEY (id)

)

CREATE TABLE outputs (

id BIGSERIAL NOT NULL,

run_id INTEGER NOT NULL,

hotel INTEGER NOT NULL,

PRIMARY KEY (id, hotel, run_id),

FOREIGN KEY(run_id) REFERENCES runs (id) ON DELETE CASCADE ON UPDATE CASCADE

)

PARTITION BY range(hotel, run_id)

On Wed, Sep 30, 2020, at 4:21 AM, Павел Фролов wrote:

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f8a2df2f-05ff-49da-80ef-8eb71aa326a1n%40googlegroups.com.

[Павел Фролов]

Mike,

fist of all thank you a lot for your answers - it works for me now,

but I found issue case (it was necessary to test more thoroughly and describe the libraries, I use latest sqlalchemy and alembic)

so problem was when I had UniqueConstraint instead of PrimaryKeyConstraint.

# UniqueConstraint case NOK

outputs = Table(

    "outputs",

    de_db.metadata,

    Column("id", sa.BIGINT, autoincrement=True, nullable=False),

    Column("run_id", sa.Integer, nullable=False),

    Column("hotel", Integer, nullable=False),

    # sa.PrimaryKeyConstraint("id", "hotel", "run_id"),

    UniqueConstraint('id', 'hotel', 'run_id'),

)

NOK ->

CREATE TABLE outputs (

    id BIGINT NOT NULL, 

    run_id INTEGER NOT NULL, 

    hotel INTEGER NOT NULL, 

    UNIQUE (id, hotel, run_id)

)

# PrimaryKeyConstraint OK

outputs = Table(

    "outputs",

    de_db.metadata,

    Column("id", sa.BIGINT, autoincrement=True, nullable=False),

    Column("run_id", sa.Integer, nullable=False),

    Column("hotel", Integer, nullable=False),

    sa.PrimaryKeyConstraint("id", "hotel", "run_id"),

    # UniqueConstraint('id', 'hotel', 'run_id'),

)

OK ->

CREATE TABLE outputs (

    id BIGSERIAL NOT NULL, 

    run_id INTEGER NOT NULL, 

    hotel INTEGER NOT NULL, 

    PRIMARY KEY (id, hotel, run_id)

)

But anyway, your example with custom type works independently from anything, as expected:

class BIGSERIAL(sa.types.UserDefinedType):

    def get_col_spec(self, *args, **kwargs):

        return "BIGSERIAL"

outputs = Table(

    "outputs",

    de_db.metadata,

    Column("id", BIGSERIAL, nullable=False),

)

OK ->

CREATE TABLE outputs (

    id BIGSERIAL NOT NULL

)

thanks again for the solution,

Pavel

среда, 30 сентября 2020 г. в 16:56:50 UTC+4, Mike Bayer: