How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

(Updated – Thanks to the people who commented) Modern Versions of PostgreSQL Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL: ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY; Older Versions of PostgreSQL … Read more

Is it possible to have autoincrement number without it being an ID?

I see the following options: 1) You can use @Generated annotation. You should have a table declared in the following way: create sequence TST_DATA_SEQ increment by 1 start with 1; create table TST_DATA ( … dat_auto integer default nextval(‘TST_DATA_SEQ’), … ); and appropriate column in the entity: @Generated(value = GenerationTime.INSERT) @Column(name = “dat_auto”, insertable = … Read more

Returning from a function with OUT parameter

It would work like this: CREATE OR REPLACE FUNCTION name_function(param_1 varchar , OUT param_2 bigint) LANGUAGE plpgsql AS $func$ BEGIN INSERT INTO table (collumn_seq, param_1) — “param_1” also the column name? VALUES (DEFAULT, param_1) RETURNING collumn_seq INTO param2; END $func$; Normally, you would add a RETURN statement, but with OUT parameters, this is optional. Refer … Read more

Why can I create a table with PRIMARY KEY on a nullable column?

Because the PRIMARY KEY makes the included column(s) NOT NULL automatically. I quote the manual here: The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL. Bold emphasis mine. I ran a test … Read more

How to get function parameter lists (so I can drop a function)

Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual: pg_get_function_identity_arguments(func_oid) … get argument list to identify a function (without default values) … pg_get_function_identity_arguments returns the argument list necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance. This form omits default … Read more

SQLAlchemy, Psycopg2 and Postgresql COPY

accepted answer is correct but if you want more than just the EoghanM’s comment to go on the following worked for me in COPYing a table out to CSV… from sqlalchemy import sessionmaker, create_engine eng = create_engine(“postgresql://user:[email protected]:5432/db”) ses = sessionmaker(bind=engine) dbcopy_f = open(‘/tmp/some_table_copy.csv’,’wb’) copy_sql=”COPY some_table TO STDOUT WITH CSV HEADER” fake_conn = eng.raw_connection() fake_cur = … Read more