Writing scripts for PostgreSQL to update database?

Question

I need to write an update script that will check to see if certain tables, indexes, etc. exist in the database, and if not, create them. I've been unable to figure out how to do these checks, as I keep getting Syntax Error at IF messages when I type them into a query window in PgAdmin.

Do I have to do something like write a stored procedure in the public schema that does these updates using Pl/pgSQL and execute it to make the updates? Hopefully, I can just write a script that I can run without creating extra database objects to get the job done.

What kind of updates and what are you trying to check?

Karlson
– Karlson

2012-03-12 21:30:42 +00:00
Commented Mar 12, 2012 at 21:30 — Karlson
– Karlson, Commented Mar 12, 2012 at 21:30

user330315 · Accepted Answer · 2012-03-12 22:37:28Z

3

If you are on PostgreSQL 9.1, you can use CREATE TABLE ... IF NOT EXISTS

On 9.0 you can wrap your IF condition code into a DO block: http://www.postgresql.org/docs/current/static/sql-do.html

For anything before that, you will have to write a function to achieve what you want.

answered Mar 12, 2012 at 22:37

user330315

Sign up to request clarification or add additional context in comments.

Comments

tmpearce · Accepted Answer · 2012-03-12 22:35:45Z

1

Have you looked into pg_tables?

select * from pg_tables;

This will return (among other things) the schemas and tables that exist in the database. Without knowing more of what you're looking for, this seems like a reasonable place to start.

answered Mar 12, 2012 at 22:35

tmpearce

12.7k4 gold badges44 silver badges64 bronze badges

1 Comment

Tony Vitabile Over a year ago

Actually, yes, I have looked into pg_tables. I'm using queries against this table (and others) to determine which changes need to be made to the database schema. The problem was I didn't know how to get my logic to work, as Postgres doesn't support IF statements unless they're in some sort of function. The DO block statement lets me build those IF statements and execute them. That's what I was looking for.

Collectives™ on Stack Overflow

Writing scripts for PostgreSQL to update database?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related