Re: [SQL] The best option to insert data with primary id
On 2010-12-06, - wrote:
> --0016364d26cf7fa4970496bf2224
> Content-Type: text/plain; charset=ISO-8859-1
>
> 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?
(assuming the default for id is nextval ('seq_table'))
INSERT INTO table ( id, ...) VALUES ( default, ...) RETURNING id;
or you can leave id and default out of the left half:
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
> 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
you run into concurrency issues that way. (two concurrent inserts
could pick the same ID, one will fail with an error)
> Quero your opinions on how best to insert data to have less maintenance and
> better productivity with concurrent users.
> Thank you very much.
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
Use the sequence, that's what they were designed for.
Let id get the default value and pull that from the returning.
you will get gaps in the serquence due to failed or cancelled
transactions but there will probably not be many gaps.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] concatenate question
On 2010-12-08, Tony Capobianco wrote: > Thanks so much to everyone for your responses. You've been very > helpful. I'm running PostGres 8.4 and we're migrating our datawarehouse > from Oracle 10.2. I guess datatype is implicitly cast in oracle under > these circumstances: > > SQL> create table tony_test as select memberid||addeddate "data" from > TMPSV_PARENT_MASTER where rownum < 5; > > Table created. > > SQL> desc tony_test > Name Null?Type > - > > data VARCHAR2(59) > > SQL> select * from tony_test; > > data > --- > 3812482212010-06-23 13:53:38 > 3812510902010-06-23 14:12:25 > 3812622482010-06-23 15:24:45 > 3812725152010-06-23 16:35:24 > > > Thanks! FWIW, I think you can also extend the built-in concatenate operator, though I personally haven't used something like this in a production DB: BEGIN TRANSACTION ; CREATE FUNCTION concat_num_ttwotz(numeric, timestamp without time zone) RETURNS text AS 'select $1::text || $2::text;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT ; CREATE OPERATOR || ( PROCEDURE = concat_num_ttwotz ,LEFTARG = numeric ,RIGHTARG= timestamp without time zone ) ; CREATE TABLE tony_test ( memberid numeric ,addeddate timestamp without time zone ) ; INSERT INTO tony_test VALUES(1,CURRENT_DATE) ; SELECT memberid || addeddate FROM tony_test ; ROLLBACK ; -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Correct usage of FOR UPDATE?
Hello, I have a simple table-based queue system, and I'd looking for some advice on improving my dequeue function. it boils down to: SELECT id FROM queue WHERE FOR UPDATE NOWAIT; which works well, in that no item gets dequeued more that once. The issue, however is that when a contention occurs, the error "could not obtain lock on row in relation" is raised. Is there a way that I can rewrite this so that when a contention occurs, there is no error? I would like the "winning" process to lock the row, and the "losing" process to select null rather than raise the rowlock error. thanks beforehand for any help!
[SQL] concatenate question
Here's my table: plsql_dw=# \d tmpsv_parent_master Table "staging.tmpsv_parent_master" Column |Type | Modifiers +-+--- memberid | numeric | addeddate | timestamp without time zone | sourceid | numeric | regcomplete| numeric(1,0)| optoutdate | date| bouncedate | date| websiteid | numeric | emailbounced | numeric(2,0)| emailok| numeric(2,0)| emailaddress | character varying(50) | srcwebsiteid | numeric | srcmemberid| numeric | sitetype | character varying | commissionpct | numeric | pricepermember | numeric | acceptrate | numeric(3,2)| mktgcenterid | numeric | label | character varying(32) | Why won't this work? plsql_dw=# select memberid || addeddate from tmpsv_parent_master limit 10; ERROR: operator does not exist: numeric || timestamp without time zone LINE 1: select memberid || addeddate from tmpsv_parent_master limit ... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Thanks. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sqlplus reporting equivalent in postgres?
psql - not as advanced, doesn't have all the features SQL*Plus has. On the other hand, it is at least capable of command history and readline support. Yes, but rlwrap will eliminate that limitation. -- Gary Chambers -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correct usage of FOR UPDATE?
Hey Joe, You can wrap the given query in the PL/pgSQL function, catch the exception and return NULL if it occurs. Please see http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING 2010/12/9 Joe Carr > Hello, > I have a simple table-based queue system, and I'd looking for some advice > on improving my dequeue function. it boils down to: > > SELECT id > FROM queue > WHERE > FOR UPDATE NOWAIT; > > which works well, in that no item gets dequeued more that once. The issue, > however is that when a contention occurs, the error "could not obtain lock > on row in relation" is raised. > > Is there a way that I can rewrite this so that when a contention occurs, > there is no error? I would like the "winning" process to lock the row, and > the "losing" process to select null rather than raise the rowlock error. > > thanks beforehand for any help! > -- // Dmitriy.
Re: [SQL] sqlplus reporting equivalent in postgres?
Samuel Gendler wrote on 11.12.2010 04:23: psql - not as advanced, doesn't have all the features SQL*Plus has. On the other hand, it is at least capable of command history and readline support. Hmm, for me SQL*Plus does support command history, but this is getting off-topic now... Regards Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] concatenate question
Tony Capobianco wrote: > Here's my table: > > plsql_dw=# \d tmpsv_parent_master >Table "staging.tmpsv_parent_master" > > Why won't this work? > plsql_dw=# select memberid || addeddate from tmpsv_parent_master limit > 10; > ERROR: operator does not exist: numeric || timestamp without time zone > LINE 1: select memberid || addeddate from tmpsv_parent_master limit ... > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. You can read? ;-) You have to CAST the INT and the TIMESTAMP both into a STRING-typ, for instance: test=*# select 1::int || now()::timestamp; ERROR: operator does not exist: integer || timestamp without time zone ZEILE 1: select 1::int || now()::timestamp; ^ TIP: No operator matches the given name and argument type(s). You might need to add explicit type casts. test=!# rollback; ROLLBACK Zeit: 0,124 ms test=# select 1::text || now()::text; ?column? 12010-12-11 14:42:12.571931+01 (1 Zeile) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
