hi folks, I'm writing a shell script (will become a cronjob) that will: 1: dump my production database 2: import the dump into my development database Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this: #!/bin/bash time=`dat...
I have two postgres queries that i execute using PHP and both of them result a Resource, is it possible to combine or concatenate these two resources? ...
Hi, Given a From Date, To Date and a Fiscal Year system, I want to get all the split-up duration within the given From & To Date based on the Fiscal Year system. Explained below with examples: Example 1: Fiscal Year system: Apr to Mar From Date: Jan-05-2008 To Date: May-15-2008 Based on Fiscal Year system, duration should be splitte...
I'm getting the error message: "Invalid byte sequence for encoding "UTF8": 0x9f Ok, now I know somewhere my php app is trying to query using that 0x9f character. But I have no idea WHERE. I checked postgresql.conf but I didn't find anything like "log_on_error". There's only the log_statement parameter which causes postgres to log all s...
Hi, Given a From Date, To Date, Fiscal Year system. I want to get all the split-up duration within the given duration based on the breakup asked for. Breakups are: 1) Weekly: This should be followed from Monday through Sunday. 2) Monthly: This should be 1st of a month through end of the month. 3) Quarterly Since the Fiscal ...
I'm in psql trying to create a table for a python admin. It's actually an extension of a working admin, so I'm adding a new table and some relations. When I enter in the following sequence, it never executes to completion: BEGIN; CREATE TABLE "work_projectinteractiveasset" ( "id" serial NOT NULL PRIMARY KEY, "name" varchar...
Hi I have the following script to create a table: -- Create State table. DROP TABLE IF EXISTS "State" CASCADE; CREATE TABLE "State" ( StateID SERIAL PRIMARY KEY NOT NULL, StateName VARCHAR(50) ); It runs fine in the query tool of PgAdmin. But when I try to run it from the command line using psql: psql -U postgres -d dbname -f 0010...
I have a psql script that looks like this: -- first set of statements begin sql statement; sql statement; sql statement; exception when others then rollback write some output (here I want to exit the entire script and not continue on to the next set of statements) end / -- another set of statements begin sql statement; s...
I'm using Pervasive SQL. I have the following UNION of mulitple SQL statements. Is there a way to clean this up, especially the Pay Date an the Loc No fields that are selected in each statement. Is there a way to pull this out and have only one place to need to change those two fields? ( SELECT '23400' as Gl_Number, ...
I'm using psql to run a simple set of COPY statements contained in a file: psql -d mydb -f 'wbf_queries.data.sql' where wbf_queries.data.sql contains lines: copy <my_query> to '/home/gvkv/mydata' delimiter ',' null ''; ... but I get a permission denied error: ... ERROR: could not open file ... for writing: Permission denied I'm ...
I need a command line that runs against psql and does: psql database -c "select date_trunc('day', start) as date, avg(finish - start) as duration from things group by date order by date;" But the problem is this gives me human readable output, aka: date | duration ---------------------+----------------- 2010-01-11 00:...
Hi all. I have time value 04:30:25 , I want to convert this to seconds . Is there any dedicated function to do this...? I know that we can extract hours ,minutes, seconds then we can calculate the second . SELECT extract ( hour from t ) * 60*60 + extract ( minutes from t ) * 60 + extract ( seconds from t ) from test ; B...
I have a PostgreSQL backup made with PHPPgadmin using Export > Copy (instead Copy > SQL which is actually what I need). File contains entries like this: COPY tablename(id, field) FROM stdin; ... How to convert this file to SQL format? INSERT INTO tablename... I want to use Pgadmin to to import this file using execute SQL command. ...
I regularly need to delete all the data from my postgresql database before a rebuild. How would I do this directly in SQL? At the moment I've managed to come up with a SQL statement that returns all the commands I need to execute: SELECT 'TRUNCATE TABLE ' || tablename || ';' FROM pg_tables WHERE tableowner='MYUSER'; but I cant see ...
Hi, I have following model described: class UserProfile(models.Model): avatar = models.ImageField(blank = True, upload_to='files') about = models.TextField(blank=True) rank = models.IntegerField(default = 1) solvedProblems = models.ManyToManyField(Composition, blank=True) user = models.ForeignKey(User, unique=True) ...
I am new to Rails database operation. I want to know , how to connect the psql database with my Rails Web application. For example I just wanted to display some table information in rails web page. What do I have to do? Any idea or any tutorial to learn ...
Could you tell me please how to check permissions to functions with psql console but without being overwhelmed with source code and descirption (like when using \df+). ...
In postgresql and bash(linux), are there ways to directly import a file from the filesystem like [execute.sh] pgsql .... -f insert.txt [insert.txt] insert into table(id,file) values(1,import('/path/to/file')) There seems to be no import function, bytea_import as well, lo_import would save int and i dont know how to get the file bac...
Hi All ,, Is there any way to get the get the specific values from date such as year , month , day . Actually we have in php date function . If we give date (Y-m ) ; it will give us 2010-07 . I want like this in postgres(psql) . Thanks in Advance... ...
I am trying to open a dataset through a data store layer in OpenJUMP. I have successfully made the connection to the project that I have created with Postgres. However, when I select the dataset that should be loaded, OpenJUMP gives an error message saying that the relation does not exist (Runtime Exception, PSQL Exception) What could be...