[SQL] First aggregate with null support
Hello, I'm looking for a First aggregate which may return null. From the example at http://wiki.postgresql.org/index.php?title=First_%28aggregate%29, I have just defined a non strict function that returns the first value: CREATE OR REPLACE FUNCTION public.first_agg_wnull ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT $1; $$ LANGUAGE SQL IMMUTABLE COST 1; And an aggregate: CREATE AGGREGATE public.first_wnull ( sfunc= first_agg, basetype = anyelement, stype= anyelement ); But this always return null which is the default init condition of the aggregate :-( I also have a working one using an array function (first_wnull_a, below) , but I wonder if there is a simpler solution ... best regards, Marc Mamin CREATE OR REPLACE FUNCTION first_element_state(anyarray, anyelement) RETURNS anyarray AS $$ SELECT CASE WHEN array_upper($1,1) IS NULL THEN array_append($1,$2) ELSE $1 END; $$ LANGUAGE 'sql' IMMUTABLE COST 2; CREATE OR REPLACE FUNCTION first_element(anyarray) RETURNS anyelement AS $$ SELECT ($1)[1] ; $$ LANGUAGE 'sql' IMMUTABLE COST 2; CREATE AGGREGATE first_wnull_a(anyelement) ( SFUNC=first_element_state, STYPE=anyarray, FINALFUNC=first_element ); select first_wnull(s) from generate_series (1,10) s => NULL select first_wnull_a(s) from generate_series (1,10) s => 1 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] The best option to insert data with primary id
Hi everyone,
I have a question about how best to insert and manipulate the table with
primary key id for better productivity. I need to insert data into the table
and get last id.
1. First option to take counter Postgres SEQUENCE:
INSERT INTO table (id, ...) VALUES ((SELECT nextval ('seq_table')), ...)
RETURNING (SELECT currval ('seq_table')) AS id
Only thing I see, that if the row is not inserted, the counter is
incremented every time when called. Then they will have empty unused id in
the table and ID number will grow much. There will be many records. This id
int8 type declared with length 64.
Is there any option to occupy empty sequence records. I have to worry about
this?
2. Second option is to take control of id and
INSERT INTO table (id, ...) VALUES ((SELECT MAX (id) +1 FROM table), ...)
RETURNING (SELECT MAX (id) +1 FROM table) AS id
Quero your opinions on how best to insert data to have less maintenance and
better productivity with concurrent users.
Thank you very much.
Same question in Spanish.
Hola a todos,
tengo una pregunta sobre como mejor hacer insert en la tabla y manipular id
con primary key para mejor productividad. Necesito Insertar datos a la tabla
y obtener ultima id.
1. Primera opcion llevar contador con SEQUENCE de Postgres :
INSERT INTO table (id, ...) VALUES ((SELECT nextval('seq_table')), ...)
RETURNING (SELECT currval('seq_table')) AS id
Unica cosa que veo, que si no se inserta la fila, el contador se incrementa
cada ves cuando se llama. Entonces habran id vacias sin usar en la tabla y
numero de id se va a crecer mucho. Habran muchos registros. Esta id
declarada como type int8 con longitud 64.
Hay alguna opcion de ocupar registros de sequence vacias. Tengo que
preocupar por esto?
2. Segunda opcion es coger control de id como
INSERT INTO table (id, ...) VALUES ((SELECT MAX(id)+1 FROM table), ...)
RETURNING (SELECT MAX(id)+1 FROM table) AS id
Quero vuestras opiniones sobre como mejor hacer insert de datos para tener
menor mantenimiento y mejor productividad con usuarios concurrentes.
Muchas gracias.
Re: [SQL] The best option to insert data with primary id
Quoting - :
I have a question about how best to insert and manipulate the table with
primary key id for better productivity. I need to insert data into the table
and get last id.
1. First option to take counter Postgres SEQUENCE:
INSERT INTO table (id, ...) VALUES ((SELECT nextval ('seq_table')), ...)
RETURNING (SELECT currval ('seq_table')) AS id
Much simplier:
INSERT INTO table (name, email, salary) VALUES ('joe', 'j...@example.com',
100) RETURNING id;
Cheers,
-Mike
--
Michał Roszka
m...@if-then-else.pl
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] The best option to insert data with primary id
Quoting - :
I know you can skip SEQUENCE - ((SELECT nextval ('seq_table')) do not put
this in the query, my question was that such concurrency, and ids
omitted which can not be inserted but increased with SEQUENCE ?
In the initial message you have been wondering, if you should be worried
about "wasted" sequence tokens. You have mentioned, that your primary key
is of type int8 and so is the sequence range. Do you really expect as many
records and/or insert queries?
If so, consider the id column int8 DEFAULT NULL and an AFTER INSERT trigger
function that would take a nextval of the sequence and update the id
accordingly once the record *has been actually inserted* instead of poking
the sequence each time you *are going to insert* something.
I am pretty sure, that the table is locked to prevent inserts until the
after-insert-trigger is finished.
Cheers,
-Mike
--
Michał Roszka
m...@if-then-else.pl
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Union Question
On Fri, Dec 3, 2010 at 8:58 AM, Plugge, Joe R. wrote: > Hello, > > I need to union three PostgreSQL tables and this won’t be a problem but the > tables are on different servers. Basically, I have an administrative server > that needs the tables viewable in a web administrator and three query > servers that log the needed data locally. Is there a way I can do this > without using Slony-I to replicate the data to the administrative server? PL/Proxy might fit the bill also. http://pgfoundry.org/projects/plproxy/ http://wiki.postgresql.org/wiki/PL/Proxy -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
