psql

PostgreSQL "DESCRIBE TABLE"

How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL (using the psql command)? ...

postgreSQL - psql \i : how to execute script in a given path

Hi, I'm new to postgreSQL and I have a simple question: I'm trying to create a simple script that creates a DB so I can later call it like this: psql -f createDB.sql I want the script to call other scripts (separate ones for creating tables, adding constraints, functions etc), like this: \i script1.sql \i script2.sql It works fine ...

Querying data from different tables

I am using a query like this on my postgres database, SELECT TableA.id FROM TableA , TableB WHERE TableA.id = 100; Each TableA.id is unique (its an autoincrement), I am gettting more than 1 result. Am i missing something in here? ...

Using psql to run an update

Hi there, I've a table with two columns are a unique key together and i cannot change the schema. I'm trying to execute an update using psql in which i change the value of one of the column that are key. The script is similar to the following: BEGIN; UPDATE t1 SET P1='23' where P1='33'; UPDATE t1 SET P1='23' where P1='55'; COMMIT; U...

How do I move a Pervasive PSLQ database between servers

What is the easiest way to move a Pervasive PSQL database between two servers. I can stop the database engines if necessary. ...

Why is psql inserting a tilde when I press any of the keys in the Home key cluster?

I'm using psql 8.2.3 on FreeBSD. Every time I press Insert, Home, Delete, End, Page Up or Page Down, a tilde (~) character is inserted instead of performing the expected function of the key. Why does this happen and how can I fix it? ...

Determining the start and end range of bytes changed in a file

I'm working on a little experimental utility to use within our company that indexes notes stored in our custom CRM software for full-text searching. These notes are stored in a Btrieve database (a file called NOTES.DAT). It's possible to connect to the database and retrieve the notes for indexing by using Pervasive's ADO.NET provider. Ho...

Is there a good reference card that compares T-SQL and PL/SQL side-by-side?

I'm looking for a good reference card / cheat sheet that compares T-SQL and PL/SQL data manipulation language commands side-by-side. I've previously searched SO but there isn't any older thread covering this particular subject so I thought it'd be a good idea to start one, given that such reference is almost mandatory if you happen to w...

How Do I Backup My PostgreSQL Database with Cron?

I can run commands like vacuumdb, pg_dump, and psql just fine in a script if I preface them like so: /usr/bin/sudo -u postgres /usr/bin/pg_dump -Fc mydatabase > /opt/postgresql/prevac.gz /usr/bin/sudo -u postgres /usr/bin/vacuumdb --analyze mydatabase /usr/bin/sudo -u postgres /usr/bin/pg_dump -Fc mydatabase > /opt/postgresql/postvac.gz...

PostgreSQL return setof record ( virtual table )

Hi, i need a postgres function to return a virtual table ( like in oracle ) with custom content. The table would have 3 columns and an unknown amounts of rows.. i just couldn't find the correct syntax on the internet.. imagine this: CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric) RETURNS setof record AS DEC...

Using parameterized SQL with LIKE in WHERE clause (Pervasive SQL)

Hi all, I have a Pervasive database that I connect to using C++. All my queries so far is parameterized, i.e "SELECT USER.NAME FROM USER WHERE USER.ID = ?", and that works fine. But in a search query I use LIKE in the WHERE clause, and then it seems I can't use parameters and wild chars (%). My query looks something like this "SELECT ...

Postgresql restore Error at or near "`"

Hi, I have 800mb backup postgresql db, i even had hardtime to open the file because not enough memory. I tried to restore the file, but i receive this error while restoring, does any one know how to fix I run this command: psql -U root -d mydatabase -f dbfile.sql i receive message: ERROR: syntax error at or near "" LINE 1: INSERT I...

Postgres serialization error without serializable transactions.

I got this error: ERROR: could not serialize access due to concurrent update But I'm not using serializable transaction isolation. Is that possible without setting the default isolation level to serializable? The postgres docs only mention it for serializable transactions. ...

How to find child tables that inherit from another table in PSQL

In PSQL, is there a good way of finding all the tables that inherit from another table? Ideally, I could get the data from a SQL query, but at this point, I'd be happy with any reliable method. ...

Detecting errors to decide whether to rollback or commit

I'm trying to be as lazy as possible by generating a series of SQL commands in a file to feed to psql for processing. In a nutshell, I'm loading a series of import tables from outside sources (already done, via COPY), and then in a final step, deleting/updating/inserting records into the primary tables (which is functionally also done). ...

Making Postgres accessible in Firefox by Psql

How can you make Postgres accessible in Firefox, in installing MediaWiki? I get the error "No database connection" because Firefox cannot have a database connection. I can have database connection in terminal by the user, masi. This suggests me that Firefox is not acting like a masi, so it cannot have the access. Error Warning: pg_ve...

Altering Oracle sequence using result of expression

I have two variables MAX_TABLE_ID INTEGER; NEXT_TABLE_ID INTEGER; I'm reading values into these and then trying to alter a sequence based on them EXECUTE IMMEDIATE 'ALTER SEQUENCE NEXT_VALIDATED_TABLE_ID INCREMENT BY [MAX_TABLE_ID-NEXT_TABLE_ID]'; Bit I'm getting an "invalid number" error or a SQL command not ended error. This e...

Copy data from Postgresql to MySQL

Hi all, I have encountered a problem where I need to copy only data from a Postgresql database to Mysql database. I already have the Mysql database with empty tables. By using PGAdmin I got a backup (data only, without database schema). I tried using PSQL tool but it keeps giving segmentation fault which I couldn't fix at the moment. ...

PSQL select max value per minute per id for multiple values a minute per id?

Given a table that looks like this: sensor_id | time | voltage -----------+------------------------+------------ 12292 | 2009-12-01 00:50:04-07 | 2270 12282 | 2009-12-01 00:50:04-07 | 93774 12192 | 2009-12-01 00:50:04-07 | 9386 12609 | 2009-12-01 00:50:05-07 | 0 125...

PSQL 8.3+ client query results display

Rather silly question, but I can't seem to find the answer in the docs or man pages. Starting with 8.3 I think, the psql CLI client will not display the results of a large query inline. It pipes to a 'less-like' result viewer that disappears when you hit q. This makes it very difficult to use data from the first query in subsequent que...