There are two methods you can use. Both require creating a user and a database. By default psql connects to the database with the same name as the user. So there is a convention to make that the “user’s database”. And there is no reason to break that convention if your user only needs one … Read more
(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
You can use the REASSIGN OWNED command. Synopsis: REASSIGN OWNED BY old_role [, …] TO new_role This changes all objects owned by old_role to the new role. You don’t have to think about what kind of objects that the user has, they will all be changed. Note that it only applies to objects inside a … Read more
The manual: Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement: PERFORM query; Unless you assign the result, replace SELECT 1; with PERFORM 1;
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
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
Yes. UNIQUE constraints are implemented using a unique B-tree index in Postgres. See: How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
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
I believe xmax::text::int > 0 would be the easiest trick: so=# DROP TABLE IF EXISTS tab; NOTICE: table “tab” does not exist, skipping DROP TABLE so=# CREATE TABLE tab(id INT PRIMARY KEY, col text); CREATE TABLE so=# INSERT INTO tab(id, col) VALUES (1,’a’), (2, ‘b’); INSERT 0 2 so=# INSERT INTO tab(id, col) VALUES (3, … Read more
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