Re: [SQL] convert a bigint into a timestamp
On Thursday 25 Jul 2002 1:48 am, marc sturm wrote: > Hello, > > Does anyone know how to convert a bigint into a date > or timestamp in a SQL query. > Thanks a lot. The problem is that there is no cast from bigint=>interval directly AFAICT, so go via text. richardh=> select extract(epoch from now()); date_part -- 1027593096.67471 (1 row) richardh=> select '1970-01-01 00:00:00 GMT'::timestamp + ((1027593096::bigint)::text)::interval; ?column? 2002-07-25 11:31:36+01 In your case you'd replace (1027593096::bigint) with the name of your column. HTH - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Return Primary Key from Procedure
On Wednesday 24 Jul 2002 3:12 pm, Peter Atkins wrote:
> All,
>
> I have two tables t_proj, t_task see below:
>
> CREATE TABLE t_proj (
> proj_id INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (proj_id),
> task_id integer(12),
> user_id integer(6),
> title varchar(35),
> description varchar(80)
> );
>
> CREATE TABLE t_task (
> task_id INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (task_id),
> title varchar(35),
> description varchar(80)
> );
These are MySQL, not standard SQL - the AUTO_INCREMENT won't work for you in
PostgreSQL. Likewise things like integer(12) - see online manual for details
on types and SERIAL columns.
> When I insert into t_task I need to return the task_id (PK) for that insert
> to be used for the insert into the t_proj table.
>
> I tried using RESULT_OID but I have no idea how to obtain the true PK using
> this opague id. Below is the procedure I tried to use.
You can get an AUTO_INCREMENT type feature by decaring task_id as SERIAL type,
but I'd recommend an explicit SEQUENCE.
richardh=> CREATE SEQUENCE foo_test_seq;
CREATE
richardh=> CREATE TABLE foo2 (task_id int4 NOT NULL DEFAULT
nextval('foo_test_seq'), a int4);
CREATE
richardh=> INSERT INTO foo2 (a) VALUES (1);
INSERT 7023473 1
richardh=> INSERT INTO foo2 (a) VALUES (2);
INSERT 7023474 1
richardh=> INSERT INTO foo2 (a) VALUES (2);
INSERT 7023475 1
richardh=> SELECT * FROM foo2;
task_id | a
-+---
1 | 1
2 | 2
3 | 2
(3 rows)
richardh=> SELECT currval('foo_test_seq');
currval
-
3
(1 row)
The sequence guarantees you an accurate report for this process. So - if you
have two processes each inserting, they'll only see their own "currval". Also
read up on nextval and sequences.
Sequences / serial type are more flexible than MySQL's AUTO_INCREMENT. You can
have multiple serials in a table, and share a sequence between several tables
if you want.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] how do I change regional setting for dates?
I live in Guatemala, and our timezone is GMT -6, but my server (postgres 7.1 on a linux redhat 6.2) is storing the dates in the format 2002-07-24 00:00:00-04 ( I this understand represents GMT -4). My linux servers gives me the the date and time correctly, so I know is not a matter of the date of the server, and I checked and its GMT-6 (which is right) . How do I change the regional setting in postgres? Thanks, Ligia ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Function using more than one database
Hi group, i am currently learning PGSQL and having a question: In DB1 i have got a table tbl1. In this table there are added, let's say 300 rows a day. Every time, rows are added, I want to Server to move these rows to other DBs (DB2,DB3,DB4). This seems not to work with Pl/Pgsql (or maybe I don't know the right syntax). Is there any other way, any other language to do so? Thanks, Christian Lübeck ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] use of client-side \lo_import and \lo_export commands
I am trying to insert a large object into my dbs using: INSERT INTO table VALUES (lo_import ( ' z/image/image.jpg ' )); but i get the message saying that I need superuser privilidges to do it and that I should use client-side \lo_import command. Does anyone know how to use that command? And what is the syntax for \lo_import and export commands? Many thanks in advance. Slava Viacheslav Kovalchuk [EMAIL PROTECTED] _ Send and receive Hotmail on your mobile device: http://mobile.msn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how do I change regional setting for dates?
You can do "show all" inside psql, which will show you all of your current run time settings, including a line that should read "Time Zone is unset" or whatever it is set to. This isn't abundantly clear in the docs, so I'll add some notation there, but if you check out http://www.postgresql.org/idocs/index.php?sql-set.html and http://www.postgresql.org/idocs/index.php?timezones.html it will explain how to update your internal timezone as needed. Robert Treat On Wed, 2002-07-24 at 15:38, Ligia Pimentel wrote: > I live in Guatemala, and our timezone is GMT -6, but my server (postgres 7.1 > on a linux redhat 6.2) is storing the dates in the format > > 2002-07-24 00:00:00-04 ( I this understand represents GMT -4). > > My linux servers gives me the the date and time correctly, so I know is not > a matter of the date of the server, and I checked and its GMT-6 (which is > right) . > > How do I change the regional setting in postgres? > > > Thanks, > > Ligia > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Last record
select * from foo_table order by oid desc limit 1; >From: Chris Ruprecht <[EMAIL PROTECTED]> >To: "Leao Torre do Vale" ><[EMAIL PROTECTED]>,<[EMAIL PROTECTED]> >CC: Jan Wieck <[EMAIL PROTECTED]> >Subject: Re: [SQL] Last record >Date: Wed, 24 Jul 2002 13:56:50 -0400 > >Select * from where <...> desc limit 1; > >Desc = from the bottom up, limit 1 = just one record. > >Best regards, >Chris > >On Wednesday 24 July 2002 10:36 am, Leao Torre do Vale wrote: > > Dear Sir, > > > > If you already have the answer of the question below please, send to me. > > > > Best Regards > > > > Leao > > > > Maputo - Mozambique > > > > How can select one field of last > > record of table? > > > > (ex: SELECT LAST ) > > > > Thanks > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Using Tables from another database.
Hello, I need to make a view like: create view view_test as select * from mydb.mytable; and I get this error: ERROR: parser: parse error at or near "." Whats the problem?? How can I solve this problem? Thanks a lot, Rogério Baldini
[SQL] queries got slower using 7.1.3
I have a weird problem and don't even know where to begin. First let me explain what is going on. I am developing on a Postgres 7.1.3 box and found this unexplainable delay on this query select * from webfinal where w_artistkey = 'MICBUR' order by w_date offset 2 limit 4; I get at least a 8-9 second delay. If I take either the 'order by' or the 'limit' off the results are immediate. I have several other postgres boxes 7.1.1 and there is no delay using this query, here is what I'm getting with explain, but I don't know why? notifydev=# explain select * from webfinal where w_artistkey = 'MICBUR' order by w_date offset 2 limit 4;NOTICE: QUERY PLAN: Limit (cost=39.97..119.91 rows=4 width=109) -> Index Scan using webfinal_w_date on webfinal (cost=0.00..10563.49 rows=529 width=109) EXPLAINnotifydev=# explain select * from webfinal where w_artistkey = 'MICBUR' order by w_date offset 2; NOTICE: QUERY PLAN: Limit (cost=999.97..999.97 rows=527 width=109) -> Sort (cost=999.97..999.97 rows=529 width=109) -> Index Scan using wf_ak1023165516 on webfinal (cost=0.00..976.06 rows=529 width=109) EXPLAIN Now I run explain on a 7.1.3 box news=# explain select * from webfinal where w_artistkey = 'MICBUR' order by w_date offset 2 limit 4;NOTICE: QUERY PLAN: Limit (cost=924.71..924.71 rows=4 width=109) -> Sort (cost=924.71..924.71 rows=498 width=109) -> Index Scan using wf_ak1027580700 on webfinal (cost=0.00..902.38 rows=498 width=109) EXPLAIN can someone explain this to me? Please send response directly as I am not on the list.
