PostgreSQL Database Forum

hai everyone, I am having a doubt in inserting data in time field. I am having a table in which in column in timestamp without time zone datatype. I want to insert a row into the table but that time field has no value if I insert omitting that column error occurs... for eg. CREATE TABLE blah ( uuid integer NOT NULL DEFAULT...

Hi! I have a table named sales with the columns date (timestamp) and price (bigdecimal or something). I want to get the sale for each day (the sum of ‘price’ for all the rows with the same ‘date’). Like: 2006-10-10, 17 sales, 1500.00 $ 2006-10-11, 15 sales, 1400.00$ etc… This is how I try to do it: SELECT to_char(date, 'YYYY'),...

the reason I ask is I have always used postgres at home and work, but my new web host only has mysql :( I want to know what sort of differences I will have in regards to programming php/perl webpages using the database. I current use sequences in postgres and understand this will be different in mysql, so can you tell me what the...

Hi, I am using postgres 7.4.5 on Redhat Enterprise Linux 3. My background is really on Oracle, and I am porting a largish database over to postgres. Here is my problem: On oracle, I had a table with an "on update or delete" trigger that

I have a large amount of data that I copy in and out of Sybase very often. Now I also want to copy this data in and out of postgres. I have an existing script that creates the entire database(s) from scratch in Sybase and then uses the Sybase bulk copy tool "bcp" to copy the data in. I already did a brute force port of this script to...

Hello, In mysql, we use "show processlist" to see all current process. Could someone let me know in PostgreSQL, what commands that we can check the current connections and processes please? Thanks a lot, Ly

Hello, I'm looking for a way to convert a unix timestamp to a PostgreSQL date without using ::abstime which seems to be deprecated. Currently, I do that query : levure=> select 1063147331.843::int4::abstime; abstime ------------------------

I would like to be able to truncate all of the tables in a schema without worrying about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before truncating, but I still get constraint errors. Is there a way todo something like: 1) disable all constraints 2) truncate all tables 3) re-enable all constraints ? In the slony...

Is that possible to show all triggers enabled status(true/false) within a schema by an SQL query?

Hi, I have got an error with PostgreSQL-7.4Beta4 : ERROR SET AUTOCOMMIT TO OFF is no longer supported I used these option to load huge Database as i can save time with it. Is there an other option in PG 7.4 to do the same ? Regards Thierry Missimilly

In using pg_dump to dump an existing postgres database, I get the following: pg_dump: WARNING: out of shared memory pg_dump: attempt to lock table <table name> failed: ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. postgresql.conf just has the default of 1000 shared_buffers. The database itself...

Hello, I have a table with 29268 odd records. Deleting records is VERY slow, and I don't know why. I explained analysed the following query: delete from people where id < '2000' Index Scan using people_pkey on people (cost=0.00..71.68 rows=2792 width=6) (actual time=1.361..5.657 rows=2000 loops=1)

Hi , How To Execute A Psql Commands Through Batch File(.bat). My Aim Is , Usting Batch File , Connect The Database And Then Execute The Psql Statements.

hi all, Question: i have a table in a database of 6 columns and 630 rows.i want to add one new column to this table and also update this column starting from row 1 to row 630. System information: 1.Linux ac8d7f39.ipt.aol.com 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005 i686 i686 i386 GNU/Linux 2. PostgreSQL 7.4.6 My trial: 1. i...

Hi: I'm trying to dump tableA and restore it to tableB: $ ./pg_dump -Fc -t tableA databaseA -f tableA.dump -v $ ./pg_restore -t tableB -d databaseA tableA.dump -v pg_dump creates tableA.dump aparently well, but after running pg_restore without errors I cannot find any "tableB", what am I doing wrong?

i am using a regular expression in a select statement inside a function. how do i dynamically insert a variable into the regexp? eg. SELECT string ~ '\\d{X}' where X is the dynamic variable. I tried '\\d{' || X || '}' obviously it didn't work.

DECLARE groups integer; tmp RECORD; tmpi integer; BEGIN FOR tmp IN SELECT i_group_id FROM l_group_to_user WHERE i_user_id = $1 LOOP SELECT i_group_id INTO tmpi FROM s_group WHERE i_group_id = tmp.i_group_id; groups := tmpi;

I am creating a script to dump a postgres database into a sqlite database. I need to know how to find out programattically what fields are in the primary key of a given table. Is this possible in postgres? Thanks, Rick ---------------------------(end of broadcast)---------------------------

Say, we have uptimes from several servers: Server up_from up_to ------ ------- ------- s1 0:00 8:00 s1 10:00 20:00 s1 22:00 24:00 (would better be a timestamp with 0:00 and next day) s2 0:00 4:00 s2 6:00 23:00

Suppose I have vacuum_values() function, which removes all "no longer referenced" by parent column. Kind of function to be run from time to time to clean table from crud. It looks like this: CREATE FUNCTION vacuum_values() RETURNS void AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT value_id FROM values NATURAL LEFT JOIN other_tab WHERE...

Q: How to update properties value in the array of jsonb field? Hi All, I run Postgres13 (PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit) I have a jsonb field type (fieldname is test), like below JSON array format, how to update is_read properties in extras node to true where the...

Hi experts, I have a table: cod1 int, hours1 timestamp, cod2 int, hours2 timestamp,

Hello, I have a simple two-column table mapping names to ids. I'd like to write a select statement that will return a default value if a given name isn't found in the table. That is, I want something equivalent to the following pseudocode: if exists (select 1 from map where name = 'foo') then select id from map where name = 'foo' else

Hello, I know that PostgreSQL, like most database management systems, has a function call called NOW() that returns the current date. Is there a way to return a datein PostgreSQL such that the output is in ISO 8601 format (Unix 'date -I' format)but such that the date is not "today"'s date but the date two days ago or five days ahead of...

how to get current date in postgresql?