PostgreSQL questions?

Classic Servoy
21

All,

And the Sybase->PostgreSQL conversion service is now available: http://www.pgexperts.com/servoyconversion.html

22

Some of my questions:

  1. Is there an equivalent in postgresql for the scheduled events in sybase?
  2. Do we have to use pg_dump for an online backup or is there an equivalent for BACKUP DATABASE?
  3. ‘sql’ or ‘plpgsql’ as language for Functions which one to use an why?
  4. What’s the easiest/fastest/most reliable way to dump a table and import it in an other database (UNLOAD FROM TABLE/LOAD TABLE in sybase)?
23

Hi Michel,

michel:

  1. Is there an equivalent in postgresql for the scheduled events in sybase?

You might want to take a look at PgAgent
http://www.pgadmin.org/docs/1.4/pgagent.html

michel:
2. Do we have to use pg_dump for an online backup or is there an equivalent for BACKUP DATABASE?

You mean to trigger a backup from SQL itself? I am not sure if that is possible. Perhaps Josh can shed some light on this one.

michel:
3. ‘sql’ or ‘plpgsql’ as language for Functions which one to use an why?

There are many procedural languages available on PostgreSQL. I guess it’s a matter of preference. But again, perhaps Josh can answer this one better.

michel:
4. What’s the easiest/fastest/most reliable way to dump a table and import it in an other database (UNLOAD FROM TABLE/LOAD TABLE in sybase)?

I guess the sql copy command would be fastest. With this you can create a CSV file that you then can import into another database.

24

ROCLASI:

michel:

  1. Is there an equivalent in postgresql for the scheduled events in sybase?

You might want to take a look at PgAgent
http://www.pgadmin.org/docs/1.4/pgagent.html

Nice!But it’s a shame that it is not installed with pgAdmin III by default. It also cannot be installed by the application stack builder.

ROCLASI:

michel:
4. What’s the easiest/fastest/most reliable way to dump a table and import it in an other database (UNLOAD FROM TABLE/LOAD TABLE in sybase)?

I guess the sql copy command would be fastest. With this you can create a CSV file that you then can import into another database.

Thanks. I’ve also found out that pg_dump and pg_restore have a -t switch with which selected tables can be dumped and restored.

25

michel:

ROCLASI:

michel:
4. What’s the easiest/fastest/most reliable way to dump a table and import it in an other database (UNLOAD FROM TABLE/LOAD TABLE in sybase)?

I guess the sql copy command would be fastest. With this you can create a CSV file that you then can import into another database.

Thanks. I’ve also found out that pg_dump and pg_restore have a -t switch with which selected tables can be dumped and restored.

Ah yes, I thought you meant to another brand of database. As for the pg_dump/pg_restore you can also simply do this in PgAdmin by right-clicking on the table node and selecting backup/restore (in fact it does use pg_dump/pg_restore under the surface).

Hope this helps.

26

Michel,

  1. Is there an equivalent in postgresql for the scheduled events in sybase?

See prior responses regarding pgagent. I think it might be a good idea for Servoy 5.3 to include pgagent installed on databases by default, given that users are used to a scheduling utility with Sybase.

  1. Do we have to use pg_dump for an online backup or is there an equivalent for BACKUP DATABASE?

The is no way to do a full database backup fro the SQL command line. You need to use pg_dump or pgAdmin3.

  1. ‘sql’ or ‘plpgsql’ as language for Functions which one to use an why?

SQL is quite limited in the kinds of functions it can execute; it just runs SQL statements and that’s all. So SQL functions are mostly good for simply creating a shortcut for complex expressions (like, replace CASE WHEN $1 = 0 THEN NULL ELSE $1 END with a “ztn()” function). PL/pgSQL is a procedural language with integrated SQL, based on Oracle’s PL/SQL and on Ada. It includes loops, conditional branching, error trapping and more.

Note that PostgreSQL also supports quite a few standard programming languages as procedural languages if you install them. This includes PL/Perl, PL/Python, PL/Tcl, PL/R, PL/PHP and even PL/LOLCAT. You need to have that language’s libraries on your system, though, which can be painful for Windows users.

  1. What’s the easiest/fastest/most reliable way to dump a table and import it in an other database (UNLOAD FROM TABLE/LOAD TABLE in sybase)?

COPY or \copy on the command line will dump the data from the table, and has quite a few options including CSV output. pg_dump allows you to dump table schema (design) and data in a portable way. pgAdmin3 has GUI interfaces for these tools; right-click the table you want to copy.

27

Hi, I am currently evaulation Servoy 5.2.2 and create a table using PostgreSQL driver. But I cant change the field length of the text fields. Should I be using Servoy to create tables and columns in my database or is there some other database administration tool ?

28

Hi,

Servoy doesn’t support changing column lengths once created. You should use a DBA tool for this.
For PostgreSQL you can use PgAdmin.

Hope this helps.